• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Version Control of Access systems

I'm trying to find a method of assigning version numbers to .mdb files, along the lines of the File Version property of .exe and .dll files;  it needs to be accessible (and updatable) programmatically using VBA and/or VBScript, and preferably capable of holding a 4-component version number such as  It needs to be manageable externally (for example via a VBScript file), so I don't think the use of a table within the .mdb, or code within the Access app, would be appropriate.

I've seen a "Revision Number" in the Properties -> Summary -> Advanced screen of any file I look at (including mdb's);  is this a possibility that might be appropriate in these circumstances?  If so, how do I access and update it?  Some specific code examples would be helpful ...

Alternatively, if the Revision Number is not suitable, can anybody suggest other techniques for Version Control on .mdb files?  
Ken Turner
Ken Turner
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In my Access apps I have a table called VAR_VARIABLES that stores single values I want to use, and one of them is version number.

In an .exe, I use ADO to set a connection, then call the query "SELECT VAR_VALUE FROM VAR_VARIABLES WHERE VAR_ID=3", and it gives me the value inside VAR_VALUE, which is my version number.
Use Source Safe and it'll take care of all the versions and revision history for you.
You could create your own database properties or define your own revision numbering via a module
Either way, u have to remember to set them

If u use DB Properties, u could use a func that sets the property (it creates it if it dont exist)

Function SetRevision(sRevNo As String)
     On Error GoTo Err_SetRev
     Dim db As dao.Database
     Dim prop As dao.Property
     Const conPropNotFound = 3270
     Set db = CurrentDb()
     db.Properties("Revision") = sRevNo

     Exit Function

    If Err = conPropNotFound Then
        Set prop = db.CreateProperty("Revision", dbText, sRevNo)
        db.Properties.Append prop
        Resume Next
        MsgBox "Failed to create property. " & Err.Description
    End If
End Function

Now u can set your revision number by doing this

SetRevision "1.2"

If u wanted to extract the current version, u can do this

msgbox  CurrentDb.Properties("Revision").Value

Being a db property, u can use other vb/vba to open this db and interrogate the properties
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Ken TurnerAuthor Commented:
Thanks for the suggestions. guys.

mike1086:  we replaced MS Visual SourceSafe some while ago with Borland StarTeam, and we're already using this as a repository for our Access databases.  But while I agree this takes care of the revision history and maintains its own version numbering, how do I look at an mdb file outside the repository and determine which version it is?

jimhorn:  creating an additional table seems to be the 'stock' solution to this problem - but as I stated in my original post I was hoping for an 'external' rather than an 'internal' technique.

rockiroads: your code looks interesting;  could it be adapted into, for example a standalone VB Script, which initially opens the mdb file, and then does the business?

Nobody has commented on the proposal in my original post to use the "Revision Number" property - is this idea a non-starter?
Ken, looking at the Revision Number property, I guess its the same as what I suggested

What do u want the VBScript for, to set or to fetch the value?

I would assume the set is done by yourself on release and interrogating is done on user's PC to see

dim acc

set acc = CreateObject("Access.Application")
acc.OpenCurrentDatabase ("C:\EE\mydb.mdb")
WScript.Echo acc.CurrentDB.Properties("Revision")

Ken TurnerAuthor Commented:
rockiroads:  The reason I want VBScript is that I am intending to use an automation tool to manage the release process, and this tool can use VBScript to augment its in-built functionality;  I therefore need to be able to both set and fetch the value.

I'll play with your code and get back to you ...
Setting a value should be easy enough, its the fact that u need to create a property

I dont know if u can check errors in vbs as u can with vba
Problem Im having is to initially create the property and capture the error

dim acc

set acc = CreateObject("Access.Application")
acc.OpenCurrentDatabase ("C:\EE\Barcodes.mdb")
x =acc.CurrentDB.Properties("Revision")

if err.number > 0 then
    acc.CurrentDB.CreateProperty "Revision",10,"1.1"
    acc.CurrentDB.Properties("Revision") = "1.2"
end if
WScript.Echo = acc.CurrentDB.Properties("Revision")

So if u can use an create db properties first, then simply use

acc.CurrentDB.Properties("Revision") = "1.2"

to set them, then u are fine
Ken TurnerAuthor Commented:
rockiroads: I tried your code, but the line

WScript.Echo acc.CurrentDB.Properties("Revision")

failed with "Property not Found"
thats why I mentioned in my last post

I have not been able to create properties using vbscript but I can set a value to an existing property

I think if all db's that u create, if u create the property in that DB (u can use SetRevison function I posted earlier) initially
then u should be able to set a value to that in vbs
Ken TurnerAuthor Commented:
For the record, since the last post I've done some research into the "Properties -> Summary -> Advanced screen" part of my original question.  Many of the answers are here:


However the downside is that when I assigned some values to some of these properties, then saved my (mdb) file into StarTeam, deleted the mdb, then checked out a fresh copy from StarTeam, all the assigned values were lost.  It transpires that this stuff is not saved within the file itself, but in the 'metadata' maintained within the NTFS File System, so when the file was deleted all was lost.

So this approach is a non-starter for what I want to do.

Back to the rockiroads proposals: I need to find out why the CreateProperty operation doesn't work.  I'll post again when I have any further progress.
Ken TurnerAuthor Commented:

I haven't found out why the CreateProperty operation doesn't work, but I have been able to update the property "AppTitle" in VB Script:

acc.CurrentDB.Properties("AppTitle") = replace(acc.CurrentDB.Properties("AppTitle"), OldVersion, NewVersion)

Assuming the last 'word' in AppTitle is always the version number (which it is in our case), this seems to be the best compromise.  It does have the advantage that by default AppTitle shows in the window title bar.

So as long as u can parse out the version number if u ever wanted to query it, then thats fine (unless of course u just dump AppTitle)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now