I was late to the ADP party, having discovered their power for a project I needed to do on a multi-franchise national database. I had started using a regular Access file but quickly grew disenfranchised with the record locks and other issues that propped up. The ADP proved great in both reliability and speed, but it took me a while to get the solution done right, mostly due to the lack of information on the web. It was due to these frustrations I decided to start this blog and the rest is history.
Call us for a great quote on converting your ADP to a regular Access file or Web app
Tips For Converting Access Data Projects to Regular Access
Access ADP is no longer supported in Access, you will need to instead create a brand new Access file and import your Forms, Reports, Code and Queries:
- Take a look at my getting starting guide if you are new to optimizing Access with SQL Server
- Create views when two or more tables are being used as form or report record sources, don’t let Access do the joins for you. If you wish to use the views for editing data you will need to create an index and/or table triggers to handle delete, update and inserts. This tip will allow you to speed up your database more than anything else you can do, aside from using stored procedures.
- Create local copies of tables that seldom change, for example: the list of 50 states.
- Don’t convert single table queries over to pass through queries, it’s not going to be worth the time or the hassle.
- Do consider converting multi-table queries over to pass-through or views.
- Do optimize your form design and only load records you need. Do you really need users to see every order from the beginning of time when the form loads? Load only what you need.
- Do trick your users into thinking your app is faster than it was before. For example, in one of my apps I was loading every customer record on a customer search form. I updated the code to not display any records until criteria were used for searching. The result? Users thought the search form loaded faster.
- Do learn how to use stored procedures for complicated tasks and data manipulation best suited for the server.
- Consider using temporary tables to store SQL data.
You may never match the speed and reliability of ADPs, but I hope these tips can help you transition over to a stable build of your app.
Hi guys, I am facing some issue in access 2013. Actually I was using a access app properly in access 2003 but now whenever I open this access db app in access 2013(using sql server 2008 r2), sorting doesn’t work. I am totally new in this, I didn’t get idea why it’s not working. Can any one help me out, please.
ADP is still alive in Access 2013, I found this link:
http://www.joakimdalby.dk/HTM/ADPX.htm
What Joakim Dalby calls “ADP+” looks like just his own private conversion framework, which seems similar to what Ben Clothier was talking about earlier : “Write a class module that will manage creation and binding of an ADO recordset … “
Hi Graham
That certainly sounds like it.
Thanks
Juan
How do I actually convert an ADP file to ACCDB?
Russell, there is no single button to convert an ADP file to ACCDB.
Generally speaking, I think the steps to put objects from ADP file into an ACCDB file would be something like the following:
1) Use Import (External Data -> Import -> Access) to get all forms, reports, and modules (not sure if it’ll let you import queries).
2) Convert all your reports’ recordsource to use passthrough queries. For those reports that has code within it, those will need to be updated to work with a DAO recordset.
3) Write a class module that will manage creation and binding of an ADO recordset and have your forms that has large amount of VBA code where ADO recordset is assumed invoke that class at the loading so that it may have an ADO recordset and thus minimize changes to your codebase
4) Write a routine to handle linking/connecting at your application’s startup.
That’s major steps I can think of. There may be some more. If you need help, you could contact us to work out a plan for conversion.
One thing that’s often underevaluated with client server linked tables, is the fact that ALL your linked tables pointing to the same server should have identical connection strings. Only in that case is Access capable of passing joins to te server instead of joining them locally. This can make huge performance impact with large tables.
For the same reason, I advise NOT to follow Juan’s recommendation to put static tables in front-end. The performance of queries will suffer when mixing data sources.
Patrick,
Thanks for sharing your thoughts. I don’t think Juan was envisioning that you’d do heterogeneous joins. As he pointed out already, it’s best to ensure that joins are done on server and not at Access by using views. In case of static tables, there usually should be no need to join the tables anyway – you only want to have a copy of the static table locally so that you can then bind them to the combobox controls and listbox controls. That does not mean you need to join the static tables in the form’s recordsource. The report’s recordsource should be based on a view that has all joins from the server performed. In short, most of the objects is to have only a single linked object with no joins within Access query as the recordsource. This way, we don’t have to guess whether Access will be able to pass back the joins to the server or might decide to do the joins locally and murder the performance that way.
I hope that helps clarify. Thanks again!
Ben,
Thanks for replying. Let me raise 2 points:
First, in numerous corporate banking environments where I have been working during the last years, you usually just can’t even think about creating a view on the server.
Second, if one pays enough attention while linking, Access itself does a great job of having the server do the joins, even for an Access query. In that case, the passthrough has little or no added performance.
Patrick,
Good points to think about!
1) That has not been a problem for us – most of the projects we worked on, we usually got the necessary permissions. I also want to point out that while I can understand why some DBAs might not want to allow you to create views, it is usually the case that you can be given a separate schema where you can be allowed to create objects. Additionally, the DBA can only grant you the CREATE VIEW permission on your own schema, thus ensuring only views and maybe stored procedures will reside in that schema (e.g. no extra tables that might cause problems with their file management). It’s in their interest to give you at least that much so you can ensure you’re using the server in the most efficient manner. Because all of your views are contained in a separate schema, it is easier for them to manage and monitor and if necessary, drop them all in one go.
2) You’re right. Access is indeed smart enough and usually can figure how to marshal joins back to the server. However, I’ve seen queries that have complicated joins end up evaluated locally, even though there were no reasons why it shouldn’t be passed to the server. This is because of how Access evaluates the query – it would build a tree representing the source then re-frame the SQL into the ODBC SQL and if it can’t figure out how to do that, then it may end up doing the join locally. The precise ‘when’ is not exactly known. Creating a view in its place eliminates the guesswork and more importantly, when you make the changes to the query, it won’t suddenly hit a performance problem when it cross that threshold. It’s also a good way of ‘documenting’ the project by programming them in a consistent manner. We’ve taken over some projects where too many things were mish-mosh mix that made it hard to maintain because we would have to track down where each object came from, whether it was a linked table, a view as a linked table, an Access query based on linked tables, an Access query mixing sources, or some more combinations. I would want to minimize the variance (it’s not always possible to completely eliminate all combinations and sometime you do really need the mish-mosh).
I hope that helps.
I have the same problem and opinion as Vera Van Boxel. A lot of small companies and/or shools and/or other subjects uses Access ADP (with OLEDB). Access ADP is very usefull and I don’t understand why Microsoft stopped support insted of develop. Microsoft is more and more worse and don’t care what customers need and/or want.
Linked tables does not solve problem. ODBC is slow,… no slow it’s even snail speed. Sometimes (once or twice a year) I notice some non-replicated problems with using, e.g. record does not writen or some recordset was not readed all only few record… Another problem is for example, you have to still relink tables if some changes on server. If you have terminal environment (citrix) with accdb is near impossible that one frontend (specific file) run more users in one time… big problem with connecting tables and rights… ACCDB is good, but only for small self Access databases not for working with SQL Server with a lot of users.
ADP as frontend is more better compared to LightSwitch. Access forms and datasets connected to tables, views or procedures has components e.g. filters like Execel. Divided Forms (Acc2007/2010), subforms… Creating is true RAD. AND the best is ADP can be modified directely on specific PC with full Access. For changes in LightSwitch you need Visual Stuio and LightSwitch, It’s of course not installed on all PCs…
Access and it’s ADP has many years lead and I don’t understand Microsoft discarded in the trash so good product…
PLEASE, IF YOU CAN, LOBBYING FOR RETURN ADP (as ACCDP)!
[…] when your database is built on the ADP model, which is connected to a SQL server. About ADP, see: Access ADP no longer supported in Access 2013, here are some tips. | accessexperts.com __________________ Have a nice […]
We have hundreds of ADPs and I just don’t see myself edit them all with linked tables (slower and less save). I don’t understand why Microsoft has done this and why they don’t offer a decent solution. In my opinion, this is not a smart move of MS.
Vera,
Sorry to hear you will be working on upgrading all of those files. It would seem you will need to create a special program to convert ADPs to Accdb.
Good Luck,
Juan
I always use an .adp copy of my .accdb project for occasional editing the raw SQL data. It beats opening/editing the tables in SSMS. What to do in 2013?
You still can use a linked table in an ACCDB for some light editing. However, for bulk editing, it’s best to write appropriate T-SQL queries. See if this helps:
http://accessexperts.net/blog/2011/08/05/learn-the-safe-way-to-issue-action-queries-in-ssms/
What keeps me using Access is the superb reporting ability it has. LightSwitch? Well one comment on the latest release says it all. You can find it here:
http://blogs.msdn.com/b/bethmassi/archive/2012/07/13/new-features-explained-lightswitch-in-visual-studio-2012.aspx
To save clicking on the link the comment is:
What About the Printing Support in LightSwitch Apps..?
Pls help me..!
To get LightSwitch to print it seems you must spend hundreds more for a report writer and Access has a better one built-in. Not much has changed from the early days when the joke was that Microsoft had finally perfected the paperless office because you can’t print from .Net.
Great point Frank! In our LightSwitch implementations we use SSRS, (SQL Server Reporting Services), and don’t bother with LS reporting.
Thanks
Juan
If you are using lightswitch, then likely, you have Visual Studio, which comes with reporting controls.
You can also use HTML to build the reports.
Juan, thank you for this update. I really like using adps also and sad to hear they’re being dropped. I hope my employer doesn’t upgrade to 2013 for a very long time.