jwcolby54
Active member
- Local time
- Today, 06:12
- Joined
- May 19, 2025
- Messages
- 326
I am doing a YouTube presentation in a couple of weeks for the Pacific Coast Access Users Group. In the last few months I have worked more and more with Chat. I am deep into a Python / SQL Server application. I do not know Python but Chat does! So I started having Chat write the code and I learned how to channel Chat in a useful direction. Cool stuff.
In order to document the SQL Server side of the house I had Chat build a handful of stored procedures to add a "Purpose" property to tables, field and views. Then Chat and I sat down and created these "purpose" properties for everything we had built so far. It worked swimmingly and was really fast to label these objects. So I decided to make that the subject of my presentation to PCAUG. Join us to discuss...
The idea is to have Chat create VBA code for a function that will take a dao SomeObject.Properties as the first parameter and a purpose string "This object does this here thing" as the second parameter. Chat wrote that, however I had planted the seed that SomeObject.Properties was just a collection with property objects in it. Which is not the case as it turned out, my bad. SomeObject.Properties is a specific thing, a DAO object not a generic collection. Long story short, it took a couple of iterations for me to straighten out that function.
I actually wrote this code while driving down the road using the phone ChatGPT voice mode where I speak and Chat responds audibly. When I got back to my laptop, the code was waiting for me. I tested it. It failed as described below, but I fed back the error messages to Chat and Chat fixed the code. Ten minutes on the phone with Chat and 10 minutes on my laptop with Chat and I had working code. Which I find unbelievably cool.
And finally I had Chat write a GetTableMetadata function to pull very basic metadata for the table just to demonstrate that the properties were in fact created.
SetTablePurpose "tlkpHairColor", "A lookup table to store hair color"
SetFieldPurpose "tlkpHairColor", "HC_ID", "Primary Key"
SetFieldPurpose "tlkpHairColor", "HC_Color", "Stores the hair color"
?GetTableMetadata ("tlkpHairColor")
Table: tlkpHairColor
Table Purpose: A lookup table to store hair color
Field: HC_ID, Type: 4
Field Purpose: Primary Key
Field: HC_Color, Type: 10
Field Purpose: Stores the hair color
So there ya go, using Chat to write code. Perhaps not the best code but it works and it demonstrates what can be done with Chat as my own personal coding assistant.
The function now takes an object and makes a purpose property, appending it to the object's .Properties collection. For my purpose, the new property will always be named "Purpose", it will always be a string, and it will always contain whatever I pass in as the purpose. Very focused for this one thing I wanted to do. Just know that it could be generalized quite easily to create properties of other types and names.
My Blog on Event Programming in VBA
Then I had Chat build a function that specifically added a purpose property to a table. I make no claim that this is the optimum or best way to do it, but it does work! I call the function, pass in a table name and a string which is the "purpose" for the table.
And finally, the same thing for a field purpose. A function which takes the name of a table, the name of a field in that table, and a string for the purpose.
SetFieldPurpose "tlkpHairColor", "HC_ID", "Primary Key"
SetFieldPurpose "tlkpHairColor", "HC_Color", "Stores the hair color"
And finally code to get a very crude metadata string...
In order to document the SQL Server side of the house I had Chat build a handful of stored procedures to add a "Purpose" property to tables, field and views. Then Chat and I sat down and created these "purpose" properties for everything we had built so far. It worked swimmingly and was really fast to label these objects. So I decided to make that the subject of my presentation to PCAUG. Join us to discuss...
The idea is to have Chat create VBA code for a function that will take a dao SomeObject.Properties as the first parameter and a purpose string "This object does this here thing" as the second parameter. Chat wrote that, however I had planted the seed that SomeObject.Properties was just a collection with property objects in it. Which is not the case as it turned out, my bad. SomeObject.Properties is a specific thing, a DAO object not a generic collection. Long story short, it took a couple of iterations for me to straighten out that function.
I actually wrote this code while driving down the road using the phone ChatGPT voice mode where I speak and Chat responds audibly. When I got back to my laptop, the code was waiting for me. I tested it. It failed as described below, but I fed back the error messages to Chat and Chat fixed the code. Ten minutes on the phone with Chat and 10 minutes on my laptop with Chat and I had working code. Which I find unbelievably cool.
And finally I had Chat write a GetTableMetadata function to pull very basic metadata for the table just to demonstrate that the properties were in fact created.
SetTablePurpose "tlkpHairColor", "A lookup table to store hair color"
SetFieldPurpose "tlkpHairColor", "HC_ID", "Primary Key"
SetFieldPurpose "tlkpHairColor", "HC_Color", "Stores the hair color"
?GetTableMetadata ("tlkpHairColor")
Table: tlkpHairColor
Table Purpose: A lookup table to store hair color
Field: HC_ID, Type: 4
Field Purpose: Primary Key
Field: HC_Color, Type: 10
Field Purpose: Stores the hair color
So there ya go, using Chat to write code. Perhaps not the best code but it works and it demonstrates what can be done with Chat as my own personal coding assistant.
The function now takes an object and makes a purpose property, appending it to the object's .Properties collection. For my purpose, the new property will always be named "Purpose", it will always be a string, and it will always contain whatever I pass in as the purpose. Very focused for this one thing I wanted to do. Just know that it could be generalized quite easily to create properties of other types and names.
My Blog on Event Programming in VBA
Code:
Public Function AddPurposeProperty_3rdTry(ByRef obj As Object, ByVal purpose As String)
Dim prop As DAO.Property
On Error Resume Next
Set prop = obj.Properties("Purpose")
If Err.Number <> 0 Then
Err.Clear
Set prop = obj.CreateProperty("Purpose", dbText, purpose)
obj.Properties.Append prop
Else
prop.Value = purpose
End If
On Error GoTo 0
End FunctionThen I had Chat build a function that specifically added a purpose property to a table. I make no claim that this is the optimum or best way to do it, but it does work! I call the function, pass in a table name and a string which is the "purpose" for the table.
Code:
SetTablePurpose "tlkpHairColor", "A lookup table to store hair color"
Public Function SetTablePurpose(ByVal tableName As String, ByVal purpose As String)
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Get the current database
Set db = CurrentDb()
' Get the table definition
Set tbl = db.TableDefs(tableName)
' Call the AddPurposeProperty function with the table's properties collection
'AddPurposeProperty tbl.Properties, purpose
AddPurposeProperty_3rdTry tbl, purpose
' Clean up
Set tbl = Nothing
Set db = Nothing
End FunctionAnd finally, the same thing for a field purpose. A function which takes the name of a table, the name of a field in that table, and a string for the purpose.
SetFieldPurpose "tlkpHairColor", "HC_ID", "Primary Key"
SetFieldPurpose "tlkpHairColor", "HC_Color", "Stores the hair color"
Code:
Public Function SetFieldPurpose(ByVal tableName As String, ByVal fieldName As String, ByVal purpose As String)
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
' Get the current database
Set db = CurrentDb()
' Get the table definition
Set tbl = db.TableDefs(tableName)
' Get the field definition
Set fld = tbl.Fields(fieldName)
' Call the AddPurposeProperty function with the field's properties collection
'AddPurposeProperty fld.Properties, purpose
AddPurposeProperty_3rdTry fld, purpose
' Clean up
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
End FunctionAnd finally code to get a very crude metadata string...
Code:
Public Function GetTableMetadata(ByVal tableName As String) As String
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim prop As DAO.Property
Dim result As String
' Get the current database
Set db = CurrentDb()
Set tbl = db.TableDefs(tableName)
' Start building the result string
result = "Table: " & tbl.Name & vbCrLf
' Get the table's Purpose property if it exists
On Error Resume Next
result = result & "Table Purpose: " & tbl.Properties("Purpose").Value & vbCrLf
On Error GoTo 0
' Loop through each field in the table
For Each fld In tbl.Fields
result = result & " Field: " & fld.Name & ", Type: " & fld.Type & vbCrLf
' Get the field's Purpose property if it exists
On Error Resume Next
result = result & " Field Purpose: " & fld.Properties("Purpose").Value & vbCrLf
On Error GoTo 0
Next fld
' Clean up
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
' Return the metadata as a string
GetTableMetadata = result
End Function
Last edited:
