Database Properties - Did you know (2 Viewers)

[Pat Hartman]

Don't you ever wonder about some of these typos with subliminal messages?

 

[NauticalGent]

Don't you ever wonder about some of these typos with subliminal messages?

I try not to chase the mice in my head!

 

[jwcolby54]

You guys are as harsh as you are swift...

AWF's "Ignore List Poster Boy"?

 

[jwcolby54]

Boy did this thread go sideways in a hurry.

 

[Edgar_]

what is this exactly?

An example of how to set a property for a database and how it appears in the debugger. Posted because because you wrote this:

One of the things I remember is that many things have property collections to which we can add our own properties. The database itself has this but IIRC also forms, and maybe other things such as tables and queries. Which makes it possible to set up properties which are "hidden" from the user but visible to us programmers. As an example I can embed a version number property, and programmatically modify the name of the database displayed at the top of the screen to append the version number to the database name. Stuff like that.

If anyone has studied this stuff, please do pipe up in this thread. A discussion might be useful to us. I found it fascinating back then and I find it fascinating now.

Multiple things allow adding properties. It can be checked with the object browser by searching "properties" as search term. I can see the following, as examples:
AccessObjectThing.Properties.Add(PropertyName As String, Value)
QueryDefThing.Properties.Append(Object As Object)

EDIT:
But I prefer just opening the debugger and see what's loaded to see if I can tweak it.

 

[jwcolby54]

You mean something like this?

Sub asdasd()
    Dim prop As Object
    Set prop = CurrentDb.CreateProperty("CustomProp", 1, False)

    CurrentDb.Properties.Append prop
End Sub

View attachment 120038

No, I meant something more like this:

'
'Comments  :   SETS A CUSTOM (USER DEFINED) PROPERTY FOR A GIVEN DATABASE
'              CREATES THE PROPERTY IF IT DOESN'T EXIST
'Parameters:   strDBName IS A STRING CONTAINING THE FULL PATH TO THE DATABASE
'              strPropName - THE NAME OF THE PROPERTY TO CREATE
'              intPropType - THE TYPE OF PROPERTY TO CREATE
'              strPropValue - THE VALUE TO PUT IN THE PROPERTY
'Sets      :   NOTHING
'Returns   :   A STRING CONTAINING ALL THE PROPERTIES, FORMATTED FOR DISPLAY
'Created by: Colby Consulting
'Created   : 7/3/98 9:14:49 PM
Function ccSetCustomProperty(strDbName As String, strPropName As String, intPropType _
   As Integer, strPropValue As Variant) As Integer

   Dim dbs As DAO.Database, cnt As DAO.Container
   Dim wsp As DAO.Workspace
  
Dim doc As DAO.Document, prp As DAO.Property

   Const conPropertyNotFound = 3270 ' Property not found error.
   Set wsp = DAO.DBEngine.Workspaces(0)
   Set dbs = wsp.OpenDatabase(strDbName)               ' Define Database object.
   Set cnt = dbs.Containers!Databases  ' Define Container object.
   Set doc = cnt.Documents!UserDefined ' Define Document object.
   On Error GoTo SetCustom_Err
   doc.Properties.Refresh

   Set prp = doc.Properties(strPropName)
   prp = strPropValue               ' Set custom property value.
   ccSetCustomProperty = True

SetCustom_Bye:
   Set dbs = Nothing
   Set wsp = Nothing
   Exit Function

SetCustom_Err:
   If Err = conPropertyNotFound Then
      Set prp = doc.CreateProperty(strPropName, intPropType, strPropValue)
      doc.Properties.Append prp     ' Append to collection.
      Resume Next
   Else                             ' Unknown error.
      ccSetCustomProperty = False

Resume SetCustom_Bye
   End If
End Function

And this:

'Comments  :   GETS A CUSTOM (USER DEFINED) PROPERTY FOR A GIVEN DATABASE
'Parameters:   strDBName IS A STRING CONTAINING THE FULL PATH TO THE DATABASE
'              strPropName - THE NAME OF THE PROPERTY TO CREATE
'Sets      :   NOTHING
'Returns   :   A STRING CONTAINING THE REQUESTED PROPERTY
'Created by: Colby Consulting
'Created   : 7/3/98 9:14:49 PM
Function ccGetCustomProperty(strDbName As String, strPropName As String) As Variant
   Dim dbs As DAO.Database, cnt As DAO.Container
   Dim wsp As DAO.Workspace
  
Dim doc As DAO.Document, prp As DAO.Property

   Const conPropertyNotFound = 3270       ' Property not found error.
   Set wsp = DAO.DBEngine.Workspaces(0)
   Set dbs = wsp.OpenDatabase(strDbName)  ' Define Database object.
   Set cnt = dbs.Containers!Databases     ' Define Container object.
   Set doc = cnt.Documents!UserDefined    ' Define Document object.
   On Error GoTo GetCustom_Err
   doc.Properties.Refresh

   'GET THE VALUE OF THE PROPERTY
   ccGetCustomProperty = doc.Properties(strPropName).value

GetCustom_Bye:
   Set dbs = Nothing
   Set wsp = Nothing
   Exit Function

GetCustom_Err:
   ccGetCustomProperty = "ccERROR"
   Resume GetCustom_Bye

End Function

 

[MajP]

One of the things I remember is that many things have property collections to which we can add our own properties. The database itself has this but IIRC also forms, and maybe other things such as tables and queries. Which makes it possible to set up properties which are "hidden" from the user but visible to us programmers. As an example I can embed a version number property, and programmatically modify the name of the database displayed at the top of the screen to append the version number to the database name. Stuff like that.

If anyone has studied this stuff, please do pipe up in this thread. A discussion might be useful to us. I found it fascinating back then and I find it fascinating now.

The thing that took me a while to get my head around is that there are all kind of Default properties that "do not exist" until you add them. This is not the same as custom properties. This is not saying they do not have a value but the property does not exist until you add a value. Then that native property gets added to the properties collection.

To demonstrate I loaded all the properties of a field from a table. Then I modified a property in the property's window and logged the properties again. Now I have more properties.

I changed the display control of the field from TextBox to Combobox and re ran the code to insert the Properties. It created additional properties shown in yellow as a result of changing from textbox to combobox. Again these properties did not exist. If I searched for Rowsource prior to changing display type it would not say the value was null, but say that the property does not exist.

What gets really confusing is that you would assume that all things visible in the property's window would exist even if they have no value. But that is not true and it seems to be inconsistent. See Caption and Default value. (This drove me crazy until I understood this.)

Caption does not exist in the Properties collection until you add a value. Then it creates that property. See above list. However Default Value does not have a value but it does appears in the Properties collection with no value. Go figure.

 

[jwcolby54]

Go to the debug window and enter:

?ccSetCustomProperty ("d:\dev-access\CruiseAmericaSales.accdb", "CC CopyRight", dbText, "© Colby Consulting 2025" )
-1
?ccGetCustomProperty ("d:\dev-access\CruiseAmericaSales.accdb","CC CopyRight")
© Colby Consulting 2025

An example of how to set a property for a database and how it appears in the debugger. Posted because because you wrote this:


Multiple things allow adding properties. It can be checked with the object browser by searching "properties" as search term. I can see the following, as examples:
AccessObjectThing.Properties.Add(PropertyName As String, Value)
QueryDefThing.Properties.Append(Object As Object)

EDIT:
But I prefer just opening the debugger and see what's loaded to see if I can tweak it.

Thank you @Edgar_ ! So far you are the only one who has spoken up. I spent a long time poking around in there looking for property collections. IIRC a property collection is kind of baked into objects. Even a property has a property collection, which led me to going down the rabbit hole of what the heck is down here. FYI, nothing.

 

[jwcolby54]

The thing that took me a while to get my head around is that there are all kind of Default properties that "do not exist" until you add them. This is not the same as custom properties. This is not saying they do not have a value but the property does not exist until you add a value. Then that native property gets added to the properties collection.

To demonstrate I loaded all the properties of a field from a table. Then I modified a property in the property's window and logged the properties again. Now I have more properties.


View attachment 120042
I changed the display control of the field from TextBox to Combobox and re ran the code to insert the Properties. It created additional properties shown in yellow as a result of changing from textbox to combobox. Again these properties did not exist. If I searched for Rowsource prior to changing display type it would not say the value was null, but say that the property does not exist.

What gets really confusing is that you would assume that all things visible in the property's window would exist even if they have no value. But that is not true and it seems to be inconsistent. See Caption and Default value. (This drove me crazy until I understood this.)
View attachment 120044

Caption does not exist in the Properties collection until you add a value. Then it creates that property. See above list. However Default Value does not have a value but it does appears in the Properties collection with no value. Go figure.

Hmmmm I wonder if one can add a property to a command button to give it a raised / sunken outline. Things like a combo or text box have a special effect property which can have the values flat, raised, sunken etc. A button doesn't have that.

Not sure if that would have any value, just idle curiosity.

 

[jwcolby54]

The thing that took me a while to get my head around is that there are all kind of Default properties that "do not exist" until you add them. This is not the same as custom properties. This is not saying they do not have a value but the property does not exist until you add a value. Then that native property gets added to the properties collection.

