Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File Revision Number (2000)

    Please Help!

    How do you set and retrieve the Revision Number Property of an Access database?

    Thanks.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: File Revision Number (2000)

    If you mean a version number that you determine yourself, you can set a custom database property using DAO methods. Example:

    Public Sub SetDBVersion(ByRef strVersNo As String)
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim prop As DAO.Property
    Dim strPropName As String
    Dim strMsg As String

    Set db = CurrentDb
    strPropName = "Version Number"
    db.Properties(strPropName) = strVersNo
    ' Test msg:
    MsgBox "Version " & db.Properties(strPropName), vbInformation, "DB VERSION NUMBER PROPERTY"

    Exit_Sub:
    Set db = Nothing
    Set prop = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 3270 'Property not found
    db.Properties.Append db.CreateProperty(strPropName, dbText, strVersNo, False)
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "SET DB VERSION ERROR MESSAGE"
    Resume Exit_Sub
    End Select
    End Sub

    Example of use: SetDBVersion "1.02B"

    To retrieve version number use function like this example:

    Public Function GetDBVersion() As String
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim strPropName As String
    Dim strMsg As String

    Set db = CurrentDb
    strPropName = "Version Number"
    GetDBVersion = db.Properties(strPropName)

    Exit_Sub:
    Set db = Nothing
    Exit Function
    Err_Handler:
    Select Case Err.Number
    Case 3270 'Property not found
    strMsg = "Database " & strPropName & " property has not been set."
    MsgBox strMsg, vbExclamation, "GET DB VERSION ERROR"
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox "Unexpected error: " & strMsg, vbExclamation, "GET DB VERSION ERROR"
    Resume Exit_Sub
    End Select
    End Function

    This requires setting a reference to DAO 3.6 object library in A2K. Note that this type of property is not reflected in "Database Properties" (File menu). These properties are Office Document properties. To manipulate these properties in VBA see this MSKB article:

    Dsofile.exe Lets You Edit Office Document Properties from Visual Basic and ASP

    I don't know if this works with Access files; the article references Word, Excel, & PowerPoint. (When you right-click an Access file in Windows Explorer & view Properties, there is no "Custom" tab as seen in Word, XL, & PPT files.) See MSKB article for further details.

    HTH

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Revision Number (2000)

    Hey, thanks Mark, that could help but not what I was thinking of.

    Windows 2000 & XP file properties (go to Windows Explorer, select an mdb & right click. The Summary tab displays a "Revision Number"). That's the property I'm trying to touch. Is this an API call?

    AlanS

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File Revision Number (2000)

    I think the Revision Number shown on the Summary tab is only there for compatibility reasons, I don't think it is used in Access. Creating your own version property (as recommended by MarkD) is the best you can do.

  5. #5
    New Lounger
    Join Date
    Mar 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Revision Number (2000)

    Hans, my gut tells me that this revision number property is accessable. Here's why.

    Try this.... With Windows 2000 or XP O/S, Open Windows Explorer, select any access database file (an mdb) and right-click to open the properties window. Now select the Summary tab and the Advanced button, if it's not already open. There you'll see an Origin catagory and the Revision Number property under that. Note the icon, a sheet with a pencil, indicating that the property is editable. Select it. Now click on the highlighted area to the right of the property and a text box opens that you can use to insert any text you want.

    A general rule to windows is that what you can do from the user interface, you can do progamatically. I'm assuming this is an API call but maybe there is an easier and more direct way. In either case, how?

    One reason I'd prefer to do it this way is that the user would be able to observe the revision number without have to open the application.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File Revision Number (2000)

    Hmm, I just set the Revision number the way you described for an Access 2000 format database under Windows XP Home. It seems to work: close the Properties window, then reopen it, and the revision number is still there. But as soon as I open and close the database, the revision number has disappeared, so it doesn't seem very useful.

  7. #7
    New Lounger
    Join Date
    Mar 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Revision Number (2000)

    Hans, this is getting verrrrry interesting....

    thought you were on to something, so I tried it myself. Created a test.mdb, set the Revision Number to Rev1, Opened the db, added tblTest to the db, and closed the db. Went back to WinExplorer, back tothe Revision Property and (honest) Rev1 was still there.

    I'm running XP Pro & A2k.

    Any thoughts on this?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File Revision Number (2000)

    I'll test on Windows XP Pro tomorrow, perhaps there is a difference between XP Home (which I'm using now) and XP Pro. I'm using Access 2002 SP-2 by the way, and it doesn't matter whether I use a database in Access 2000 format or in Access 2002 format.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: File Revision Number (2000)

    You can retrieve (but not set) the file Revision Number that you see in Windows Explorer by downloading & registering the DS: OLE Document Properties 1.4 Object Library (DSOleFile Library) (dsofile.dll), see previous reply for link to MSKB article. Sample code (from VB project, works same in Access VBA):

    Public Function GetDocRevisionNumber(ByRef strFileName As String) As String
    Dim obj As New DSOleFile.PropertyReader
    GetDocRevisionNumber = obj.GetDocumentProperties(strFileName).RevisionNum ber
    Set obj = Nothing
    End Function

    Example of use:

    ? GetDocRevisionNumber("C:Program FilesMicrosoft OfficeOfficeSamplesNorthwind.mdb")
    9.0
    ? GetDocRevisionNumber("C:Program FilesMicrosoft OfficeOfficeSamplesProducts.doc")
    5

    Note: I entered the Northwind "Revision Number" manually in Windows Explorer before testing function; otherwise returned an empty string. In practical terms, looking at various .MDB files, I've yet to see this property actually set based on a revision to the database itself (as opposed to Word documents, etc, where this property is updated when you make changes to the document) - it is always blank unless you go in there & change it yourself. Also note, this property is read-only. This function:

    Public Function SetDocRevisionNumber(ByRef strFileName As String, ByVal strRevNo As String)
    Dim obj As New DSOleFile.PropertyReader
    obj.GetDocumentProperties(strFileName).RevisionNum ber = strRevNo
    Set obj = Nothing
    End Function

    when tested as follows:

    SetDocRevisionNumber "C:Program FilesMicrosoft OfficeOfficeSamplesNorthwind.mdb","9.01"

    resulted in error msg - see attached pic for results. Also note, the properties you can see (and set) in Windows Explorer are NOT the same as you see when you open the .MDB file and view Database Properties from File menu. So I'm not sure what useful purpose this will serve. I find it more useful to set a custom database property as described in previous reply to keep track of "versions" of a custom db application. (Also use "global" constants and Registry settings for this purpose.) But in any event, you need to download the DLL from link in MSKB article to be able to avail yourself of this functionality.

    HTH
    Attached Images Attached Images

  10. #10
    New Lounger
    Join Date
    Mar 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Revision Number (2000)

    Thanks guys-

    I'll push this stuff thru and give you an update in the morning.

    Again, thanks!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File Revision Number (2000)

    More info: the databases I tested on had "Compact on Close" switched on. It turns out that compacting a database clears properties such as Source, Author and Revision Number!

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: File Revision Number (2000)

    FYI - tested this with copy of Northwind.mdb on WIN XP (Home) system & Office 2K/A2K installed. After compacting, then closing db, the custom properties I had previously entered (Title, Author, Comments, Revision Number) were still displayed when I then right-clicked file in Windows Explorer & viewed Properties "Summary" tab (Advanced). Perhaps you need to run a "System Restore" on your operating system to remedy this (joke).

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File Revision Number (2000)

    To be precise: properties entered in Access itself (through File | Database Properties) survive compacting the database. These properties are different from those displayed when you right-click the database and select Properties. The "Title" and "Author" properties I enter in Access itself are not reflected in the Explorer properties and vice-versa. This is completely different from the situation for Word documents and Excel workbooks.
    Attached Images Attached Images
    • File Type: png x.PNG (15.2 KB, 0 views)

  14. #14
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: File Revision Number (2000)

    Yes, that's correct, as noted in a previous reply there appears to be no relation between the properties displayed in Window Explorer by right-clicking on file, and those displayed when you open database and view Database Properties from the File menu. Tested this at work, where we have Office 2K/A2K installed, on WIN 2000 system (at home use XP home). Unlike XP, when opened file properties from Explorer, Summary tab, the "Value" column provided drop-down boxes for entering values, where XP (unless I missed something) only provided textboxes. Further, the drop-down list listed properties that I had entered in other .MDB files - see attached pic. The top half illustrates Northwind.mdb properties entered in Explorer before opening file. Then opened NW and compacted, then closed. The bottom half of pic shows results - the properties previously entered were gone, but still listed in the drop-down box. This behavior was not observed with WIN XP Home. The conclusion appears to be that relying on these properties for whatever purpose would not be very reliable or useful.

    Also to clarify, the properties that can be retrieved using the DSOleFile DLL are those displayed in Windows Explorer, not those displayed in Database Properties when file is open. I would not know how to retrieve the latter set of properties programatically, never having had much practical use for them....
    Attached Images Attached Images

  15. #15
    New Lounger
    Join Date
    Mar 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Revision Number (2000)

    <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    GREAT CATCH!

    If the Rev# doesn't stick, it's worthless. Custom Properties is the way I'll have to go. (I was just hoping that the rev# would be available to the user without having to fire up the app.)

    Thanks to you both for sticking through this. You've earned your MVP badges on this one.

    Again, VERY MANY THANKS!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •