A Quick Question (...but are they ever 'quick'?) - Can we get to the Default Form via VBA in Access?

> I recall running into some friction with use of the AutoExec macro due to IT policy changes.

You may be thinking of File > Options > Trust Center > Macro Settings.
The typical setting is "Disable all macros with notification", which is how I run Northwind.
Then why does AutoExec macro work? Because the accdb (or accde in your Production app) is in a Trusted Location, which you can setup in the same Trust Center.
I'm not sure if it affects this - but we USED to be able to manage trusted locations... but no longer... Maybe the AutoExec macro will still work regardless. (I seem to recall opening one of my old Access files recently where I had included an AutoExec macro and it tried to run it. I don't recall if it ran successfully or not. I'll be looking back into that to see if it still runs or not, and then why or why not etc.)
 
: Why is it that you need not use myDB.Properties("Name") but you must use .Properties in the other case?
I would rephrase that because you said it backwards. Properties of the object model are a subset of those in the properties collection not the other way around.
So in some cases you can only reference a property through the properties collection "myDb.Properties("propertyName"), while other times you can reference it directly through either the object model "myDB.propertyName" or through the properties collection. If it is in the object model it is also in the properties collection.

My understanding.
The reason why some properties are not in the object model especially DAO objects is for several reasons. DAO is used in other things besides Access so some of the properties only have meaning in Access. So having a properties collection allows you to "extend" the object model to fit your needs. Other places these properties will have no meaning. So only attributes that are universal and stable are in the strong typed properties of the object model. I think another reason may be compactness. As I demoed in the other link certain properties have no meaning until you get into a certain state. If a field in a table is displayed using a textbox then properties for a combobox have no meaning. Some of it may be versioning too. If after the release of a DAO object model you were to add new properties it may cause backward compatibility issues.

Here is a more extensive discussion from chat. I did not verify all assertions, but most sounds legit.

In Microsoft Access/Jet/ACE DAO (Data Access Objects), you’ll notice two “layers” of metadata on objects such as Database, TableDef, Field, Index, Relation, etc.:

  1. Strongly‑typed properties and methods on the object itself (what IntelliSense shows, e.g., TableDef.Name, Field.Type, Index.Primary).
  2. An extensible Properties collection (e.g., TableDef.Properties("Description"), Field.Properties("ValidationRule")).
Why do some attributes live in the first, and others only in the second? The short answer is: binary compatibility and extensibility of a COM/DAO library across engines, versions, and providers. Here’s the deeper breakdown.


1) DAO is a COM library with a fixed, versioned type interface​

DAO is implemented as a COM type library. Public members exposed directly on objects (Database, TableDef, Field, etc.) are compiled and frozen per version (e.g., DAO 3.6, 12.0). Adding or changing those members is risky:

  • Binary compatibility: Once an interface ships, adding/removing properties can break compiled VBA/VB code and other clients.
  • Cross‑provider scope: DAO front‑ends multiple data engines (Jet/ACE, ODBC, ISAMs). Only attributes that are universal and stable across engines tend to become first‑class properties.
So, core things every client needs—like Field.Name, Field.Type, Recordset.Edit, Index.Name, Relation.Attributes—are baked into the object model as strongly‑typed properties. That keeps the main model stable and predictable across versions.


2) The Properties collection exists for extensibility and provider‑specific metadata​

The Properties collection is DAO’s “escape hatch” for engine‑, provider‑, or version‑specific attributes that the core interface didn’t (or couldn’t) standardize:

  • Provider/engine specific: Jet/ACE invents new attributes (e.g., engine type, ANSI padding, Unicode compression) that shouldn’t bloat the core interface or force a DAO version bump.
  • Version agility: New Access/ACE releases can add attributes without breaking old code. If a caller doesn’t know about a property, it simply won’t ask for it.
  • Optional/rare features: Some attributes apply only to certain object types or configurations (e.g., validation text on a field; SubdatasheetName; replication IDs). Keeping these in Properties avoids cluttering the main interface.
  • Backward compatibility: Older code can continue to automate DAO without recompilation, while newer clients can discover added capabilities through Properties.
Think of Properties as a metadata key/value bag attached to a DAO object, designed so engines and tools can add capabilities without changing the strongly‑typed contract.


3) Practical examples you’ll likely encounter​

