How do I distribute updates to a Access database front end?

Question

I've got an Access 2007 database that I developed which connects to SQL Server for the actual data storage. I used the Package Solution Wizard to create a distributable installer which included access runtime (with an ACCDE file) which I went around and installed on 15 or so PCs. Anyway, my question is, what is the best way to distribute updates to this database? Right now I'd need to go around and remove and reinstall. That's not a problem... I was just wondering if there was another way.

I've tried leaving the front end on a network share but it seems that most people suggest storing the front-end on the local machine, which makes sense. The problems I've run into when I leave it on a network share (at least with Access 2003 mdbs) is that I find myself needing to compact and repair often and I also have to kill the open sessions (user's who have the file open) when upgrading. I would imagine it could also hypothetically create an unnecessary bottleneck if the user was not on the local network.

Mangle

You have discovered the first problem with Access front ends! I wouldn't wish Access on any developer. Learning some web framework is worth it. – Dineen

@Joe Koberg: what a crazy suggestion. Automating front-end distribution is trivial. It's a problem that has been solved repeatedly. autofeupdater.com is one such solution that is extremely easy to implement and completely transparent to the end user. – Disembowel

Or you could just go to this url... – Dineen

David.... you should provide that as an answer... as that's exactly what I'm looking for... – Mangle

I assumed Tony Toews himself would post it. Tony? – Disembowel

David, I've been busy with family stuff this week. Intermittent access, etc, etc. – Hon

Answer 1

Automating front-end distribution is trivial. It's a problem that has been solved repeatedly. Tony Toews's http://autofeupdater.com is one such solution that is extremely easy to implement and completely transparent to the end user.

Disembowel

It's a good tool, we've been using an old (free) version for years. After I updated it I realised it wasn't free anymore, after 15 days. We are a small company and the price tag now is kinda high (for us). . I consider switching to some shell skript or something. We only have 6 clients to update. I'd be glad to pay 30€ for a kind of a "Starter Edition" that has maybe a limit to 10 clients or something ... – Privily

Answer 2

We developed a vbscript 'launcher' for our access apps. That is what is linked to on the start menu of user's pcs and it does the following.

• It checks a version.txt file located on a network share to see whether it contains different text to a locally stored copy
• If the text is different it copies the access mdb and the new version.txt to the user's hard drive.
• Finally it runs the mdb in access

In order to distribute an update to the user's pc all that is required is to change the text in version.txt on the network share.

Perhaps you can implement something similar to this

Euglena

There are links to several such on the FAQ page at the Auto FE UPdater website. autofeupdater.com/faq – Hon

Answer 3

• Make a batch file on the server (network drive).
• Create a shortcut link to that batch file.
• Copy the shortcut to User's Desktop.
• When user double-clicks on shortcut, it will copy a fresh copy from network to local.
• Replace old database.adp on the server drive when you update a new version.
• Each user gets a copy of database.adp on their machine.
• Remove Security warning when opening file from network share is here.

Batch File

@ECHO OFF

REM copy from network drive to local
xcopy "Your_Network_Drive\database.adp" "C:\User\database.adp" /Y /R /F 

REM call your database file - Access 2007
"C:\Program Files\Microsoft Office\Office12\MSAccess.EXE" "C:\User\database.adp"  

Kalb

Answer 4

This is a very old post and I used the autofeupdater until it stopped working so I wrote one of my own and it has evolved over the last few years into something that I have used with many clients. It's so simple to use and there is no interface. Just an EXE and a very simple config file.

Please check it out here. I can also help with custom solutions if none of the configurations work for your needs. http://www.dafran.ca/MS-Access-Front-End-Loader.aspx

Restharrow

Answer 5

After trying all of the solutions above (not exactly these solutions but these are the common suggestions in the Access community), I developed a system entirely within Access using VBA that allows an admin DB to create and publish objects to client DBs without the need for user intervention or management of multiple DB files.

This approach has several benefits: 1. It simplifies the development process by having a dedicated environment (admin DB) for development and testing totally separate from the client DBs. 2. It simplifies the update/distribution process by allowing a developer to push out updates in real time that client DBs can implement in the background, without involving users. Can also allow devs to roll back to previous versions if desired. 3. It could be used as a kind of change management system within Access for developers who want to commit multiple changes to objects and modules and retain past changes. 4. It allows for easier user access control by allowing an admin to easily assign certain objects to specific users/roles without needing to maintain multiple versions of the DB.

I will hopefully post the code to GitHub soon, I just have to get clearance from my workplace to release it. I will edit this post to include the link when I have.

Lewls

Answer 6

We have usually kept the Access front ends on network drives, and just put up with the need to compact and repair on a regular basis. You will probably find you need to do that even when they are installed locally, anyway.

If you must have it installed locally, there are various tools which will enable you to "push out" software updates, and the guys over on ServerFault would have more information on those. Assuming such tools aren't available, the only other option I can think of is to write a small loader program that checks the local .MDB against a master copy on the server, and re-copies it across if they are different, before then launching the MDB.

Niggle

Yeah... those update tools are avaliable... but with the amount of desktops I'm dealing with, I think nearly all of them would be a bit overkill. – Mangle

A properly-designed front end will never need to be compacted. – Disembowel

SQLServer backend eliminates the data update and compaction problems... And most of the noncompliant SQL problem! – Dineen

@Joe Koberg: What "data update" problem? what "compaction" problem? All databases need to be compacted. With SQL Server, you can set the agent to do it for you. With Jet/ACE you have to create some code to do it and you can schedule that just like with the agent. SQL Server also has its own SQL dialect which is not 100% compliant with SQL standards or with other SQL dialects. So, your comment seems to me to be filled with nothing but nonsense. – Disembowel

David: That was addressed to the guy wanting to copy around MDBs when they get updated. Having all the data in one place eliminates that issue (for the data at least). And you have to admit that even if SQL Server's dialect is nonstandard, its a lot MORE standard than that of Access. You seem to find Access a good solution. For me and the enterprise I work in, it causes lots of pain every single day, and it has entrenched itself in business processes that are now held back because of how difficult Access is to migrate from. – Dineen

Access is difficult to migrate from because there's nothing that does the same things as it does. As to the SQL dialect, you do know about "SQL 92" mode in recent versions? This is an effort by MS to make Jet/ACE SQL as compatible with SQL Server as possible. It's not perfect, but you have to recognize that you're dealing with a database engine that was created before SQL 92 even existed and when hardly any database supported SQL 89 except in part. – Disembowel