Note: This is part three of a series of articles on Access Data Projects,
Click to see Part 1
Click to see Part 2
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!
I hope they eventually make a product that is easy to publish to a web based server. (I know, I know, there is the sharepoint publishing…but its clunky, unpredictable and complex).
@jscales –
Have you looked at Office 365 & Access 2013 web apps?
i cant connect acces adp to my sql server 2008 r2. please help me
You may wish to ask for help at UtterAccess.com were there are many users willing to help you.
Good Luck
Juan
You can use…
Dim stdocname As String
Dim stLinkCriteria As String
stLinkCriteria = “[TrackID]=” & Me![TrackID]
DoCmd.OpenForm “frm_isqnew”, acNormal, , stLinkCriteria
OR…
p1 = Me![TrackID]
DoCmd.OpenForm “frm_isqnew”
Forms![frm_isqnew].RecordSource = “Exec SP_quickfind ” & p1
“DoCmd.OpenForm where and filter clauses will not work.”
I can assure you it does.
Good day Juan, what version of ms access adp youre currently using? Ive been running 2k3 mde with mysql 5, 20 users in win2003 r2 terminal server in both lan and wan, and performance is ok so far its only 500,000 records. I am interested in testing adp for performance comparison. tnx and more power!
Hi Erwin!
I recommend using Access 2007 ADP, but do keep in mind ADP technology is going away, so I wouldn’t recommend investing any time into it and rather focus on optimizing Access with SQL Server in your code and methodologies.
Kind Regards,
Juan