Common first‑class vs. Properties‑only examples​

  • First‑class on Field: Name, Type, Size, Attributes, DefaultValue (the major, portable stuff).
  • Properties on Field:
    • ValidationRule / ValidationText (rule text strings the engine/UI interpret)
    • Description (UI/Design‑time metadata)
    • AllowZeroLength (text‑specific input rule in Jet/ACE)
    • UnicodeCompression (ACE storage behavior)
  • First‑class on TableDef: Name, Attributes, Connect, SourceTableName.
  • Properties on TableDef:
    • Description
    • SubdatasheetName, SubdatasheetHeight (Access UI behavior)
    • Engine settings such as Jet OLEDB:Engine Type/ACE analogues (depending on provider)
  • Indexes/Relations: core flags (e.g., Index.Primary, Relation.Attributes) are first‑class; descriptive text or UI extras tend to show up in Properties.
You’ll also run into Access‑UI properties that DAO carries because the Access designer uses DAO to persist them (subdatasheet settings, column widths, etc.). Those are not broadly meaningful to non‑Access engines, so they remain in Properties.


4) Why not just promote everything to a real property?​

Promoting a property from Properties("X") to a first‑class Object.X:

  • Requires changing the public interface of the COM type library (new v‑table, new GUIDs/typelibs in some cases).
  • Introduces versioning headaches for code compiled against the old interface.
  • Creates portability issues if the attribute only makes sense for Jet/ACE (but DAO also fronts ODBC/ISAM).
DAO’s philosophy (dating back to the 1990s) favors a small, stable core plus an extensible bag. That’s why many “design‑time” or “engine‑specific” attributes never become first‑class members.
 
I would rephrase that because you said it backwards. Properties of the object model are a subset of those in the properties collection not the other way around.
So in some cases you can only reference a property through the properties collection "myDb.Properties("propertyName"), while other times you can reference it directly through either the object model "myDB.propertyName" or through the properties collection. If it is in the object model it is also in the properties collection.

My understanding.
The reason why some properties are not in the object model especially DAO objects is for several reasons. DAO is used in other things besides Access so some of the properties only have meaning in Access. So having a properties collection allows you to "extend" the object model to fit your needs. Other places these properties will have no meaning. So only attributes that are universal and stable are in the strong typed properties of the object model. I think another reason may be compactness. As I demoed in the other link certain properties have no meaning until you get into a certain state. If a field in a table is displayed using a textbox then properties for a combobox have no meaning. Some of it may be versioning too. If after the release of a DAO object model you were to add new properties it may cause backward compatibility issues.

Here is a more extensive discussion from chat. I did not verify all assertions, but most sounds legit.

In Microsoft Access/Jet/ACE DAO (Data Access Objects), you’ll notice two “layers” of metadata on objects such as Database, TableDef, Field, Index, Relation, etc.:

  1. Strongly‑typed properties and methods on the object itself (what IntelliSense shows, e.g., TableDef.Name, Field.Type, Index.Primary).
  2. An extensible Properties collection (e.g., TableDef.Properties("Description"), Field.Properties("ValidationRule")).
Why do some attributes live in the first, and others only in the second? The short answer is: binary compatibility and extensibility of a COM/DAO library across engines, versions, and providers. Here’s the deeper breakdown.


1) DAO is a COM library with a fixed, versioned type interface​

DAO is implemented as a COM type library. Public members exposed directly on objects (Database, TableDef, Field, etc.) are compiled and frozen per version (e.g., DAO 3.6, 12.0). Adding or changing those members is risky:

  • Binary compatibility: Once an interface ships, adding/removing properties can break compiled VBA/VB code and other clients.
  • Cross‑provider scope: DAO front‑ends multiple data engines (Jet/ACE, ODBC, ISAMs). Only attributes that are universal and stable across engines tend to become first‑class properties.
So, core things every client needs—like Field.Name, Field.Type, Recordset.Edit, Index.Name, Relation.Attributes—are baked into the object model as strongly‑typed properties. That keeps the main model stable and predictable across versions.


2) The Properties collection exists for extensibility and provider‑specific metadata​

The Properties collection is DAO’s “escape hatch” for engine‑, provider‑, or version‑specific attributes that the core interface didn’t (or couldn’t) standardize:

  • Provider/engine specific: Jet/ACE invents new attributes (e.g., engine type, ANSI padding, Unicode compression) that shouldn’t bloat the core interface or force a DAO version bump.
  • Version agility: New Access/ACE releases can add attributes without breaking old code. If a caller doesn’t know about a property, it simply won’t ask for it.
  • Optional/rare features: Some attributes apply only to certain object types or configurations (e.g., validation text on a field; SubdatasheetName; replication IDs). Keeping these in Properties avoids cluttering the main interface.
  • Backward compatibility: Older code can continue to automate DAO without recompilation, while newer clients can discover added capabilities through Properties.
Think of Properties as a metadata key/value bag attached to a DAO object, designed so engines and tools can add capabilities without changing the strongly‑typed contract.


3) Practical examples you’ll likely encounter​

Common first‑class vs. Properties‑only examples​

  • First‑class on Field: Name, Type, Size, Attributes, DefaultValue (the major, portable stuff).
  • Properties on Field:
    • ValidationRule / ValidationText (rule text strings the engine/UI interpret)
    • Description (UI/Design‑time metadata)
    • AllowZeroLength (text‑specific input rule in Jet/ACE)
    • UnicodeCompression (ACE storage behavior)
  • First‑class on TableDef: Name, Attributes, Connect, SourceTableName.
  • Properties on TableDef:
    • Description
    • SubdatasheetName, SubdatasheetHeight (Access UI behavior)
    • Engine settings such as Jet OLEDB:Engine Type/ACE analogues (depending on provider)
  • Indexes/Relations: core flags (e.g., Index.Primary, Relation.Attributes) are first‑class; descriptive text or UI extras tend to show up in Properties.
You’ll also run into Access‑UI properties that DAO carries because the Access designer uses DAO to persist them (subdatasheet settings, column widths, etc.). Those are not broadly meaningful to non‑Access engines, so they remain in Properties.


4) Why not just promote everything to a real property?​

Promoting a property from Properties("X") to a first‑class Object.X:

  • Requires changing the public interface of the COM type library (new v‑table, new GUIDs/typelibs in some cases).
  • Introduces versioning headaches for code compiled against the old interface.
  • Creates portability issues if the attribute only makes sense for Jet/ACE (but DAO also fronts ODBC/ISAM).
DAO’s philosophy (dating back to the 1990s) favors a small, stable core plus an extensible bag. That’s why many “design‑time” or “engine‑specific” attributes never become first‑class members.
Wow - Awesome.

I think NOW I can see so much more of the reasoning for what seemed surprising to me (in my little Access world -- ha...)
Very cool - and thank you very much.
 
This lists the startup form name

Code:
Sub ShowStartupForm()
    On Error Resume Next
    Debug.Print CurrentDb.Properties("StartupForm")
End Sub

To set a form as startup using vba
Code:
Sub SetStartupForm(FormName As String)
    Dim db As DAO.Database
    Dim prp As DAO.Property

    Set db = CurrentDb

    On Error Resume Next
    db.Properties("StartupForm") = FormName

    If Err.Number = 3270 Then
        Err.Clear
        Set prp = db.CreateProperty("StartupForm", dbText, FormName)
        db.Properties.Append prp
    End If
End Sub

Example Usage: SetStartupForm "frmStart"
@isladogs - I have a tiny follow-up question, if that is OK.

On the db.CreateProperty line, there is the "dbText" argument.

Intellisense is telling me that it is the "Type". I'm thinking it must mean the type of the property name, not the type of the property itself (as that appears to be an Integer with a value of 10 (per the screenshot here.) Am I understanding what "dbText" is referring to properly?
 

Attachments

  • Question_On_DB_Property_Type_Screenshot.png
    Question_On_DB_Property_Type_Screenshot.png
    59.2 KB · Views: 1
It is the Data Type of the property you are creating. This property is a string (name of the form) so it is text. But these constants are used throughout the DAO model. So these are also used for things like fields and defines their data type.
type.png
 
