Solved

Version Control of Access systems

Posted on 2006-10-31
13
419 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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
 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

739 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