Solved

Version Control of Access systems

Posted on 2006-10-31
13
382 Views
Last Modified: 2008-01-09
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
Comment
Question by:Ken Turner
13 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17842614
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
 
LVL 11

Expert Comment

by:mike1086
ID: 17842725
Use Source Safe and it'll take care of all the versions and revision history for you.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17842789
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
 
LVL 6

Author Comment

by:Ken Turner
ID: 17848061
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17848392
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
 
LVL 6

Author Comment

by:Ken Turner
ID: 17848497
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 65

Expert Comment

by:rockiroads
ID: 17848515
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17848542
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
 
LVL 6

Author Comment

by:Ken Turner
ID: 17850173
rockiroads: I tried your code, but the line

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

failed with "Property not Found"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17850282
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
 
LVL 6

Author Comment

by:Ken Turner
ID: 17856490
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
 
LVL 6

Author Comment

by:Ken Turner
ID: 17896630
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 200 total points
ID: 17896723
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now