Need a solution for database update

[perlfan]

Hi there,

I have an Access application which I distribute either as network version (with MySQL as backend) or as "offline" version using the standard access db as backend. For users with the standard offline version I have implemented a backup feature using the common VBA method which copies all data into a seperate mdb file. Now my problem is - how can I distribute updates to these users?? Such updates often include database changes. As the implemented backup method does not exclusively exports/imports raw data I cannot simply hand out the updated version, as users would import their data and also the old table structures etc. I hope, you'll get my point!
What would be your suggestion to update the application/db then? For MySQL users there is no problem as they can simply run the sql alter commands themselves and install the new frontend version... thanks for any advice in advance!!
FRANK

 

[StarGrabber]

You can run 'sql alter commands' from any frontend. I prefer to do it using ADO commands.
I suggest to implement a second 'backup feature' for your 'offline version'.

See also: http://msdn.microsoft.com/en-us/library/ff196148.aspx

 

[perlfan]

This is exactly what I am trying to find out - how I can make the users of my application run certain commands which will update the tables they are using. Would you let them paste the commands in a form or is it possible to execute a file for instance? It should be really easy and ideally users wouldn't even see the code.

 

[StarGrabber]

If this is a real enterprise application of course they should not be able to see the code.

I would do it like this:

- When the user starts the frontend, run a check to find out the backend version.

- If it's not the actual one, run some code that executes the 'alter commands'.

- If successful, update a version flag in the backend.

 

[perlfan]

Great idea - thanks a lot. I always focused on creating a little update file that would contain the update commands instead of simply implementing everything in the application.

For me it's now important to see if MySQL needs other sql alter commands than the Access db for offline users. In this case the app would need to check which db is used and then execute the appropriate commands.

 

[StarGrabber]

Precisely!

As I'm not familiar with MySQL, I cannot advise but it shouldn't be that different.

Good luck with your project!