What System or Hidden table(s) (if any) holds the properties for the current db? (1 Viewer)

[NauticalGent]

While trying to find a way to change the properties of my db (specifically the ShowDocumentTabs property) with PowerShell, I asked ChatGPT and here was the first response:

If you want to modify Access database properties without opening the Access application, you can consider using the Access Database Engine's ACE OLEDB provider to interact with the database. This provider allows you to execute SQL commands against the database without opening Access itself.

Here's an example PowerShell script that uses the ACE OLEDB provider to set the `ShowDocumentTabs` property to `False`:

```powershell
# Set the path to the Access database
$databasePath = "C:\Path\To\Your\Database.accdb"


# Create a connection to the Access database using ADO.NET with ACE OLEDB provider
$connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$databasePath;"
$connection = New-Object System.Data.OleDb.OleDbConnection($connectionString)


try {
    # Open the database connection
    $connection.Open()


    # Set the ShowDocumentTabs property to False
    $command = $connection.CreateCommand()
    $command.CommandText = "UPDATE MSysAccessStorage SET ShowDocumentTabs = False"
    $command.ExecuteNonQuery()


    Write-Output "ShowDocumentTabs property set to False successfully."


} catch {
    Write-Output "Error occurred: $_"
} finally {
    # Close the connection and release resources in the finally block
    if ($connection.State -eq 'Open') {
        $connection.Close()
    }
    $connection.Dispose()
}
```

In this script, I've used the `MSysAccessStorage` table to represent a system table that may contain the `ShowDocumentTabs` property. However, the actual system table and field names might differ based on the Access version and configuration.

As always, please make sure to have a backup of your database before making changes, and test any script on a copy of your database to ensure it behaves as expected.

That did not work and generated RT error '3061' - too few parameters. I relied this to CPT, here was the response:

I apologize for the confusion. The error may be occurring because the specified table or field name (`MSysAccessStorage`, in this case) might not exist in your database, or the structure might be different.

If you are using Access 2013 or later, you can use the `MSysNavPaneGroupCategories` table to store the Navigation Pane settings. Please note that manipulating system tables directly can be risky, and it's essential to have a backup of your database before making changes.

Here's an updated script that attempts to set the `ShowDocumentTabs` property to `False` using the `MSysNavPaneGroupCategories` table:

```powershell
# Set the path to the Access database
$databasePath = "C:\Path\To\Your\Database.accdb"


# Create a connection to the Access database using ADO.NET with ACE OLEDB provider
$connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$databasePath;"
$connection = New-Object System.Data.OleDb.OleDbConnection($connectionString)


try {
    # Open the database connection
    $connection.Open()


    # Set the ShowDocumentTabs property to False
    $command = $connection.CreateCommand()
    $command.CommandText = "UPDATE MSysNavPaneGroupCategories SET ShowDocumentTabs = False"
    $command.ExecuteNonQuery()


    Write-Output "ShowDocumentTabs property set to False successfully."


} catch {
    Write-Output "Error occurred: $_"
} finally {
    # Close the connection and release resources in the finally block
    if ($connection.State -eq 'Open') {
        $connection.Close()
    }
    $connection.Dispose()
}
```

Please replace `MSysNavPaneGroupCategories` with the appropriate system table name based on your Access version. If this still doesn't work, you might need to explore alternative methods or consider using VBA within Access to modify these settings programmatically.

This time the error was "Table MSysNavPaneGroupCategories not found. CPT suggested this:

I appreciate your patience. Access system tables can be version-specific, and their structures may change. If you're unable to find the correct table for your version, and since modifying system tables directly is not recommended, I'll provide an alternative solution using VBA within Access to toggle the `ShowDocumentTabs` property.

Firstly, create a new module in your Access database and add the following VBA code:

```vba
Function SetShowDocumentTabs()
    ' Set the ShowDocumentTabs property to False
    CurrentDb.Properties("ShowDocumentTabs") = False
End Function
```

