Go to Top

Part 3: Why I grew to love Microsoft Access Data Projects

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

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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!

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

8 Responses to "Part 3: Why I grew to love Microsoft Access Data Projects"

  • jscales
    August 15, 2013 - 9:08 pm Reply

    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).

    • Ben Clothier
      August 23, 2013 - 7:34 pm Reply

      @jscales –

      Have you looked at Office 365 & Access 2013 web apps?

  • Fabian
    February 1, 2013 - 1:20 pm Reply

    i cant connect acces adp to my sql server 2008 r2. please help me

    • Juan Soto
      February 2, 2013 - 9:13 pm Reply

      You may wish to ask for help at UtterAccess.com were there are many users willing to help you.

      Good Luck
      Juan

  • Pete North
    December 13, 2011 - 7:56 pm Reply

    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

  • Pete North
    December 13, 2011 - 7:46 pm Reply

    “DoCmd.OpenForm where and filter clauses will not work.”

    I can assure you it does.

  • erwin
    December 6, 2011 - 10:48 pm Reply

    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!

    • Juan Soto
      December 6, 2011 - 11:12 pm Reply

      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

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.