Automatic update database problem.

[softlover]

I met a same problem as described in thread705-1067773.
I open my current mdb file (C:\TEST.mde) and check it with new version mdb file on the network. If it neet update then creat a batch file which includes the "COPY \\server\TEST.mde C:\TEST.mde /Y" command.
Now I want to close the current mdb file and then automatic run the batch file to replace the current mdb file with new one.

 

[HaworthBantam]

Dim varBatchFile

Make the check to see if the file needs updating, if it does then...

varBatchFile = Shell("[pathway to batch file]")
Quit

To ensure that the batch file allows the time required for Access to shut down, place a Pause withing the batch file before the copy occurs.

Once the user does the required "Press any key to continue", Access should have shut down and the copy should work ok.

 

[HaworthBantam]

Oops, varBatchFile line should read:

varBatchFile = Shell("[pathway to batch file]", 1)

 

[Remou]

You need to start your database with script, rather than have the database check for new versions. Here is a useful link:

http://www.abcdataworks.com/deploy.htm

 

[BuilderSpec]

Hi

What you could do is this..

open the database and have the VBA code check to see if there is an update. if there is then create the batch file you mentioned..lets call it "update.bat".

When your db opens if you ask it to close in the same routine it is using while opening the it won;t like it. For example you cannot put an application.quit command in the first form's Form_Open() procedure..it won;t like it..
What you can do is in the Form_Open() procedure :

1. Do the version check
2. If you need to close then set a timer for say 3 seconds
3. In the timer event you should now be able to quit the database

The next bit is important too..

Instead of starting the databse use a batch file to do it. I create a batch file call startdb.bat and it contains:

if exist update.bat del update.bat
c:\<path to access>\msaccess.exe <pathtodb>.mdb
if exist update.bat update.bat

this should now run your db and do the update if necessary.




Hope this helps!

Regards

BuilderSpec

 

[softlover]

I find another way:
Open the database "TEST.mdb" normally and check if there is an update on the server. if there is then create the batch file "update.bat" as below.
@echo off
:WaitFin
rem if the current mdb file not closed then loop.
if exist Current_Database_Folder\TEST.ldb goto WaitFin
rem do copy event.
COPY \\server\TEST.mdb Current_Database_Folder\TEST.mdb /Y
rem then open the updated database (new version)
start Current_Database_Folder\TEST.mdb
EXIT

Then in the VBA codes:
Call Shell(Environ("COMSPEC") & " /C update.bat", vbHide)
DoCmd.Quit

After current mdb file closed with command "DoCmd.Quit",the batch file check that the mdb file closed with ".ldb" file disappeareda and then do the copy event.

All these codes run well under ACCESS2K.

Thanks to all.

 

[MKH2001]

Softlover,

This looks exactly like what I need for my database.

Unfortunately I am having troubles figuring out how you have implemented your resolution.

Could you give me any pointers where to execute which pieces of code etc.

 

[softlover]

Hi, lidias13,
The following codes run ok under ACCESS2K on my PC.

'Get the Server Update Information and date
Set MyWsp = DBEngine.Workspaces(0)
Set MyDb = MyWsp.OpenDatabase(ServerFolder & "Update_Inform.mdb", False, False)
Set MyRst = MyDb.OpenRecordset("SELECT UpdateDate.* FROM UpdateDate;")
MyRst.MoveFirst

'Get the Update Date.
ServerUpdateDate = MyRst.Fields("Update_Date").Value

'Get the Database name need Updated.
TargetName = Trim(MyRst.Fields("Update_Item").Value)
MyRst.Close: MyDb.Close

'Get Local database update Date.
Set MyDb = CurrentDb()
Set MyRst = MyDb.OpenRecordset("TblUpdateDate")
MyRst.MoveFirst
LocalUpdateDate = MyRst.Fields("Update_Date").Value

If LocalUpdateDate = ServerUpdateDate Then MyRst.Close: MyDb.Close: GoTo Form_Load_Exit
If MsgBox("Your Database Need Update, Make it?", vbYesNo) = vbNo Then GoTo Form_Load_Exit

'Update the Update date table
MyRst.Edit
MyRst.Fields("Update_Last") = MyRst.Fields("Update_Date")
MyRst.Fields("Update_Date") = ServerUpdateDate
MyRst.Update
MyRst.Close: MyDb.Close

'Begin Update process.
Set MyDb = CurrentDb
DbName = CurrentDb.Name
LdbName = Left(DbName, Len(DbName) - 3) & "ldb"
MyDb.Close
If Dir("C:\WINDOWS", vbDirectory) = "" Then MkDir ("C:\WINDOWS")
BatFile = "C:\WINDOWS\UpdateMe.bat"

Open BatFile For Output As #1
Print #1, "@echo off"
Print #1, ":WaitLoop"
Print #1, "Echo"
Print #1, "if exist " & LdbName & " goto WaitLoop"
Print #1, "Echo"
Print #1, "Echo"
Print #1, "COPY %1" & TargetName & " " & DbName & " /Y"
Print #1, "Echo"
Print #1, "start " & DbName
Print #1, "Echo"
Print #1, "EXIT"
Close #1
Call Shell(Environ("COMSPEC") & " /C " & BatFile & " " & ServerFolder, vbHide)

Set MyRst = Nothing: Set MyDb = Nothing: Set MyWsp = Nothing
DoCmd.Quit acQuitSaveAll

Form_Load_Exit:
Set MyRst = Nothing: Set MyDb = Nothing: Set MyWsp = Nothing: Exit Sub
Form_Load_Err:
Resume Form_Load_Exit
End Sub

It's not the best one but can work.

 

[Techniproshop]

Hi all. I was using a batch file to update a new version of my database and the script I was using under Win2k was varBatchFile = Shell("M:\04 - Adjudanterie\01 - Interne partagé\BAB\SGAD\Installation\Install_SGAD.bat", 1)

When I was opening my database and a new version was available, the database was calling the batch file and then shutting down. The batch file is still working if I run it manually (double click on) but my shell command doesn't work. It looks like the path name isn't valid but this is the good one so I don't know what to do. Is there anything to do with Win XP?

Thanks.

 

[BuilderSpec]

Hi

Or even...

Why not create a separate program or database altogether that job is to :

1) check the version information of a specified database
2) update it locally if needed
3) fires up the local copy of the database
4) exits

this way you are creating a mini "launcher" but since you are not launching the same database or app you don't need to worry about stuff like trying to close an app while it;s opening etc..

PLus..

if you pass 2 parameters to the launcher you could tell it where the local copy is and where the netwrok copy is and voila..you;ve just created a generic app version checker and launcher for ALL your databases at once. this saves having to replicate the launch code in every app u make.

the desktop shortcut weill be to the launcher and the launcher is what ultimately loads your actual application database.

Regards

BuilderSpec


Hope this helps!

Regards

BuilderSpec