Deploying Access as a client-server application

Question

I'm using Access (2003 format) as a front-end with a SQL Server 2008 backend. Each user already has Access 2003 or 2007 installed. I'm curious as to the best deployment configuration to use for efficiency and maintainability.

Should I put the .MDB file on the server and place a shortcut to it on each machine? (There will only be two to three users at a time.) Or put a copy of the file on each machine? And if I'm using a 3rd-party ActiveX, does it need to be installed on each machine even if the .MDB is on the server (I assume so, but just checking), or just on the server?

Answer 1

Access files can become problematic when opened by multiple users at the same time. I would have a copy of the file on each user's machine.

And yes, each machine needs the ActiveX control installed - even if the MDB is on the server.

Answer 2

As Jon B said, the "putting the mdb on each user's machine" is a good solution. For what it's worth, I recommend that each user's runs a batch file that xcopies from the server the master mdb when the master has a later date. The batch file then runs the mdb. That makes deploying a new version easy.

Answer 3

To expand on the "where does the ActiveX control need to be installed" answer:

The fact that you ask the question betrays a somewhat foggy grasp on the way Access works. Just because you are using SQL Server as your back end (which qualifies as client/server) does magically make Access run on the server. Access always runs in the local RAM of the workstation (or remote desktop session should you be running it on Windows Terminal Server/Citrix) and that means that ActiveX controls have to be installed on each workstation, just as Access has to be installed there (substitute appropriate qualifications for WTS).

Access is not a client/server application though it can be used to create the client-side front end to a server database.

And, as an aside, most of the professional Access developers I know of avoid ActiveX controls like the plague, since they are so easily broken with no recovery possible (since they can't be used with late binding). There's almost always a more reliable method for accomplishing the task, though it may require more code than a drop-in ActiveX control.

Answer 4

I specifically created the Auto FE Updater utility so that I could make changes to the FE MDE as often as I wanted and be quite confident that the next time someone went to run the app that it would pull in the latest version. For more info on the errors or the Auto FE Updater utility see the free Auto FE Updater utility at http://www.granite.ab.ca/access/autofe.htm at my website to keep the FE on each PC up to date.