After saving the module, you can run this function from PowerShell using the `Access.Application` COM object without opening the Access application window. Here's the PowerShell script:

```powershell
# Set the path to the Access database
$databasePath = "C:\Path\To\Your\Database.accdb"


# Create an instance of Access.Application
$accessApp = New-Object -ComObject Access.Application


try {
    # Open the Access database in invisible mode
    $accessApp.OpenCurrentDatabase($databasePath, $false)


    # Run the VBA function to set the ShowDocumentTabs property to False
    $accessApp.Run("SetShowDocumentTabs")


    Write-Output "ShowDocumentTabs property set to False successfully."


} catch {
    Write-Output "Error occurred: $_"
} finally {
    # Quit the Access application
    $accessApp.Quit()
}
```

This script opens the Access database using the `Access.Application` COM object in invisible mode, runs the VBA function to set the `ShowDocumentTabs` property, and then quits the Access application.

Please adjust the `$databasePath` variable with the actual path to your Access database. Test this script on a copy of your database to ensure it behaves as expected.

This of course worked - not as written; Access would not quit - so CPT and I played 20 questions getting something that DID work and I'll share that code if anyone wants it. Although it does work and only takes a second, if there is a way to do it with a query, I would like to know. If anything just to gain the knowledge.

So, I repeat the question: "What System or Hidden table(s) (if any) holds the properties for the current db, and can they be set with a query?"

 

[gemma-the-husky]

do you mean choosing between tabbed forms and resizable overlapping forms.

That's in file, options, current database. You can set it in code, but I would need to look that up.
You can't set it with a query - you would need to set it in code.

 

[gemma-the-husky]

I hope this is what you mean, and does what you want.

I set this property "UseMDIMode" to determine whether to use tabbed or overlapping windows
You will need code to determine whether the property exists. If not, this creates it.
The property has no effect in A2003.

Value 0 sets "Tabbed Windows"
Value 1 sets "Overlapping Windows"

I don't know whether this property gets stored in a system table. Maybe it gets written to the .mdb/.accdb file itself.
I always use DAO, and never use system tables directly.

Sub setMdiMode(setmode As Long)
Dim db As database
Dim prp As Property
Dim version As String
Dim legend As String

'  Set db = DBEngine(0)(0)
  Set db = CurrentDb
  
    If ExistsDBProperty("UseMDIMode") = False Then
        Set prp = db.CreateProperty("UseMDIMode", dbByte, 0)
        db.Properties.Append prp
        'MsgBox "Property Created"
    Else
        'MsgBox "Property Already Exists"
    End If


    version = Application.version
  
    Set prp = db.Properties("UseMDIMode")
    If prp.value <> setmode Then
        prp.value = setmode
      
        Select Case setmode
        Case 0: legend = "Tabbed Windows"
        Case 1: legend = "Overlapping Windows"
        End Select
      
        If version >= "12" Then
            MsgBox "You have selected the option for " & legend & ". This setting will take effect the " & _
                "next time you use this database. ", vbInformation, "Setting Changed"
        End If
    Else
        'MsgBox "Property Value already set to: " & prp.Value
    End If

End Sub

 

[CJ_London]

depends what you mean by property. Properties are generally stored in the properties collection of an object be it the db, a table, querydef etc so not accessible from a query without some form of UDF.

If you look in the msysobjects table, there are a number of fields that hold properties so these are accessible via a query. Most are stored as long binary data so pretty unreadable (fields that start with lv). The flags field shows some of the properties of a table in terms of whether it is a system or user table, visible, hidden or deep hidden. See @isladogs posts or his website for how this number can be interpreted.

I think the admin object in msysobjects is for the currentdb

 

[NauticalGent]

I have come to conclusion that they cannot be set with a query either. I have had a UDF that set the desired properties for some time and once CPT showed me how to run Sub/Function from PowerShell, I had my solution.

It was during the "20 questions" bit that CPT had mentioned that it could be done with a query that I thought about the possibility. CPT is a great tool for working things out, but just like a Google search and even advice from a forum, the suggestions are not always correct and often the question, as asked, will not yield the answer you were looking for!

 

