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.
So long ADPs! We will miss you! (in Access 2013)
As many of you have commented on my blog, ADPs are no longer supported in Access 2013. Many of you are now faced with converting ADPs over to regular Access apps and facing a wide range of issues in the process. I’ve put together some suggestions to help you with the cutover:
- Take a look at me 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.
- Finally, if you don’t love how well Access behaves with SQL Server, consider using LightSwitch instead. (Yes, it’s .Net but easy to use and great for business apps.)
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.