Tricky Upgrade of DB Problem

[MLUCKHAM]

How can I overwrite the existing Access database with another Access database without the need for the end user to get involved in the process.

I want the code to copy a new mdb file over the database I am using (!) and then automatically start the new mdb file...:banghead:

 

[namliam]

1) MDB files are office 97 i believe...
2) any file that is currently in use, cant be replaced period.

 

[MLUCKHAM]

I solved it!

In my code I created a batch file on the fly with a timeout /t command of 20 seconds. I then started the batch file with shell start (which spawns a separate process not connected to access) and then called docmd.quit to shut access down.

The batch file starts, counts down 20 seconds and the initiates the copy for the upgrade.

Neat and tidy...

 

[namliam]

How then do you kill the particular session of access?

 

[MLUCKHAM]

Docmd.Quit

using Shell "start batchfilename.bat" spawns a separate process not associated with the MCACCESS.EXE process id. So, I can then quit access, but keep the batch file running. Put a wait of 20 seconds as I have auto-compression built in to my access db and that can take a few seconds sometimes.

 

[namliam]

And then you copy a new front end over the current version?

 

[MLUCKHAM]

Yep, the batch file I call does a copy with a /y switch to force the copy. Filenames are the same. After the copy the batch file then calls: -

start "accessdbname"

to restart the newly copied file (again in a separate process) for the user. Front end upgraded!

 

[gemma-the-husky]

as long as the new version is where it should be.

if not, you may get stuck in a permanent loop.

 

[MLUCKHAM]

Yep, you are right there. However, the only reason why the process would kick in should be because the current version is no longer valid. Which means that someone has updated the main database which "should" mean that they have put the updated access front end file in the upgrade location...

Obviously, human error could play a factor here, but there are other safeguards...

 

[Frothingslosh]

At my last job, I had to make a self-updating front end for Access, and I did it more or less the way MLuckham described. The specifics involved a separate database only containing the current version number and the directory containing ALL the front ends, with each contained in a subfolder with the version number as a name. (I.E. - I:\blah\blah\blah\Project\Versions\2-00-01\FrontEndName.accde) I'm sure a simple text file or spreadsheet would work just as well as a version database, too.

In order to avoid issues, the update routine would compare the front end's version number against the current version in the version file. If they didn't match, the system would then verify that it could find the updated front end file, then it would create the batch file, shell out, kill access, and run the update batch file. (The main reason in verifying the tool can find the updated file is because if you don't, the batch could easily delete the old front end without installing the new one.)

So as long as MLuckham takes things like that into account, he should be fine.