DB properties in new DB? (1 Viewer)

Gasman · Nov 20, 2018

Hi all,

It is often recommended on here when it appears that there is corruption in the database to create a new one and import all the objects.?

What happens when the corrupt database has custom properties in it.?

Is there an easy method to transfer same, or is it just a case of writing some vba code to do this.

Comparing two databases one created in V14 and the new in V12 I was expecting the properties to be the same up to a point and then additional properties appended, but that is not the case.

EG V14 shows 28. NavPane Category: 0
V12 shows 14. NavPane Category: 0

The number prefix is just my counting them to see how many and in doing so noticed that this discrepancy.

TIA

jleach · Nov 20, 2018

I'm not sure of any easy way offhand (easy as in clicking a checkbox during the import process like you would for Import/Export specs, I mean).

What I'd probably do in this situation is keep a text file in the development folder that contains a list of properties and their default values (just as a documentation effort). You could also save the creation of those in a module, to re-run on-demand.

Basically any time you have "oddball" stuff like this, you should try to capture it somehow. We use SQL Server for most projects and the entire database can be recreated from scratch using scripts that are maintained through the life of the project. DB Properties sounds like a good candidate for something like that as there's no other wonderful way to document them.

Gasman · Nov 20, 2018

Hi Jack,
Thanks for the reply.

What confuses me is that the properties in each version of file do not have the same index value.?
I was expecting them to be the same for backward compatibility. Are you always expected to refer to them by name?

I'll look at a text file. I must admit, my first thought was a table

isladogs · Nov 20, 2018

Hi Gasman
Perhaps I'm being dense but what do the 14/28 refer to?

If you mean the extended property ID, that can indeed vary with version,

jleach · Nov 20, 2018

Table would work also. I'm used to text files working with other systems and storing things in git, so that's what came to mind first, but either or. A local table is self-contained so probably better here.

