The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Access Database Updater

I was wondering if anyone has ideas about best ways to approach auto-matically updating an old version of an Access database to a new version, programmatically (and NOT Access VBA code), without losing data.

I have dabbled a little with ADOX to do this, but it is very verbose and doesn't seem to map directly with JET and it's data types, etc.

Does anyone know of a component for this that is pre-built and ready to use?  I certainly don't want to re-invent this wheel.

GNU World Order Send private email
Tuesday, May 24, 2005
Hi World Order,

Check out this thread:

There's also this 3rd party app which appears to be what you're after:

Marcus from Melbourne
Tuesday, May 24, 2005
Hmm. I'm not sure whether you're trying to go from one version of Access to another or from one version of your database to another.

If it's the latter, and it's split with a front end on the client machines and a back end on a file server (as it absolutely should be), check out a page on my old site about doing this with a batch file:
JeremyNYC Send private email
Tuesday, May 24, 2005
Standard Access architecture is to have an .mdb file 'front end' on each workstation. This contains forms, reports, queries (probably). And an .mdb 'back end' on a server. This contains only data tables. The front ends have links to the back end tables.

Assuming you are using that architecture, and that it's the front ends you want to update, I do this:

It's a VB6 app, and is paired with a text file, which I rename as .ini

The two files are called something like feupdater.exe and lastuser.ini

These files, along with the front end live on each workstation. The 'latest' version of the front end lives on the server, probably in another sub-directory ('update')somewhere near the back end data file.

The desktop shortcut on each users workstation does this:

1. Runs feupdater.exe, which...

2. Opens the front end file on the workstation (but invisibly). Looks for a value called 'version' in a table called theSystemSpecs (or somesuch).

3. Does the same with the 'latest' version on the server.

4. Compares the values. If they are the same the user has got the latest version, so open the one they've got.

5. If they are different, copy the latest version across the network and open that.

6. The .ini file store the Access mdw username for the last user, and is used to 'impersonate' an Access logon, if you've implemented Access security. It's actually a VB form that looks as similar as I could make it to the Access logon. This is so the front ends can be opened without actually hard coding username/password combinations in the shortcut anywhere.

It's worked flawlessly since I started using this. Whenever I've got a new front end, I place it (remotely, as it happens) on the server, and the next time the users start the database it loads the latest one. Unless it's massive they shouldn't notice the delay when it's a new one.

The vb app is about 100 lines of code and one form. There are at least 5 parameters I pass into it, so I can use this with most systems - path to front end, path to update directory, path to security file, table where version number is stored etc.

That's my approach.

FMS sell something like this, and though I have bought many of their products and think they have value, this was one thing that seemed better to do myself.

Caveat. This only works when your users close the database and re-open it. So for a 24/7 operation with 100 users each with a front end it may not work, especially if you want to roll out the updates immediately. But you shouldn't be using Access in that scenario anyway.

But for the usual small workgroup Access setup, especially working 9-5 hours where the machines are closed down at night it's perfect. My clients love asking for a feature at 5.00pm and walking into the office next morning to find it's there!
Jo Davis Send private email
Monday, May 30, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz