Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • 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 1.0.0.234.  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?  
0
Ken Turner
Asked:
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.
0
 
mike1086Commented:
Use Source Safe and it'll take care of all the versions and revision history for you.
0
 
rockiroadsCommented:
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

Cool_SetRev:
     Exit Function

Err_SetRev:
    If Err = conPropNotFound Then
        Set prop = db.CreateProperty("Revision", dbText, sRevNo)
        db.Properties.Append prop
        Resume Next
    Else
        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
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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?
0
 
rockiroadsCommented:
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
e.g.

dim acc

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

0
 
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 ...
0
 
rockiroadsCommented:
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
0
 
rockiroadsCommented:
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")

'HERE WE NEED TO CAPTURE ERROR
if err.number > 0 then
    acc.CurrentDB.CreateProperty "Revision",10,"1.1"
else
    acc.CurrentDB.Properties("Revision") = "1.2"
end if
WScript.Echo = acc.CurrentDB.Properties("Revision")
acc.CloseCurrentDatabase



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
0
 
Ken TurnerAuthor Commented:
rockiroads: I tried your code, but the line

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

failed with "Property not Found"
0
 
rockiroadsCommented:
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
0
 
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:

http://www.microsoft.com/technet/scriptcenter/resources/tales/sg0305.mspx

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.
0
 
Ken TurnerAuthor Commented:
rockiroads:

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.

0
 
rockiroadsCommented:
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)

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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