Why I learned to love Microsoft Access Data Projects
As a seasoned Access programmer, it seams all I do lately is Access with SQL Server, in part because of the great combination of SQL Server Express with Access and the zero cost of using Express for my clients. For the greater part of my career I’ve been using regular MDB’s for all of my work, but that all changed with a recent project of mine’s.
Why I had to use a Microsoft Access Data Project
My client, a nationally recognized home cleaning firm with franchisees across the USA, needed to consolidate each franchisee’s data into one national database for reporting of income. Up until then each franchisee was using a local MDB and mailing in there numbers, going national would avoid the hassle and provide real time intelligence to corporate headquarters on the health of the business. I decided on Microsoft Access 2007 runtime with SQL Server Express 2008 R2, but I rant into the following issues with a regular MDB:
- Record Locking: Test users were getting the dreaded message “Another user has modified the data…” when running data modification routines in code. Mind you this was after I had optimized the code to use one connection throughout the project.
- Speed: The regular Access MDB can open up multiple connections to the SQL Server backend, users were noticing a significant delay in downloading data off the Internet.
Both issues were resolved when I switched to using an ADP for the frontend. Not only did the record locking issue disappear, the speed of the application was better when SQL Server on the web then with a local Access file!
Microsoft Data Projects work well…if you avoid certain pitfalls
I’ve written a series of articles on the following issues with ADP:
- DoCmd.OpenForm where and filter clauses will not work.
- It’s not easy to supply your own login form when using SQL Server security.
- You’re going to have to learn SQL Server tools in order to maximize your Access user experience
I encourage you to learn how to use ADPs and gain from their unique relationship with SQL Server, and I hope you too will learn to love them!