Making Database Open Current Version Automatically

[djezesk]

I have a database that gets update periodically and is on a network server. Because the database is very large (10g+)the company is having users who are working remotely have a local copy of the database on their computer. My job is to create either a batch file or VB Script that will run every time the database is opened. It will need to check for an updated version and replace it with the newest one if there is a newer one available. If there is no newer one available then it will just open like normal.

So far I have

COPY \\server\TEST.mdb C:\TEST.mdb /Y

Now how do I make the changes listed above to that? I have never had to make a batch file before and haven't had to do anything with VB in a decade. Please Help ASAP!

 

[addy]

OK, this is how I do it to ensure users always have the latest version of their Acces Front End Databases available.

I use two tables, one local to the .mdb file called tbl_VersionClient, the other held in the relevant SQL database that the front end is looking at called tbl_VersionServer (in your case, you could put an .mdb anywhere on your network to hold this table). Link the tbl_VersionServer table to your Access front end.

I then have a Splash Form which opens when the .mdb is opened called frm_Splash. I generally set the following code to activate on the 'On Timer' event, and I set the Timer to 1000 or 2000 so the users see the form for a second or two before the code is activated. In here I have the following:

Me.TimerInterval = 0

strVerClient = Nz(DLookup("[VersionNumber]", "[tbl_VersionClient]"), "")
strVerServer = Nz(DLookup("[VersionNumber]", "[tbl_VersionServer]"), "")

If strVerClient = strVerServer Then

/put some code in here as to what you want your database to do if it is at the current version. Usually this is to close the Splash Form and Open up a Menu Form or similar

Else

    Dim strSourceFile As String, strDestFile As String
    Dim strAccessExePath As String, lngResult As Long

    'Create the source's path and file name.
    strSourceFile = "\\FullPath\YourDatabase.mdb" ' This is the full path to where your 'Master' or Latest Version database sits on the Network
    strDestFile = CurrentProject.Fullname

    'Determine path of current Access executable
    strAccessExePath = SysCmd(acSysCmdAccessDir) & "MSAccess.exe "


    If Dir(strSourceFile) = "" Then 'Something is wrong and the file is not there.
    MsgBox ("Please see system administrator")
    Else 'copy the new version of app over the existing one.
    lngResult = apiCopyFile(strSourceFile, strDestFile, False)
    End If

    'Modify strDestFile slightly so that it can be used with the Shell function
    strDestFile = """" & strDestFile & """"

    MsgBox "Application Updated. Please wait while the application restarts.", _
    vbInformation, "Update Successful"

    'Load new version, then close old one.
    Shell strAccessExePath & strDestFile & "", vbMaximizedFocus

    DoCmd.Quit

End If

In addition to this, you need to put the followig into a Module:

Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long

So, if the two version numbers do not match, then the Master, or Network version will be copied to the User and will replace their existing copy.

If you need to roll out an update to the Master, simply make your changes and then update both the tbl_VersionClient and tbl_VersionServer to be the same Value. If these are different in the Master copy, then Users get stuck in a loop.