Automatic Front End Database Updating

[ray147]

hi,

i'm currently writing a database that will be used by 5 users, some of whom will have different access rights. i'm not quite sure about the frontend/backend issue ... right now i have the data and the forms residing in the same mdb file on the server...eventually i was gonna let each user access the mdb database through the server shared directory.

can u give me any ideas if this is a good way of doing it? where would the frontend/backend issue come in ?

10x!

 

[gold007eye]

i'm not quite sure about the frontend/backend issue ... right now i have the data and the forms residing in the same mdb file on the server...eventually i was gonna let each user access the mdb database through the server shared directory.

My suggestion is to always have the Data in a "backend" that way if something goes wrong with the front end you can still access the data. Also doing this allows you to put a front end on each users local computer that connects to a central location where the data resides.

What I also do is have a mdb called "update" anytime the user opens the database it automatically looks to the update.mdb to see if there is a newer version and if so closes their current database and deletes the old version and copies over the new database version (using a .bat script) to their local PC. I have a query that compares the FEVersion and the BEVersion. Also I keep a copy of the FE Database (Master Copy)on the server in the same location as the BEDatabase.

I hope this helps. It sounds confusing, but once it is implemented it goes smoothly and is much easier to impliment version updates.

 

[qqq]

Sounds all very useful, thanks. One open issue: I don't know where users will save their FE file... Is there maybe a way of automatically inserting the path of the FE into the .cmd file through vba? This would be very helpful!

 

[boblarson]

I do it the easy way. I make them save it in a specific directory.

 

[qqq]

that's easy of course. i think i found a way to paste the FE path into the cmd file before executing it. but then there's another thing: if i trigger a cmd file from my OldFE.mdb, how can this delete OldFE.mdb ?? Is there a way of closing triggering a cmd file with a delay, so that it waits until the mdb file is closed? or any other ideas? thanks for your help.

 

[gold007eye]

The way I have it is I have the FE in the users local database. so when you update the "master database" you change the FE version in that.. Then I use a .bat file to execute the update for the user. If it sees a new version is available it closes the database, runs the .bat file (which deletes the old version and copies the new version) then runs a command to re-open the databse (now being the updated version).

@Echo off
Color 4E
Title PELA is Now Updating to a NEW Version, Please Wait.....
@Echo on


@Echo Please wait while PELA updates to a NEW version...................


@Echo off
C:
CD\
CD PELA\
DEL PELA.mdb

G:
CD\
CD Hingham Drives\G Drive\MCU\PELA\
COPY PELA.mdb C:\PELA\

C:
CD\

Start msaccess.EXE C:\PELA\PELA.mdb
@echo On

The above code is how I have my "update.bat" file setup.

 

[ejstefl]

Maybe a dumb question - but how do you call a .bat file from Access after you've already closed it? I understand how you would check the version when you open the database, but I don't get how you'd call the .bat file after closing it...

 

[gold007eye]

Because when the database senses an update is needed it brings up a form that says to Update.. when you click on the "Update" button there is code in the "OnClick" function that tells it to run a macro called "Version Update"

Here is the layout of that macro:

Action: RunApp
Command Line: G:\Hingham Drives\G Drive\MCU\PELA\PELA_Update.bat (the
link to where the .bat file is stored.)
======================================
Action: Quit
Options: Exit
======================================
So it basically starts the batch file and the quits out of the database currently open Then when the .bat file is done running the update it has the command to re-open the database. Does that help any?

 

[ejstefl]

Ahhhh, makes sense... I figured that when you kicked off the batch file it would execute before you had a chance to quit the database. Thanks!

 

[MsLady]

quality gurus

I have learned soo much from thsi thread!! Thanks all
My new version updates the old one perfectly by checking user's version against server's version and running a bat file from macro to replace with new front end.


I have one question that i think would be useful to me even during initial deployment of my database application to my users.

How do i tell my bat file (command file) to save the copy to the user's desktop?
This currently copies it to C drive. How do i tell it to copy it to the user's specific desktop
eg. C:\Documents and Settings\JohnDoe\Desktop\

Please help?

Z:
CD\
CD Z:\server\projectsApp\
COPY dbthing.mdb C:\projectsApp\

 