To demonstrate I loaded all the properties of a field from a table. Then I modified a property in the property's window and logged the properties again. Now I have more properties.


View attachment 120042
I changed the display control of the field from TextBox to Combobox and re ran the code to insert the Properties. It created additional properties shown in yellow as a result of changing from textbox to combobox. Again these properties did not exist. If I searched for Rowsource prior to changing display type it would not say the value was null, but say that the property does not exist.

What gets really confusing is that you would assume that all things visible in the property's window would exist even if they have no value. But that is not true and it seems to be inconsistent. See Caption and Default value. (This drove me crazy until I understood this.)
View attachment 120044

Caption does not exist in the Properties collection until you add a value. Then it creates that property. See above list. However Default Value does not have a value but it does appears in the Properties collection with no value. Go figure.

I was going through C2DbProperties testing it before the demo next week and ran into this exact issue. The AllowBypassKey and allowBreakIntoCode were not there and my code couldn't modify them - just set the value. It could modify all the others. It turned out that the properties did not exist. I had to error, create the properties in the error handler, then resume zero to try again updating the value. Now it would update because the property now existed.

 

[Edgar_]

Caption does not exist in the Properties collection until you add a value.

I recall having used the count of properties somewhere to determine the kind of thing I'm dealing with, so yes, that and other things are mutating as we use them.

For example, I created an unbound form with nothing in it: properties count is 250.
Now I created a bound form with stuff: properties count is 252.

 

[jwcolby54]

So the two functions I posted allows us to set or create a property in a remote database, and read it back. However that is only working with properties belonging to the remote database itself, not any of it's objects such as forms, recordsets, queries etc. I will give 10 CC Bucks to the first poster who can modify my code (create a new function) to add an existing table name or form name *to find * (a parameter to the function) and create or modify that object.

Boy was that clear as mud.

Be aware that the only thing that CC Bucks will buy is a copy of my free book, EventDrivenProgrammingInVBA.

 

[Edgar_]

I wonder if one can add a property to a command button to give it a raised / sunken outline. Things like a combo or text box have a special effect property which can have the values flat, raised, sunken etc. A button doesn't have that.

According to the object browser, you can't add properties to controls. But I don't think that prevents us from adding them in some other way, with a class or something else. I didn't test adding the property, I just checked this:

 

[MajP]

Hmmmm I wonder if one can add a property to a command button to give it a raised / sunken outline. Things like a combo or text box have a special effect property which can have the values flat, raised, sunken etc. A button doesn't have that.

That seems unlikely, but there are cases where you can add a property and add a value where one does not exist.
Example. You have a query with -1,0 values but the field is not a yes no field. You would like to change it to a checkbox, but that cannot be changed from the properties window. You can add the display control property and then change it to checkbox.

How to put Check marks in query

I have a query in which a field is -1 or 0. Is there a way to display a check mark for the -1? This query is constructed to export as an Excel file. The original field is defined as "number" and not yes/no. Thanks for your help.

www.access-programmers.co.uk

 

[Edgar_]

No, I meant something more like this:

And this:

I see that your functions are doing this, essentially:
DBEngine.Workspaces(0).Databases(0).Containers("Databases").Documents("UserDefined").Properties.Append

But your functions perform checks and let the user choose the objects to work with. I'd say it's the same methodology. Post #13, where I'm adding a property for CurrentDb does it somewhere else, but it's adding a property in the end. The property would be accessible from this collection of properties:
DBEngine.Workspaces(0).Databases(0).Properties
Assuming your CurrentDB is the first item in the databases collection of workspaces.

 

[Edgar_]

will give 10 CC Bucks to the first poster who can modify my code (create a new function) to add an existing table name or form name *to find * (a parameter to the function) and create or modify that object.

Try this, expect bugs, I couldn't test

Function ccGetCustomProperty(strDbName As String, strPropName As String, tblName As String) As Variant
   Dim dbs As DAO.Database, cnt As DAO.Container
   Dim wsp As DAO.Workspace
 
Dim doc As DAO.Document, prp As DAO.Property, tbl As TableDef

   Const conPropertyNotFound = 3270       ' Property not found error.
   Set wsp = DAO.DBEngine.Workspaces(0)
   Set dbs = wsp.OpenDatabase(strDbName)  ' Define Database object, get its table below
'   Set cnt = dbs.Containers!Databases     ' Define Container object.
'   Set doc = cnt.Documents!UserDefined    ' Define Document object.
    Set tbl = dbs.TableDefs("tblName") ' work with a table there, could be a query definition too
   On Error GoTo GetCustom_Err
