Feeding updates from a newer database version (1.1) to an older version (1.0)

[mose Mbugua]

Hi Guys?. When using a program for example an Antivirus, there comes a time when it "tells" you that there is a newer version available. You then decide either to update it or not. Now my question is if that applies to access databases. Lets say after releasing a version of a database, say version 1.0, you then continue adding new features and debugging it. So you call the newer version 1.1. When you want those changes to appear in previous version (1.0), do you have to import the changes or is there another way that you can feed those new features in the previous database versions?

[twinnyfo]

Mose,

I always have the newest FE on the network. Whenever anyone wants to use the db, I use a command script which copies that front end to the user's local machine. This means that every time I make updates, they are automatically published.

I'm not sure why you would import new features into an old db, or why you would, as a new FE is a new FE.

I'd be glad to share my script if you are interested, but I have it at work and am at home right now.

[mose Mbugua]

Twinnyfo,
i will appreciate that alot.

[twinnyfo]

Open Notepad.

Add this text:

@ECHO OFF
IF NOT EXIST %userprofile%\Documents\DBUser\[Your DB Name].accde MKDIR %userprofile%\Documents\DBUser
COPY [\\Network Location\NetworkFolder\Database Folder\[Your DB Name].accde %userprofile%\Documents\DBUser\[Your DB Name].accde /Y
START /I "MSAccess.exe" %userprofile%\Documents\DBUser\[Your DB Name].accde

Save this file with the ".cmd" extension. Save it in your network location. Any shortcuts to the DB, should actually point to this file.

This file will copy the shared FE from the Network, down to the User's machine, then open it from there.

This makes sure every time a user opens the DB, it is the latest version.

[jforbes]

Coincidently, I was about to create a deployment script when this post showed up. Gotta love it when the code you need finds you. Thanks Twinnyfo!

I took your approach and took it a step further. We have multiple locations across the Midwest, but I want to deploy from one location. Copying the file every time the application is launched could cause some frustration when it's being copied between locations 400 miles apart. So, I added a version .txt file allowing the file copy only to take place if there is a new version.

Since I borrowed the code in the first place, I thought I would give back.

@ECHO OFF
CLS
SET gSourceLocation=\\tserver\Applications\MHSC\
SET gAppFileName=MHSC.accde
SET gVersionFileName=MHSC.txt
SET gAppDir=Documents\Applications\

SET sUserProfile=%userprofile%
SET sSourceFile=%gSourceLocation%%gAppFileName%
SET sSourceVersionFile=%gSourceLocation%%gVersionFileName%

SET sDestination=%sUserProfile%\%gAppDir%
SET sDestinationFile=%sDestination%%gAppFileName%
SET sDestinationVersionFile=%sDestination%%gVersionFileName%

SET sSourceVersion=0
SET sDestinationVersion=0

SET /p sSourceVersion=<%sSourceVersionFile%
SET /p sDestinationVersion=<%sDestinationVersionFile%
IF "%sDestinationVersion%"=="" (SET sDestinationVersion=0)

ECHO ........................................
ECHO Source Version File: %sSourceVersionFile%
ECHO Current Version File: %sDestinationVersionFile%
ECHO ........................................
ECHO Application: %sSourceFile%
ECHO Local Directory: %sDestination%
ECHO Current Version: %sDestinationVersion%
ECHO Availiable Version: %sSourceVersion%
ECHO ........................................

IF NOT EXIST %sDestination% (
ECHO Making Destination Directory...
MKDIR %sDestination%
)

IF  %sDestinationVersion% LSS %sSourceVersion% (
ECHO Installing latest version of the Application to this computer
ECHO Please be patient, this should take no more than 30 Seconds...
COPY %sSourceFile% %sDestination%
ECHO Copying Version File to Local Computer...
COPY %sSourceVersionFile% %sDestination%
)

START "MSAccess.exe" %sDestinationFile%

To use this, update the four "constants" at the top to reflect your environment. Then create a text file (mine is called MHSC.txt) and put a version number in it.
Then give your users a link to it. Finally, when you have a new version of the code available, Increment the number in the .txt file

[twinnyfo]

J,

Great script! I like it. I admit I know very little about Command Scripting, hence the very basic nature of my version.

I think I may also add some code to my FE, such that when I compile and relink all my tables (which I do programatically ), it will automatiaclly update the version text file. Then I woun't forget.....

Thanks again!

[jforbes]

That is an incredible idea. I think I'll work on that today. =)
Thanks again!

[twinnyfo]

Here is my go at it--I'm sure there are probably better ways to do it....

Public Sub UpdateVersion()
On Error GoTo EH
    Dim oFSO As FileSystemObject
    Dim oTS As TextStream
    Dim oTSTemp As TextStream
    Dim strTemp As String
    Dim strText As String
    Dim intVersion As Integer
    strTemp = gstrVersionFile & "tmp"
    Set oFSO = New FileSystemObject
    Set oTS = oFSO.OpenTextFile(gstrVersionFile)
    strText = oTS.ReadLine
    oTS.Close
    Set oTS = Nothing
    Set oTSTemp = oFSO.CreateTextFile(strTemp)
    intVersion = CInt(strText)
    intVersion = intVersion + 1
    oTSTemp.WriteLine intVersion
    oTSTemp.Close
    set oTSTemp = Nothing
    oFSO.DeleteFile gstrVersionFile, True
    oFSO.MoveFile strTemp, gstrVersionFile
    Set oFSO = Nothing
    Exit Sub
EH:
    MsgBox "There was an error updating the version text.  " & _
        "Please contact your Database Administrator.", vbCritical, "Error!"
    Resume Next
End Sub

gstrVersionFile is a global variable with the path and file name.

[jforbes]

Thank you. I stole your code. =)

[twinnyfo]

Serves me right for stealing yours!

:-)