Looping the properties of the current db and showing the type value and the name of that type. If the value of the property exists show that too.
Code:
---- DAO Properties of CurrentDb ----
Name: Name    Type:  12  (Memo/Long Text) Value: C:\\Downloads\dashboard_raptorraptur.accdb
Name: Connect Type:  12  (Memo/Long Text) Value:
Name: Transactions          Type:  1  (Boolean)         Value: True
Name: Updatable             Type:  1  (Boolean)         Value: True
Name: CollatingOrder        Type:  3  (Integer)         Value: 1034
Name: QueryTimeout          Type:  3  (Integer)         Value: 60
Name: Version Type:  12  (Memo/Long Text) Value: 14.0
Name: RecordsAffected       Type:  4  (Long)            Value: 0
Name: ReplicaID             Type:  15  (GUID)           Value:
Name: DesignMasterID        Type:  15  (GUID)           Value:
Name: Connection            Type:  0  (Unknown/Custom (0))            Value: <Error Reading Value>
Name: ANSI Query Mode       Type:  4  (Long)            Value: 0
Name: Themed Form Controls  Type:  4  (Long)            Value: 1
Name: Use Microsoft Access 2007 compatible cache        Type:  4  (Long)            Value: 0
Name: Clear Cache on Close  Type:  4  (Long)            Value: 0
Name: Never Cache           Type:  4  (Long)            Value: 0
Name: AccessVersion         Type:  10  (Text)           Value: 09.50
Name: NavPane Category      Type:  4  (Long)            Value: 0
Name: Show Navigation Pane Search Bar     Type:  4  (Long)            Value: 1
Name: UseMDIMode            Type:  2  (Byte)            Value: 0
Name: ShowDocumentTabs      Type:  1  (Boolean)         Value: True
Name: Picture Property Storage Format     Type:  4  (Long)            Value: 0
Name: WebDesignMode         Type:  2  (Byte)            Value: 0
Name: CheckTruncatedNumFields             Type:  4  (Long)            Value: 1
Name: Theme Resource Name   Type:  10  (Text)           Value: Tema de Office
Name: HasOfflineLists       Type:  3  (Integer)         Value: 70
Name: ProjVer Type:  3  (Integer)         Value: 142
Name: NavPane Closed        Type:  4  (Long)            Value: 0
Name: NavPane Width         Type:  4  (Long)            Value: 215
Name: NavPane View By       Type:  4  (Long)            Value: 0
Name: NavPane Sort By       Type:  4  (Long)            Value: 1
Name: StartUpShowDBWindow   Type:  1  (Boolean)         Value: True
Name: StartUpShowStatusBar  Type:  1  (Boolean)         Value: True
Name: AllowShortcutMenus    Type:  1  (Boolean)         Value: True
Name: AllowFullMenus        Type:  1  (Boolean)         Value: True
Name: AllowBuiltInToolbars  Type:  1  (Boolean)         Value: True
Name: AllowToolbarChanges   Type:  1  (Boolean)         Value: True
Name: AllowSpecialKeys      Type:  1  (Boolean)         Value: True
Name: UseAppIconForFrmRpt   Type:  1  (Boolean)         Value: False
Name: AllowDatasheetSchema  Type:  1  (Boolean)         Value: True
Name: DesignWithData        Type:  1  (Boolean)         Value: True
Name: StartUpForm           Type:  10  (Text)           Value: dashboard
Name: Show Values Limit     Type:  4  (Long)            Value: 1000
Name: Show Values in Indexed              Type:  4  (Long)            Value: 1
Name: Show Values in Non-Indexed          Type:  4  (Long)            Value: 1

For example
Code:
Name: StartUpShowDBWindow   Type:  1  (Boolean)         Value: True

The startupshowdbwindow is true when you select in the file options window to show the navigation pane on start up. It is a boolean field yes/no. It is set to true. The constant dbBoolean has a value of 1 so you can use that in code. You can test that by typing debug.print dbBoolean.
 
Last edited:
@MajP has already answered the question that you asked me so I’ll focus on the autoexec macro.

First of all you can have both an autoexec macro and a startup form. Several things arise from this.
1. Contrary to what many people believe the startup form runs before the autoexec macro.
If you want the macro to run first, set it to open your 'default' form (phrase used deliberately) and don't set a startup form

2. When run from an untrusted database, certain 'safe' actions do still run from an autoexec macro. However no code can run from a startup form

For both those reasons, there are some real benefits to using a startup form rather than an autoexec macro.
You can run almost any code that an autoexec would run from your startup form after it has fully loaded.

If you use both, make sure you understand the sequence of events.

For more info, see my article
 
