end-user automatic update??

[SJG0526]

I'd like to ship end users an updates.mdb that contains all objects from an access97 database (mailing.mdb) that have been updated. Then I'd like them to run a menu option or something that will make a backup copy of the mailing.mdb, delete the objects in mailing.mdb that are in updates.mdb and install the new versions of the objects from updates.mdb into mailing.mdb. Make sense? Does anyone have any hints on how to achieve this?

Thanks,

Sally

 

[beetee]

The problem you are describing is why people usually use a FE (front end, with queries, forms, macros, reports, and code) database and a BE (back end, data only) database.

You can always ship a brand new FE database without upsetting your client's databases.

However, perhaps you have a database that clients are allowed to modify, and you wish to update only your objects.

Two solutions come to mind:

1) You could perform a VBA update, but the code is a bit advanced. However, it can be supplied if needed.

2) A non-coding option is:
a) In the update database (source), create a macro that will be exported to the original (destination) database. The macro contains a number of TransferDatabase actions, each importing an item from the 'update' database. The location of the update database will have to be hard-wired.
b) Create another macro that exports the update macro to the original database.
c) create a batch file that runs the update database with a startup macro (defined in (b)), and then runs the original database with a startup macro (defined in (a)).

the batch file would have commands like

C:\Program Files\Microsoft Office\Office\MSACCESS.exe UpdateDatabase.mdb /x MacroB

C:\Program Files\Microsoft Office\Office\MSACCESS.exe OriginalDatabase.mdb /x MacroA

This approach hasn't been tested and may need some refinement, but it seems sound.

 

[SJG0526]

beetee,

Thanks for the help. I'm going to try out the macro approach altho I'd be interested in the code version also if you have it.

 

[SJG0526]

beetee,

The macro seems to work if I put it in updates.mdb and use a bunch of TransferDatabases in a macro to export to Mailing.mdb. It even overrights the existing object in Mailing.mdb with the same name if it exists. All I should have to do now is create a batch file to call the macro in Updates.mdb. Sound right?

 

[JeremyNYC]

SJG,

A quicker, more professional method, that causes far less file bloating, and is far less likely to cause corruption, is to use a batch file to roll out a new version to users whenever the one on their desktop is out of date, and a text file to keep track of what version of the database is the latest one.

Here's the text of a batch file I wrote for one of my clients.

rem make the directories (no harm if they're already there)
c:
cdcd "Program Files"
mkdir "NRP Database"
cd "NRP Database"

rem If the latest version file is on the PC, just start the database
if exist "NRP_FEVersion.1.83.txt" goto Startup

rem copy the images and shortcuts
copy "\\Nrp\C\NRP System\NRP_FE.bmp"
copy "\\Nrp\C\NRP System\ReportFooter.bmp"
copy "\\Nrp\C\NRP System\ReportHeader.bmp"
copy "\\Nrp\C\NRP System\NRP Database.lnk"
copy "NRP Database.lnk" "C:\Documents and Settings\All Users\Start Menu\NRP Database.lnk"
copy "NRP Database.lnk" "C:\Documents and Settings\All Users\Desktop\NRP Database.lnk"

rem copy ABCD Library
copy "\\Nrp\C\NRP System\ABCDLibraryXP.mde"

rem Copy a new front end
copy "\\Nrp\C\NRP System\NRP_FE.mdb"
copy "\\Nrp\C\NRP System\NRP_FEVersion.1.83.txt"

:Startup
rem fire it up
"C:\Program Files\Microsoft Office\Office10\msaccess.exe" "c:\Program Files\NRP Database\NRP_FE.mdb"

This way, any time I have a new front end, I just put it in the correct directory on the server, change the name of the referenced text file, and change the two references in this batch file.

When a new users "opens the database", what she's really doing is clicking on an icon that points to the batch file (in a directory on the server). If she has the newest version file on her machine, she has the newest front end on her machine, so the batch file just starts the front end she's got. If the version file on her machine doesn't match the version file on the server, she needs the new front end, so first it's copied to her machine and then it's started.

Jeremy

PS: If you haven't yet split your front end from your back end, you _really, really_ should do so, immediately. Not doing so causes lots of extra network traffic and greatly increases the likelihood of corruption.


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[beetee]

Sally,

Yes, creating a batch file that performs the transfer should do the work.

Again, I'm assuming you're not using the split database approach (which would be the preferred method) because of your requirements for this task.

When you make the batch file to run the update macro, you might want to have the last command of the update macro perform a 'quit' command. As Jeremy so rightly points out, the updates will 'bloat' your database. A second entry in your batch file should invoke Access with the /compact parameter in order to compact the database.

Good luck!
Bill

 

[MichaelRed]

see Thread181-30072 -but only if you really - really - really - really - really want to muck through and mull over code.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[oharab]

Or have a look at faq705-2010 for a way of updating your frontend file.
Again, as has been said before, split your database. Not doing so is asking for trouble!

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------