[theDBguy]

Just FYI, some DB properties are set using DAO, but others can be set using the SetOption method.

Database Startup Properties

A recent question in the forums asked how to set certain options for the Current Database through code. Presumably, the purpose is to restri...

thedbguy.blogspot.com

 

[The_Doc_Man]

The answer to your question was presented by several isladogs posts about five years back. You can do a selective forum search for "MSys tables" and username "isladogs" - the article list is about 2 pages long.

Yes, there are MSysxxxx tables that contain a lot of what you want. However, some of what you want might be in the registry. You can do queries to MSys and SetOption for registry entries. (Also GetOption.) And some of the factors are related to CurrentDB (object) properties.

Yes, it is POSSIBLE - but HIGHLY, REALLY STRONGLY DISAVOWED - that you can change database properties and content through queries directed at the MSysxxxx tables. Which usually lead to "BANG! ZOOM! To the moon, Alice" moments. To say that they are touchy and delicate somehow doesn't convey just how explosive it can get.

 

[NauticalGent]

Thanks Doc, yes I took into FULL consideration not only the possibility but the plausibility of working with Msys objects. Quite frankly, they scare me to death and I do not like to even open them, let alone manipulate them. When I posted this question, I was looking for two answers even though only one was asked: 1. Can you? and 2. SHOULD you

I figured someone would take care of the 2nd one unsolicited...

 

[The_Doc_Man]

Last time I used them, I was doing something to help me research a database structure so as to document it in a format OTHER than that used by the "Documenter" tool. SELECT queries? No problem. JOIN queries? Possible and not terribly ugly. Understanding some of the more obscure internal linkages? There, we come to the "don't hold your breath" moments.

 

[AccessBlaster]

Thanks Doc, yes I took into FULL consideration not only the possibility but the plausibility of working with Msys objects. Quite frankly, they scare me to death and I do not like to even open them, let alone manipulate them. When I posted this question, I was looking for two answers even though only one was asked: 1. Can you? and 2. SHOULD you

I figured someone would take care of the 2nd one unsolicited...

AI has the same limitations as experts in any forum. The question must be received and understood. As you see your still clarifying your original thought. That's not a knock it's just the nature of writing.

 

[isladogs]

What System or Hidden table(s) (if any) holds the properties for the current db?​

MSysObjects - but this information is hidden away in the 4 Lv (Long Value) fields, all of which are OLE Objects datatype and the data for each is shown as 'Long binary data'
It is difficult but not impossible to read the contents of these fields
I found that the database properties information is in the LvProp field for the 'object' name MSysDb and backstage info is in the same field for the SummaryInfo item. For example:

However that is of absolutely no use for changing database properties as the MSysObjects table is read only.
You cannot cause any harm by viewing or using that table.

As mentioned above, to change database properties, use Application.SetOption in code or use Access Options

For more info on system tables, see my 2 part article:

Purpose of System Tables - 1

System tables are used by Access to make databases function correctly. With a few exceptions, there is very little information available online about most of the system tables. This is the first part of a series of articles summarising known information about Access system tables.

www.isladogs.co.uk

 

[NauticalGent]

Hi Colin, what kept you??

 

[NauticalGent]

That's not a knock...

Not taken as such...

 

[isladogs]

Hi Colin, what kept you??

I try to pretend I have a life outside of this forum!

 

[The_Doc_Man]

I try to pretend I have a life outside of this forum!

Whereas I, being retired, have no such excuse.

 

[isladogs]

Partly prompted by this thread, I've just published an article significantly expanding on the information I gave in post #11.
If you like knowing about some of the more obscure areas of Access, this article is for you

Long Value Binary fields in System Tables

This article outlines the purpose of the Lv fields containing 'Long binary data' in Access system tables and how the data can be viewed. This is the third part of a series of articles summarising known information about Access system tables.

www.isladogs.co.uk