Distributing updates involving structural changes to back end

MadSmoke

Registered User.
Local time
Today, 22:39
Joined
Jul 11, 2007
Messages
11
Hello All!

I am running Access 2003 on XP SP2. Developing a small application for a client running Access 2000.

The developed database (‘Main Application’) consists of a front end residing on users machines and the back end residing on the server.

I now need to make some changes to the backend and am trying to develop a solution that enables me to e-mail a file (with some instructions) to enable the user to update the back-end at the click (or clicks!) of a button.

Currently I am writing a small Access database (‘Updater’) that will do this, but as this is taking a little time, wondered if I am missing a quick and easy trick?

I essentially need to modify one table in the back end (add an extra field) and then run a couple of queries to populate this field with data.

The approach I am taking is, in essence:
Check all users are out of database (‘Main Application be’)
Lock database (‘Main Application be’) to prevent users re-going in whilst the changes are made.
Backup the ‘Main Application be’ from the ‘Updater’
Import a table from the ‘Main Application be’ to the ‘Updater’
Inside the ‘Updater’ –
• add the extra field / column to the imported table,
• run the queries insider the ‘Updater’ on the new data,
• delete the original table in the ‘Main Application be’,
• export the modified table back from the Updater to the ‘Main Application be’,
• release the lock on the ‘Main Application be’.

The users will update their own front ends by creating a new mde to replace their existing one.

The reason I am exploring this approach is that the user already has tables populated with data which need preserving.

Besides I thought it would be useful to develop an approach for remotely updating database – I have only been ‘programming’ for a year so am relatively new to this…

As I read the forum (and having tried to search for an alternative approach before posting this) I become increasingly aware of how little I know, so thought it worth asking the question before I re-invent the wheel…

Any responses gratefully received!

(I hope this makes sense? I know what I am talking about!):)
 
This will do a few thing you want...

1. Hides the Access window
2. Allow you to select the backend file.
3. Copy new tables
4. Edit existing tables.

And has a professional look.

The zip contains 2 files. Unzip them to a folder somewhere. Run the "Update Patch" and navigate to the "BackEnd" file.


Hold down the shift key when opening either Db to see how it works.

Dave
 

Attachments

Also, you dont need to import the table back into your "Updater" to add fields and update data. The above example shows how to add a field to an existing table, then it is simply a matter of creating update queries in the updater and running them from there.

I have done this in the past, but can't lay my hands on an example. Will keep searching

Dave
 
Last edited:
This sample wont work (it doesn't have the back end file) but will show how to run the queries as I mentioned. Hold down the shift key when opening.

Dave
 

Attachments

Thanks Dave

I'm looking at this now! It certainly runs very neatly! Just looking at the code now.

:)

(also just trying to imagine no hypothetical questions...!)
 
Hi Dave

Thanks for all of this – extremely useful and will form a great template for what I am looking to do! I will just add the words “update patch” to this posting, as I didn’t think of the word “patch” when doing my searches…

Yes I can see that the queries run on the tables linked from the back end, and that if you can make the changes directly in the back end, that this will avoid the need to import and then export the tables. This was useful to point out as I was getting to the stage of being too close to the problem to see the solution!

You do not seem to lock any other user out of the back end when making the modifications - or have I missed it? It is obviously not necessary (or Access handles it)? (rhetorical question?)

I was checking for the existence of an ldb file to determine whether anyone else had the back end open. If this was found using a message box to ask the operator to ensure that all users are out of the system before checking again and proceeding if clear.
In relation to locking the back end, I had intended to open it exclusively with code adapted from http://support.microft.com/kb/198756

In your experience is there any benefit in doing this, or would I just be wasting my time?

Many thanks again – a cool, efficient and very useful little application!


:)

Regards

MS
 
As the Db's I write only have a limited number of users, I have never had the issue of users altering the data while updating. I have never thought of locking the Db.
 
Just had a play around. If the Main Db is opened and linked to the backend you can still add a table to the backend through your updater. If the table you want to add the field to is not open, it will work, but if someone has a form open that uses the table it will not update.

As I dont have many users I just instruct my clients to "make sure no-one is using the Db". So a MsgBox would probably do the trick as you suggest.

Dave
 
Thanks again Dave - you have been very helpful.

I think I have enough now to finish my updater.

In essence this is what I will now be doing:
1. Check for the presence of the ldb file. If present message box to ask the operator to ensur all users to log out of the database. (Use Cancel Retry Options and a Do While (ldb exists) loop.)
2. Perform a backup of the backend (using the FileCopy Statement)
3. Lock the database for exclusive use by the updater
4. Add the field to the backend database
5. Run the query in the Updater which is linked to the table in the back end
6. Release the lock on the database

Encase all this in your application which looks smart, hides the Access database window, keeps the operator informed of progress and closes itself when finished (as well as as a few other bits and bobs).

I'm not sure if I'm going to have time to finish this week, but will post a copy of the updater here when finished.

Many thanks again

Best wishes

MS :)
 
Broken Link?

The link below does not work anymore. Does anyone know the new location?

Hi Dave

Thanks for all of this – extremely useful and will form a great template for what I am looking to do! I will just add the words “update patch” to this posting, as I didn’t think of the word “patch” when doing my searches…

Yes I can see that the queries run on the tables linked from the back end, and that if you can make the changes directly in the back end, that this will avoid the need to import and then export the tables. This was useful to point out as I was getting to the stage of being too close to the problem to see the solution!

You do not seem to lock any other user out of the back end when making the modifications - or have I missed it? It is obviously not necessary (or Access handles it)? (rhetorical question?)

I was checking for the existence of an ldb file to determine whether anyone else had the back end open. If this was found using a message box to ask the operator to ensure that all users are out of the system before checking again and proceeding if clear.
In relation to locking the back end, I had intended to open it exclusively with code adapted from http://support.microft.com/kb/198756

In your experience is there any benefit in doing this, or would I just be wasting my time?

Many thanks again – a cool, efficient and very useful little application!


:)

Regards

MS
 
Updater

Hi All

A copy of my updater software slightly modified from OldSoftboss's.

I have commeneted most code but left it in to remind me of the flexibility In useing this approach to upate front end and backends.

In fact about to use it again in association with Bob Larson's "Access Front-end Auto-Updating Utility" (http://www.access-programmers.co.uk/forums/showthread.php?p=506695#poststop)

To further improve the ability to remotely support an early application...

Thanks again (OldSoftBoss) for all help - hopefully this will be of some use to someone...

MS
 

Attachments

Just a quick footnote

For those new to this game - I thought it might be worth mentioning the TransferDatabase Action will also allow exports of Forms and other objects, over-writing other objects of the same name. Again quite cool for remotely updating a users Front end as well.
 

Users who are viewing this thread

Back
Top Bottom