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.