Regarding the index, IIRC the Properties are just a collection, and items in the collection could vary position for a number of reasons (collection items aren't guaranteed to be ordered). I'd access by name, yes.

Gasman · Nov 20, 2018

isladogs said:
Hi Gasman
Perhaps I'm being dense but what do the 14/28 refer to?

If you mean the extended property ID, that can indeed vary with version,

The 14 and 28 are the index numbers of the properties in each DB. I needed to be able to see the number in each as well as the names.
Also easier to see where they changed sequence.

The first DB was created in version 14 (Access 2010?)

I created a new DB in Access 2007 (version 12) and imported everything (well at least I thought everything

)

Then when I tried to run it, I found errors setting DB properties, which did not exist in my DB, and so I went digging.

This is all due to the fact the 2007 cannot compact a 2010+ DB (at least on my laptop?) and I was looking to see how to get past that.?

isladogs · Nov 20, 2018

You could have a look at my example database which lists extended properties
https://www.access-programmers.co.uk/forums/showthread.php?t=294548

For example, the attached screenshot is for a randomly chosen database opened in A2010.

NOTE: I don't get property 14 or 28. What name is that property in each case?

Gasman · Nov 20, 2018

The name of the property is NavPane Category ?
The value is zero is each case.

This is the code I used to dump them out.

Code:

Public Function ListAllProps()
'Created by Helen Feddema 31-Mar-2017
'Modified by Helen Feddema 31-Mar-2017
'Lists all database properties
Dim iProp As Integer
On Error Resume Next
   
   Set dbs = CurrentDb
   Debug.Print "All database properties:"
   
   For Each prp In dbs.Properties
      Debug.Print vbTab & iProp & ". " & prp.Name & ": " & prp.Value
        iProp = iProp + 1

   Next prp
End Function

isladogs · Nov 20, 2018

For info this is what I get when I run that code on the test database I had open

Code:

All database properties:
    0. Name: G:\MyFiles\ExampleDatabases\MyTESTDb\Forum TEST Database.accdb
    1. Connect: 
    2. Transactions: True
    3. Updatable: True
    4. CollatingOrder: 1033
    5. QueryTimeout: 60
    6. Version: 14.0
    7. RecordsAffected: 0
    8. ReplicaID: 
    9. DesignMasterID: 
    11. ANSI Query Mode: 0
    12. Themed Form Controls: 1
    13. Use Microsoft Access 2007 compatible cache: 0
    14. Clear Cache on Close: 0
    15. Never Cache: 0
    16. AccessVersion: 09.50
    17. NavPane Category: 0
    18. Show Navigation Pane Search Bar: 0
    19. Build: 720
    20. ProjVer: 119
    21. HasOfflineLists: 70
    22. UseMDIMode: 0
    23. ShowDocumentTabs: True
    24. Picture Property Storage Format: 0
    25. WebDesignMode: 0
    26. CheckTruncatedNumFields: 1
    27. Theme Resource Name: Office Theme
    28. StartUpShowDBWindow: True
    29. StartUpShowStatusBar: True
    30. AllowShortcutMenus: True
    31. AllowFullMenus: True
    32. AllowBuiltInToolbars: True
    33. AllowToolbarChanges: True
    34. AllowSpecialKeys: True
    35. UseAppIconForFrmRpt: True
    36. AllowDatasheetSchema: True
    37. DesignWithData: True
    38. Show Values Limit: 1000
    39. Show Values in Indexed: 1
    40. Show Values in Non-Indexed: 1
    41. Show Values in Remote: 0
    42. Auto Compact: 0
    43. NavPane Closed: 0
    44. NavPane Width: 265
    45. NavPane View By: 0
    46. NavPane Sort By: 1
    47. AppTitle: Highlighted text example
    48. AppIcon: G:\MyFiles\Icons\192green.ico
    49. Use BigInt for linking and importing data: 0

That property is listed as 17 in my example db
Suggest you use the names and ignore the number

Gasman · Nov 20, 2018

Yes, they are all over the shop.

I can understand the custom ones added, but not the standard ones.?
They only appear to be present if used.

A lot more thought needs to go into this if I need to regularly import the DB.

isladogs said:

For info this is what I get when I run that code on the test database I had open

Code:

All database properties:
    0. Name: G:\MyFiles\ExampleDatabases\MyTESTDb\Forum TEST Database.accdb
    1. Connect: 
    2. Transactions: True
    3. Updatable: True
    4. CollatingOrder: 1033
    5. QueryTimeout: 60
    6. Version: 14.0
    7. RecordsAffected: 0
    8. ReplicaID: 
    9. DesignMasterID: 
    11. ANSI Query Mode: 0
    12. Themed Form Controls: 1
    13. Use Microsoft Access 2007 compatible cache: 0
    14. Clear Cache on Close: 0
    15. Never Cache: 0
    16. AccessVersion: 09.50
    17. NavPane Category: 0
    18. Show Navigation Pane Search Bar: 0
    19. Build: 720
    20. ProjVer: 119
    21. HasOfflineLists: 70
    22. UseMDIMode: 0
    23. ShowDocumentTabs: True
    24. Picture Property Storage Format: 0
    25. WebDesignMode: 0
    26. CheckTruncatedNumFields: 1
    27. Theme Resource Name: Office Theme
    28. StartUpShowDBWindow: True
    29. StartUpShowStatusBar: True
    30. AllowShortcutMenus: True
    31. AllowFullMenus: True
    32. AllowBuiltInToolbars: True
    33. AllowToolbarChanges: True
    34. AllowSpecialKeys: True
    35. UseAppIconForFrmRpt: True
    36. AllowDatasheetSchema: True
    37. DesignWithData: True
    38. Show Values Limit: 1000
    39. Show Values in Indexed: 1
    40. Show Values in Non-Indexed: 1
    41. Show Values in Remote: 0
    42. Auto Compact: 0
    43. NavPane Closed: 0
    44. NavPane Width: 265
    45. NavPane View By: 0
    46. NavPane Sort By: 1
    47. AppTitle: Highlighted text example
    48. AppIcon: G:\MyFiles\Icons\192green.ico
    49. Use BigInt for linking and importing data: 0

That property is listed as 17 in my example db
Suggest you use the names and ignore the number

isladogs · Nov 20, 2018

Some standard properties always exist - other custom properties are added as needed. Then of course there are user defined properties which I believe the code ignores.

The number is just the iProp number in the code loop - IGNORE IT!
It may well be that the number just depends on the order the properties are loaded in the database

For example in another db I have 50 properties where the last two are
49. StartUpForm: frmSplash
50. CustomRibbonID: Help

In a third I only have 46 properties, but the properties & order are different:

Code:

   0. Name: C:\Programs\MendipDataSystems\UKPAF\UKPAF.accdb
    1. Connect: 
    2. Transactions: True
    3. Updatable: True
    4. CollatingOrder: 1033
    5. QueryTimeout: 60
    6. Version: 14.0
    7. RecordsAffected: 0
    8. ReplicaID: 
    9. DesignMasterID: 
    11. HasOfflineLists: 70
    12. WebDesignMode: 0
    13. ProjVer: 119
    14. Theme Resource Name: Office Theme
    15. NavPane Closed: 1
    16. NavPane Width: 343
    17. NavPane Category: 0
    18. NavPane View By: 0
    19. NavPane Sort By: 0
    20. Show Navigation Pane Search Bar: 1
    21. AccessVersion: 09.50
    22. AppTitle: UK Postal Address Finder
    23. UseAppIconForFrmRpt: False
    24. UseMDIMode: 1
    25. ShowDocumentTabs: True
    26. AllowDatasheetSchema: True
    27. DesignWithData: True
    28. StartUpForm: frmSplash
    29. Show Values Limit: 1000
    30. Show Values in Indexed: 1
    31. Show Values in Non-Indexed: 1
    32. Show Values in Remote: 0
    33. Auto Compact: 0
    34. ANSI Query Mode: 0
    35. Picture Property Storage Format: 1
    36. Property Sheet Label Width: 2295
    37. Track Name AutoCorrect Info: 1
    38. CustomRibbonID: Help
    39. AllowBypassKey: True
    40. AllowFullMenus: True
    41. StartUpShowStatusBar: True
    42. AllowBuiltInToolbars: True
    43. AllowShortcutMenus: True
    44. AllowToolbarChanges: True
    45. AllowSpecialKeys: True
    46. StartUpShowDBWindow: True

For info, this is the code I normally use. No numbewrs with this:

Code:

'---------------------------------------------------------------------------------------
' Procedure : ListDBPropsAll
' Purpose   : List all "Built in" and Custom Database Properties-but not "UserDefined"
'---------------------------------------------------------------------------------------

Function ListDBPropsAll() As String
    On Error Resume Next    ' An error occurs whenever this is run

    Dim db As DAO.Database
    Dim i As Long
    Dim strDBP As String
    Set db = CurrentDb

    For i = 0 To db.Properties.count - 1
        If db.Properties(i).Name = "DesignMasterID" Then
            ' Causes an error or long delay so skip it
        Else
            Debug.Print db.Properties(i).Name & " = " & db.Properties(i).Value
            strDBP = strDBP & db.Properties(i).Name & " = " & db.Properties(i).Value & ";"
        End If
    Next i
    
    ListDBPropsAll = strDBP

Exit_ListDBProps:
    Set db = Nothing
    Exit Function
End Function

Gasman · Nov 20, 2018

Yes I know, I added the iProp to get how many there were and the order, which we now find can be in any oder after about the first 6 or 7.
My initial thoughts were a set of x properties and then custom ones coming after them.
That logic would infer that the property exists even if not used, which does not appear to be the case.

Ah well, learnt something new at least.

Minty · Nov 20, 2018

They definitely don't all exist until used/created.

On one DB, I set the DB Caption property on start up and as I occasionally re-import everything to new it always breaks as soon as the code is fired for the first time as that property doesn't exist! (And I never remember to program it to check if it exists!)

Gasman · Nov 20, 2018

Minty,
I would have thought that you would have some code that would recreate them in such a situation?
One to save them off and another to import them.?

This is the first time I have come up against DB properties, and whilst I can see them to be quite handy, they do appear to a little unwieldy to maintain.

Minty · Nov 20, 2018

You are right I should, but I keep forgetting to actually do anything about it.

Normally dealing with something more "Pressing" which distracts me

Frothingslosh · Nov 20, 2018

To save you guys some time, here's something I threw together for my 'new application toolbox'. You'll need to modify, since it's literally a cut and paste, but it definitely works, and the constants' values should be pretty obvious. It's nothing particularly brilliant, but it gets the job done and automatically adds missing properties if I forgot to set them.

Code:

Private Function CreateDBProp(ByVal PropertyName As String, _
                              ByVal PropertyValue As Variant, _
                              Optional ByVal PropertyType As VbVarType = vbString)

    Dim db As DAO.Database
    Dim P As DAO.Property
    Set db = DBEngine(0)(0)
    Set P = db.CreateProperty(PropertyName, PropertyType, PropertyValue)
    db.Properties.Append P
    
End Function

Public Function ReadDBProp(ByVal PropertyName As String) As Variant

On Error GoTo RDBP_Err

Dim Response As Long

    ReadDBProp = CurrentDb.Properties(PropertyName).Value

RDBP_Exit:
    Exit Function

RDBP_Err:
    
    Select Case Err.Number
        Case 3270   'Property not found.
            'Error occurred because property hasn't been created yet.
            Select Case PropertyName
                Case APP_PROPERTY_IN_PRODUCTION
                    Response = MsgBox("The '" & PropertyName & "' property was not yet created for this application." & vbCrLf & vbCrLf & _
                                      "Please hit YES to link to Production, NO to link to Development, or CANCEL to abort without creating the property.", _
                                      vbYesNoCancel + vbQuestion + vbDefaultButton2, PROJECT_NAME)
                    Select Case Response
                        Case vbYes, vbNo
                            ReadDBProp = (Response = vbYes)
                            CreateDBProp APP_PROPERTY_IN_PRODUCTION, ReadDBProp, vbBoolean
                        Case Else       'Abort
                            ReadDBProp = Null
                    End Select
                Case APP_PROPERTY_APP_TITLE
                    ReadDBProp = GetAppTitle()
                    CreateDBProp APP_PROPERTY_APP_TITLE, ReadDBProp
            End Select
        Case Else
            Beep
            MsgBox "An error has been encountered in basLinkMaintenance.ReadDBProp!  " & _
                   "Please notify the database administrator with the following information and " & _
                   "a description of what you were doing when the error occured." & vbCrLf & vbCrLf & _
                   "Error:" & vbTab & vbTab & Err.Number & vbCrLf & _
                   "Description:" & vbTab & Err.Description, vbCritical + vbOKOnly, PROJECT_NAME
            ReadDBProp = ""
    End Select
    Resume RDBP_Exit
    
End Function

Public Function SetDBProp(ByVal PropertyName As String, _
                          ByVal PropertyValue As Variant) As Boolean

On Error GoTo SDBP_Err

Dim db As DAO.Database

    Set db = CurrentDb
    
    db.Properties(PropertyName).Value = PropertyValue
    SetDBProp = True
    
SDBP_Exit:
    If Not db Is Nothing Then Set db = Nothing
    Exit Function
    
SDBP_Err:
    Select Case Err.Number
        Case 3270   'Property not found.
            'Error occurred because property hasn't been created yet.
            CreateDBProp PropertyName, PropertyValue
            SetDBProp = True
        Case Else
            Beep
            MsgBox "An error has been encountered in basLinkMaintenance.ReadDBProp!  " & _
                   "Please notify the database administrator with the following information and " & _
                   "a description of what you were doing when the error occured." & vbCrLf & vbCrLf & _
                   "Error:" & vbTab & vbTab & Err.Number & vbCrLf & _
                   "Description:" & vbTab & Err.Description, vbCritical + vbOKOnly, PROJECT_NAME
            SetDBProp = False
    End Select
    Resume SDBP_Exit

End Function

Frothingslosh · Nov 20, 2018

Oh, and the GetAppTitle function just checks the 'InProduction' custom property and returns one of two strings, depending on whether it's flagged for development or production. (There are a few security measures that go into place in these apps in production versions that are not active in dev versions.)

Gasman · Nov 20, 2018

Thank you FrothingSlosh.

I had got as far as saving the properties to a table, but having difficulty setting them.
I am getting error 3001

How does one identify which ones cannot be modified?
I've just tried it with a custom property of my own and that has worked.

Frothingslosh · Nov 20, 2018

I've not tried. That code was built just to handle custom properties.

Gasman · Nov 21, 2018

Ok, I have something that is working. As it was there (apart from the restore problem) before FrothingSlosh posted his, I stuck with what I had. At least it means the properties can be transferred reasonably easy.

The code attached should be imported to the DB with the custom properties. Custom properties in this case are the properties you yourself have added to a DB. This site does not allow .bas files, so I just added a .txt extension to the filename.

Run SaveDBProperties() and this will create a table tblDBProperty and place all the properties that you would see from procedure ListAllProps into the table.

At this moment, I could not find out how to determine which properties can be reloaded, so a Boolean field has been added called PropCreate to identify which ones to restore. Open the table and amend this field for the required records to 1 or -1.

Remember this thread was for me to be able to compact a DB that was created in Access 2010 which I cannot do using Access 2007, and it can be necessary to compact this particular DB regularly.

So once the table has been created, populated and edited, you can import everything to the new DB.

In the new DB run RestoreDBProperties() to restore the ones updated to True. You would restore (generally your own created properties?) I have not extensively checked the ones that appear to be the standard properties.

As the first DB is not my own, it was harder for me to work this out. You of course would recognise your own properties. All this came out of having to copy the DB properties for the new DB to work as it should.

If nothing else, it might save someone a few minutes writing their code?

Thanks to Helen Feddema, whose code started all this off.

DB properties in new DB? (1 Viewer)

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

MVP / VIP

Registered User.

Enthusiastic Amateur

MVP / VIP

Attachments

Enthusiastic Amateur

MVP / VIP

Enthusiastic Amateur

MVP / VIP

Enthusiastic Amateur

AWF VIP

Enthusiastic Amateur

AWF VIP

Premier Pale Stale Ale

Premier Pale Stale Ale

Enthusiastic Amateur

Premier Pale Stale Ale

Enthusiastic Amateur

Attachments

Similar threads

Users who are viewing this thread