'   doc.Properties.Refresh

   'GET THE VALUE OF THE PROPERTY
   ccGetCustomProperty = tbl.Properties(strPropName).Value
 

GetCustom_Bye:
   Set dbs = Nothing
   Set wsp = Nothing
   Exit Function

GetCustom_Err:
   ccGetCustomProperty = "ccERROR"
   Resume GetCustom_Bye

End Function

 

[Edgar_]

Forgot to post this too

Function ccSetCustomProperty(strDbName As String, strPropName As String, intPropType _
   As Integer, strPropValue As Variant, tblName As String) As Integer

   Dim dbs As DAO.Database, cnt As DAO.Container
   Dim wsp As DAO.Workspace
   Dim prp As DAO.Property
   Dim tbl As TableDef

   Const conPropertyNotFound = 3270 ' Property not found error.
   Set wsp = DAO.DBEngine.Workspaces(0)
   Set dbs = wsp.OpenDatabase(strDbName) ' Define Database object.
   Set tbl = dbs.TableDefs("tblName") ' table param
   On Error GoTo SetCustom_Err

   Set prp = tbl.Properties(strPropName) ' use table obj
   prp = strPropValue ' Set custom property value.
   ccSetCustomProperty = True

SetCustom_Bye:
   Set dbs = Nothing
   Set wsp = Nothing
   Exit Function

SetCustom_Err:
   If Err = conPropertyNotFound Then
      Set prp = tbl.CreateProperty(strPropName, intPropType, strPropValue) 'modified
      tbl.Properties.Append prp     ' Append to collection.
      Resume Next
   Else                             ' Unknown error.
      ccSetCustomProperty = False

Resume SetCustom_Bye
   End If
End Function

Again, expect bugs, I didn't test

I'm merely using your dbs variable because it contains the database object. And that object contains the tabledefs and querydefs objects where one can add properties to

 

[jwcolby54]

Forgot to post this too

Function ccSetCustomProperty(strDbName As String, strPropName As String, intPropType _
   As Integer, strPropValue As Variant, tblName As String) As Integer

   Dim dbs As DAO.Database, cnt As DAO.Container
   Dim wsp As DAO.Workspace
 
Dim doc As DAO.Document, prp As DAO.Property

   Const conPropertyNotFound = 3270 ' Property not found error.
   Set wsp = DAO.DBEngine.Workspaces(0)
   Set dbs = wsp.OpenDatabase(strDbName)               ' Define Database object.
'   Set cnt = dbs.Containers!Databases  ' Define Container object.
'   Set doc = cnt.Documents!UserDefined ' Define Document object.
    Set tbl = dbs.TableDefs("tblName") ' table param
   On Error GoTo SetCustom_Err
   doc.Properties.Refresh

   Set prp = tbl.Properties(strPropName) ' use table obj
   prp = strPropValue               ' Set custom property value.
   ccSetCustomProperty = True

SetCustom_Bye:
   Set dbs = Nothing
   Set wsp = Nothing
   Exit Function

SetCustom_Err:
   If Err = conPropertyNotFound Then
      Set prp = tbl.CreateProperty(strPropName, intPropType, strPropValue) 'modified
      tbl.Properties.Append prp     ' Append to collection.
      Resume Next
   Else                             ' Unknown error.
      ccSetCustomProperty = False

Resume SetCustom_Bye
   End If
End Function

Again, expect bugs, I didn't test

I'm merely using your dbs variable because it contains the database object. And that object contains the tabledefs and querydefs objects where one can add properties to

We need the table name optional so that if it isn't there we just use the db properties. But with a little work we should be able to pass in something that says what kind of option it is. Or set up a new function to do things other than db properties. A function just for tables, another just for forms. I'll think about it.

I'm going to award CC bucks anyway. You can redeem them here.

The Book Event Driven Programming in VBA

 

[Edgar_]

A function just for tables, another just for forms. I'll think about it.

Maybe write an Enum of the possibilities and unify the methods, because the syntax may differ:

AccessObjectThing.Properties.Add(PropertyName As String, Value)
QueryDefThing.Properties.Append(Object As Object)

Some expect a string, others a Property object. Oh, Thanks for the CC bucks .

 

[The_Doc_Man]

This is perhaps just an aside, but for Word documents, I recall that a long time ago it was similar in that some properties didn't exist until you created them. You COULD find online documentation about the property (that didn't always exist.) In fact, it took online research to find the correct name. If you added a property with the name spelled incorrectly, it nevertheless persisted and you could test later it programmatically. However, it was about as useful as a screen door on a submarine with respect to its originally intended purpose.

I'm going to guess that the aforementioned behavior is actually due to something being subject to COM rules. But that's just a guess.