Looping the properties of the current db and showing the type value and the name of that type. If the value of the property exists show that too.
Code:
---- DAO Properties of CurrentDb ----
Name: Name    Type:  12  (Memo/Long Text) Value: C:\\Downloads\dashboard_raptorraptur.accdb
Name: Connect Type:  12  (Memo/Long Text) Value:
Name: Transactions          Type:  1  (Boolean)         Value: True
Name: Updatable             Type:  1  (Boolean)         Value: True
Name: CollatingOrder        Type:  3  (Integer)         Value: 1034
Name: QueryTimeout          Type:  3  (Integer)         Value: 60
Name: Version Type:  12  (Memo/Long Text) Value: 14.0
Name: RecordsAffected       Type:  4  (Long)            Value: 0
Name: ReplicaID             Type:  15  (GUID)           Value:
Name: DesignMasterID        Type:  15  (GUID)           Value:
Name: Connection            Type:  0  (Unknown/Custom (0))            Value: <Error Reading Value>
Name: ANSI Query Mode       Type:  4  (Long)            Value: 0
Name: Themed Form Controls  Type:  4  (Long)            Value: 1
Name: Use Microsoft Access 2007 compatible cache        Type:  4  (Long)            Value: 0
Name: Clear Cache on Close  Type:  4  (Long)            Value: 0
Name: Never Cache           Type:  4  (Long)            Value: 0
Name: AccessVersion         Type:  10  (Text)           Value: 09.50
Name: NavPane Category      Type:  4  (Long)            Value: 0
Name: Show Navigation Pane Search Bar     Type:  4  (Long)            Value: 1
Name: UseMDIMode            Type:  2  (Byte)            Value: 0
Name: ShowDocumentTabs      Type:  1  (Boolean)         Value: True
Name: Picture Property Storage Format     Type:  4  (Long)            Value: 0
Name: WebDesignMode         Type:  2  (Byte)            Value: 0
Name: CheckTruncatedNumFields             Type:  4  (Long)            Value: 1
Name: Theme Resource Name   Type:  10  (Text)           Value: Tema de Office
Name: HasOfflineLists       Type:  3  (Integer)         Value: 70
Name: ProjVer Type:  3  (Integer)         Value: 142
Name: NavPane Closed        Type:  4  (Long)            Value: 0
Name: NavPane Width         Type:  4  (Long)            Value: 215
Name: NavPane View By       Type:  4  (Long)            Value: 0
Name: NavPane Sort By       Type:  4  (Long)            Value: 1
Name: StartUpShowDBWindow   Type:  1  (Boolean)         Value: True
Name: StartUpShowStatusBar  Type:  1  (Boolean)         Value: True
Name: AllowShortcutMenus    Type:  1  (Boolean)         Value: True
Name: AllowFullMenus        Type:  1  (Boolean)         Value: True
Name: AllowBuiltInToolbars  Type:  1  (Boolean)         Value: True
Name: AllowToolbarChanges   Type:  1  (Boolean)         Value: True
Name: AllowSpecialKeys      Type:  1  (Boolean)         Value: True
Name: UseAppIconForFrmRpt   Type:  1  (Boolean)         Value: False
Name: AllowDatasheetSchema  Type:  1  (Boolean)         Value: True
Name: DesignWithData        Type:  1  (Boolean)         Value: True
Name: StartUpForm           Type:  10  (Text)           Value: dashboard
Name: Show Values Limit     Type:  4  (Long)            Value: 1000
Name: Show Values in Indexed              Type:  4  (Long)            Value: 1
Name: Show Values in Non-Indexed          Type:  4  (Long)            Value: 1

For example
Code:
Name: StartUpShowDBWindow   Type:  1  (Boolean)         Value: True

The startupshowdbwindow is true when you select in the file options window to show the navigation pane on start up. It is a boolean field yes/no. It is set to true. The constant dbBoolean has a value of 1 so you can use that in code. You can test that by typing debug.print dbBoolean.
Funny you should pick the "StartUpShowDBWindow" example, as I'm thinking of adding that one to my list since I have that checked once in a while.
 
@MajP has already answered the question that you asked me so I’ll focus on the autoexec macro.

First of all you can have both an autoexec macro and a startup form. Several things arise from this.
1. Contrary to what many people believe the startup form runs before the autoexec macro.
If you want the macro to run first, set it to open your 'default' form (phrase used deliberately) and don't set a startup form

2. When run from an untrusted database, certain 'safe' actions do still run from an autoexec macro. However no code can run from a startup form

For both those reasons, there are some real benefits to using a startup form rather than an autoexec macro.
You can run almost any code that an autoexec would run from your startup form after it has fully loaded.

If you use both, make sure you understand the sequence of events.

For more info, see my article
I didn't know the order --- probably because I tended to use one OR the other. Very good to know that.
 
I'm not sure if it affects this - but we USED to be able to manage trusted locations... but no longer... Maybe the AutoExec macro will still work regardless. (I seem to recall opening one of my old Access files recently where I had included an AutoExec macro and it tried to run it. I don't recall if it ran successfully or not. I'll be looking back into that to see if it still runs or not, and then why or why not etc.)
You still can manage TLs yourself, because they are in HKCU. On my machine with A365-32:
Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Access\Security\Trusted Locations

There may be additional ones under HKLM, which may be locked down for average users.
 
I'm not sure if it affects this - but we USED to be able to manage trusted locations... but no longer... Maybe the AutoExec macro will still work regardless. (I seem to recall opening one of my old Access files recently where I had included an AutoExec macro and it tried to run it. I don't recall if it ran successfully or not. I'll be looking back into that to see if it still runs or not, and then why or why not etc.)
As stated in post #28, certain 'safe' autoexec macro actions will run even when database are not trusted.
In fact, safe actions still run even where all macros are disabled in Access options
 

Users who are viewing this thread

Back
Top Bottom