batch file to check "version" of a file (1 Viewer)

[delikedi]

I use a batch file to distribute the front end of a database application. Currently, everytime the user clicks the shortcut on her desktop, the batch file executes and copies the front end from a network location to user's local machine. The FE is updated like every couple of days. The users run the database more frequently.

I would like to modify the batch file so that it checks some attribute of the FE file to decide whether it needs to be updated. I can't rely on file size, since the FE includes temporary tables. I essentially need something like the "tag" property of form controls, only for files. It would be ideal if it was me who sets this property, like "version number". Except it has read without opening the file itself.

Any ideas?

 

[Pat Hartman]

Add a table to the FE with a version number. Add a table to the BE with a version number.

Using VBScript (just like VBA but without dim statements), read both tables and compare the values. Load a new copy of the FE if necessary

 

[robynb]

My question is similar, but a batch file is run to copy the front end file from the server to the users desktop each time they log on. Is there anyway that the batch file can find the date of the front end on the desktop, then compare to the server copy. If the modified date is the same, then no need to copy - thanks anyone:banghead:

 

[Pat Hartman]

Welcome Aboard VBScript is very much like VBA. The major difference being you don't declare your variables. Keep in mind that you can't compare the external DOS file modified date since Access modifies the date of every database as you close it. Use internal tables and write a query to compare them.

Create the VBA in Access so you can use the IDE. Then move it to a text file where you can execute it.

 

[delikedi]

I was going to reply to Pat's suggestion after successfully implementing it but when more pressing matters arise you lose focus. I only got to discover that VBScript is utilized by creating simple text files with the extension ".vbs". If for example you open notepad and write MsgBox "U can has Front End" and save it as SomeFile.vbs, you can double click it and see the message run like in VBA. To run it from a batch file, you add the line
Start C:\SomeFolder\ReplaceFrontEnd.vbs
to the batch file.

I believe querying the front end and back end files of a database application in this fashion is simple as Pat says, however I never found the time to google it. One thing I won't shy away from asking him though: Do you have to use ADO objects?

 

[Pat Hartman]

I never use ADO objects. I do use DAO objects but for this purpose DLookup() should suffice.

 

[robynb]

Many thanks. I created a dos batch file to copy the files down and this code is in the user logon script when they connect to the network. They dont modify the front end, only use for data entry, so the modified date doesnt change. This is the date I need to compare, without opening a db to do the comparison. I dont know if the dos file can do this -- not much of a dos user these days. Can you help with the code needed?

 

[delikedi]

I was able to implement Pat's suggestion. I created "tblVersion" in my database and I can query the value in that table with the following code:

'create a connection and open it. 
Set conConnection = CreateObject("ADODB.Connection")
strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\fimOS\fimOS.accdb;Persist Security Info=False;"
conConnection.Open strConnectionString 

'query the database through the connection
strSQL = "SELECT Version FROM tblVersion"
Set rstVersion = conConnection.Execute(strSQL)

If Not rstVersion.EOF Then
    strValue = rstVersion("Version")
Else
    strValue = "Record not found"
End If

rstVersion.Close
Set rstVersion = Nothing
conConnection.Close
Set conConnection = Nothing

MsgBox strValue

If you are a vbscript first-timer like me, just copy the code into notepad and save the file with .vbs extension. You have to change the source file path though, and the connection string will probably be different if your file is a mdb file.
You can then run it like an exe file, or run it from within a batch file.

Thanks Pat for pointing me in the right direction.

@ robynb
I believe the following code would help in your case:

'provide the path of the file here
strFullPath = "C:\fimOS\fimOS.accdb"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(strFullPath)

'the following are three properties of a file.
'you can use other properties listed at 
'http://msdn.microsoft.com/en-us/library/1ft05taf%28v=vs.84%29.aspx

MsgBox objFile.DateCreated _
    & vbcrlf _
    & objFile.DateLastModified _
    & vbcrlf _
    & objFile.DateLastAccessed

 

[robynb]

I have tried the modified date - which works fine, but it does show that the file has been modified each time it is opened, and not as per the windows 'detail' modified date. So tried the tblversion way, but received the following error
Provider cannot be found - it may not be installed
Error code 800a0e7a
ADODB.connection.
I have googled it, but now had any luck. I am running windows 7, with Access 2010 - any suggestions, thanks
Robyn

 

[boblarson]

And if someone is interested, I have a FREE tool on my website that enables Auto Updating in any Frontend file. Once enabled you just change the version number in both tables and the next time the user opens their database it

• Let's them know it is out of date
• Closes the database
• Deletes the old version
• Copies the new one down to their machine
• Reopens it automatically

See the link in my signature, if interested, and click on the My Free Access Tools link on my page.

 

[robynb]

Thanks heaps and heaps, I downloaded your updater file, and works fantastically. Saves a lot of wasted time.

 

[mdlueck]

I would like to modify the batch file so that it checks some attribute of the FE file to decide whether it needs to be updated.

At a high level, my architecture does a shell game to handle Software Distribution.

On the file server I name files with a "_STAGE" before the file extension. I use MS RoboCopy.exe to replicate those files down to the workstation. I check the return code of RoboCopy.exe and only if a good return code, THEN do a local XCopy.exe from the "_STAGE" name to the production name.

If the RoboCopy should fail, the users are instructed to open Access and select the FE DB from the recent previous file list. Since the run-time file was not touched by RoboCopy, the run-time file is as good still as the last run of the application... minus ware and tare with the file size build up of MS Access files.

I am using the RoboCopy that is distributed with RoboCopy GUI: http://technet.microsoft.com/en-us/magazine/cc160891.aspx

List of versions:
http://en.wikipedia.org/wiki/Robocopy

Prior to distributing a new version, I run through these steps...

VBA to Cleanup A2007 DB Extra Objects
http://www.access-programmers.co.uk/forums/showthread.php?t=226466

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948

 

[AccessUser4]

I am a newbie. How can I get to the updater file? When I click the signature I can't find an "Access Tools Link". Help anyone?

I have a database that has about 15 front end users and I would like to create a batchfile to copy the latest so I don't have to bother them by e-mailing upated copies.

Thank you in advance!

Lisa

 

[LaurieW]

I found this on the internet, Bob's Free Access Tools:
http://www.btabdevelopment.com/ts/freetools

 

[AccessUser4]

Thanks Laurie. I did find that and opened the file. It is a ton of code. I haven't gotten up then nerve to give it a try yet...scared. LOL! No one else at my place of work know much about access so I'm solo.

 

[CNorway]

And if someone is interested, I have a FREE tool on my website that enables Auto Updating in any Frontend file. Once enabled you just change the version number in both tables and the next time the user opens their database it

• Let's them know it is out of date
• Closes the database
• Deletes the old version
• Copies the new one down to their machine
• Reopens it automatically

See the link in my signature, if interested, and click on the My Free Access Tools link on my page.

Does this work if the front end is located on a website (Sharepoint 365 folder)? Thanks, Chris

 

[Gasman]

I sincerely doubt it, as it constructs a batch file to run the copy.
However, if you can do it manually like that, then the answer would be yes.

 

[CNorway]

I sincerely doubt it, as it constructs a batch file to run the copy.
However, if you can do it manually like that, then the answer would be yes.

Thanks. Just trying to find the optimal solution