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

tags:

ms-access-2007

views:

234

answers:

+4 Q:

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

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.

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.

CraigS 2010-02-05 00:20:31

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.

Brett G 2010-02-05 01:11:07

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

David-W-Fenton 2010-02-05 03:22:41

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

Joe Koberg 2010-02-05 03:32:54

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

David-W-Fenton 2010-02-06 01:34:56

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.

Joe Koberg 2010-02-06 18:43:15

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.

David-W-Fenton 2010-02-07 00:15:28

+1 A:

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

Martin Booth 2010-02-05 00:27:18

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

Tony Toews 2010-02-11 22:44:15

+2 A:

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.

David-W-Fenton 2010-02-06 01:36:05

Thanks for your kind words, David.

Tony Toews 2010-02-11 19:58:31

ansaurus

tags:

views:

answers:

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

related questions