Automatic Front End Database Updating

[gold007eye]

How do you automatically update a database to a new version?

I am up for another issue. I am moving the front end of my database off of the network and onto each individuals computer; the only problem is I am not sure how to update their front end automatically if it changes without me having to manually re-install it on everyonces pc. I don't know if there is code or a form type thing that I can have execute on startup that says

If (the network version of the database is newer then then local pc version) then update the local pc version to the newest version and then restart the db running the new version.

Is this possible? I would need the autoupdate to do Tables, Queries, Reports, and Forms.

 

[gold007eye]

45 reviews and no one can give me a clue or hint or something to help me out?
Maybe if I word it differently it will be easier to understand what I am trying to do.

Does anyone know if there is a form or sample database out there that can check the version of a specific database a person is running on their pc against a master version; and if the 2 versions don't match automatically update the local version from the master version?

Is this possible? I would need the autoupdate to do Tables, Queries, Reports, and Forms.

 

[kec]

What I do is, keep an updated copy on the network then send an email to all users with a link to a batch file that deletes the old DB on each system then copies the new version. It works great. That way I don't have to go to all 30 users to update.

I'm sure there are fancier ways to do it, but it works for me.

-Ken

 

[gold007eye]

Could you send me a copy of the bat file or the procedure you use to do this task? I have something similar I was trying that would look at the time and automatically determine changes, but never could get it to work.

 

[kec]

It's just basic batch file stuff...

C:
CD\
CD MAS\
DEL MASQA_Prgm.mdb

S:
CD\
CD Quality_Control\Share\QC_DB\Updating\
COPY MASQA_Prgm.mdb C:\MAS\


That's it. I wouldn't want to have an automatic update. I like to have control of when the change is implemented. I setup a distribution list in Outlook and have a Word template already setup for when I send out the updates. Makes my job much easier.

-Ken

 

[gold007eye]

Thanks for the info> I will give this a shot and see if I should do this instead. My thinking is for the "computer illiterate" the less they have to do the easier it will be.. lol.

 

[ghudson]

I can not provide a sample of what I do but I will list the steps I take to auto-update the users front end with the latest version.

I have a table in the front end which stores the front ends vesion number. I verify that the value in that table is the same in a linked table which sits in the back end. If the version numbers disagree then I call another "utility" database which has the code to do a simple file [from the network] replace to give the user the latest version on their hard drive. Of course you can not over write an open file so I have to close the front end right after the utility db is opened. After the copy is complete then the utilty opens the updated front end for the user and then the utility is closed down. The user does not have to do a thing for all steps are automated. There is a lot more involved to it but that is just an overview as to what I do. It works for me and my users.

 

[kec]

I agree, the easier for the end user the better.

I forgot to mention, In the email I put some text like this:

Please follow these steps:

1. Make sure you close your existing database.
2. Click on the link below.
3. Click on open file when prompted.
4. Click OK.

...so the users knows exactly what to do.

-Ken

 

[pbaldy]

I do something similar to ghudson (version table in front end), but I handle the updates a little differently. I created a utility mde (actually an exe now, but started with an mde) that the users actually start. That utility checks the version on the server against the version on the client. If needed, it copies the newer version down. Then it starts the "actual" mde.

 

[gold007eye]

That is more of what I am looking for. But the one I have I can't get to work. How easy is it to create something like that for my database? I have tried using "access check" but I can't seem to get it to work at all.. I created a batch file to run the exe with the correct parameters, and it runs the code with no errors. but the new database never gets copied from the server to the client. I don't know if I have the tblVersion setup wrong or what I am doing wrong? Any ideas? or could you work with me on making one like you did? Thanks.

 

[pbaldy]

It's not really that tricky. I use FileCopy to copy the file; works fine in either VBA or VB6. In the version table, I have a numeric field for version number, a text field for a description of the changes made in that version, and a date field (the last 2 are optional of course). I compare the max version number on the server copy against the client, copy if different, then start the client copy (using Shell).

 

[gold007eye]

I am a n00b when it comes to vb coding. any helpful code you could send me to push me in the right direction?

 

[faradhi]

Another way to handle this is login scripts if you can talk your systems admin to allow you to add a snippet of code. You can test for the database on the users system and then use robocopy or xcopy to overwrite the local copy if there is a change.

 

[boblarson]

This is the way I automatically update the frontends. In the frontend it has a table with the frontend version and there is a linked table to the backend that has the frontend version. When I change frontends, I change the frontend version on the backend and then when the frontend opens up it checks to see if they match. If not, it tells the user that their frontend is out of date and that it will now close, do the update, and then reopen.

I start a batch file for the copying of the database and then close the frontend.

Since I'm on WinXP, I use a .cmd instead of .bat file (see contents below):

@echo off
cd C:\HDCC
if exist HDCC2_FE.mdb del HDCC2_FE.mdb
NET USE B: \\Cpca_oregon\cpca\HDCCbe
copy /y "B:\HDCC2_FE.mdb" "C:\HDCC\HDCC2_FE.mdb"
@echo on
You can now close this window
@echo off
START /I  "C:\Program Files\Microsoft Office\OFFICE\MSACCESS.exe" "C:\HDCC\HDCC2_FE.mdb" 

Exit

The batch file will check to see if the file exists, if so it deletes it prior to doing the copy (it wouldn't work otherwise), and then it reopens the frontend, which again checks to see if it is the correct version and this time it will be.

Works great.

 

[gold007eye]

Thanks for the code. I set up a batch file the other day (also using WinXP) and it works great. Just an FYI (i don't know if you are doing a .cmd file if it makes a difference) but on your last statement

START /I  "C:\Program Files\Microsoft Office\OFFICE\MSACCESS.exe" "C:\HDCC\HDCC2_FE.mdb"

You could actually write it like this:

Start msaccess.EXE C:\HDCC\HDCC2_FE.mdb

No quotations needed.
Only reason I say this is I noticed an issue with office 2002 & 2003 the folder that MSAccess.exe resides in is different (/Office10/msaccess.exe for 2002 and /Office11/msaccess.exe for 2003), but windows has it setup up so you can just type msaccess.exe from anywhere in the cmd prompt and it will load

 

[faradhi]

Only reason I say this is I noticed an issue with office 2002 & 2003 the file that MSAccess.exe resides in is different, but windows has it setup up so you can just type msaccess.exe from anywhere in the cmd prompt and it will load

This is not necessarly true. There are often instances that someone needs two versions of Access installed. In which case, it is necessary that the absolute path be used rather than depending on the windows path statement.

 

[gold007eye]

Alright you got me there *lol* I never thought of that scenario

 

[DT]

This is the way I automatically update the frontends. In the frontend it has a table with the frontend version and there is a linked table to the backend that has the frontend version. When I change frontends, I change the frontend version on the backend and then when the frontend opens up it checks to see if they match. If not, it tells the user that their frontend is out of date and that it will now close, do the update, and then reopen.

I start a batch file for the copying of the database and then close the frontend.

I'm trying to do the same thing with some databases. The problem I have is after the database FE is copied over the Version numbers are still different because the Master FE is still showing different Versions. How did you get around this?

Thanks,
DT

 

[gold007eye]

My solution to this problem was to create an "Update Database" that has the "BE Version table in it (the BE version would be the updated version) and link that to the Database where the FE resides.

 

[boblarson]

Similar solution. I have a main backend database and replicated versions on all of the computers. I keep them synched by using Microsoft Replication Manager (from Access 2000 Developer edition). Anyway, I can make the change to the FE version number in the table in my replicated db and it synchs to the main database soon after.