[ghudson]

"C:\Documents and Settings\" & Environ("UserName") & "\Desktop\"

But I suggest that you use the "All Users" directory instead and put it in a new directory, not the desktop.

C:\Documents and Settings\All Users\YourDM\

I like to use the WinZip Self-Extractor 2.2 program to install files to a users computer. It only costs @ $30 [in addition to buying WinZip] and it allows you to hide the extract to settings so that the user can not change the extraction location when they run it [the version that comes with WinZip does not give you extra customizable options].

 

[MsLady]

"C:\Documents and Settings\" & Environ("UserName") & "\Desktop\"

But I suggest that you use the "All Users" directory instead and put it in a new directory, not the desktop.

C:\Documents and Settings\All Users\YourDM\

I like to use the WinZip Self-Extractor 2.2 program to install files to a users computer. It only costs @ $30 [in addition to buying WinZip] and it allows you to hide the extract to settings so that the user can not change the extraction location when they run it [the version that comes with WinZip does not give you extra customizable options].

Okay, Thanks! sounds brilliant. If i put it in all users like this:
C:\Documents and Settings\All Users\YourDM\

How can i still have it send a shortcut to the desktop?

 

[gold007eye]

How do i tell my bat file (command file) to save the copy to the user's desktop?
This currently copies it to C drive. How do i tell it to copy it to the user's specific desktop
eg. C:\Documents and Settings\JohnDoe\Desktop\

Hello MsLady I am glad that this thread has been informative for you If you want to send a shortcut to the desktop the best thing to do is create the shortcut locally and then save it server side so you just give the .bat a command to copy that .lnk file to the users desktop

G:
CD\
CD Hingham Drives\G Drive\MCU\PELA\
COPY PELA.mdb C:\PELA\
Copy PELA_Live.lnk C:\PELA\

D:
CD\
CD %userprofile%\Desktop\
Copy C:\PELA\PELA_Live.lnk

DEL C:\PELA\PELA_Live.lnk

C:
CD\

Start msaccess.EXE C:\PELA\PELA.mdb

Hope this helps some.

 

[ghudson]

I would advise not using batch files for you can do what you want with an mdb file. But the choice is yours. The batch file will not work if the user has the db that you want to replace already opened.

 

[MsLady]

Hi gold007eye, that's a good idea!! thanks alot.

Ghudson, I put an option in my macro (that the batch file runs) to close the App. I am not sure what you mean, can you explain? I am a little behind in this area
How do you do that with mdb Please share. has it been answered somewhere here, perhaps you can give me the link?

Thanks guys! im very greatful

 

[ghudson]

This thread will help you close a file [db]...
Remote shutdown of a shared DB...

Here is an easy way to copy a file using VBA...

FileCopy ("\\Server\Partition\Directory\File.lnk"), ("C:\Documents and Settings\All Users\YourDB\YourShortcut.lnk")

 

[MsLady]

wow!! that's a very informative thread.
THanks for the code and thanks for all you help

 

[Keith Nichols]

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.

Hi Bob,

Having walked the halls doing multiple front end upgrades as I beta test my DB with real users, I'm keen to implement this but would need a little guidance to get me started.

Firstly, the version checking would be easy as I already have a log all users out system in place cribbed from this site. This checks a check box in a back end table and initiates a front end shut down if it is present. The table this is in already has the version number in. All I need to do is add a table to the front end and compare the fields. I should be able to work that out without to much headscratching.

What I don't know is how to create a batch file, where to keep it or how to initiate it from within Access. I was never into MS DOS (isn't that where batch files live?) and I'm sure anyone who was would be able to knock this off in a trice.

Here's hoping that someone can set me off on the right path.

BTW - I have seen several posts, BobLarson's amongst them, signed of with "hth". Anybody care to enlighten me as to the significance of this?

Regards,

Keith.

 

[Keith Nichols]

My previous post seems to have jumped threads somehow. Still, we are in the same area so the question remains: How do I create and call a batch file to update the front end.

Regards,

Keith..

 

[gold007eye]

If you read my posts Keith you should get the basic idea of how to accomplish this. Use notepad to create the .bat file.... when you go to save the file go to save as: and save it like such: Update.bat or whatever you want the file to be named.