VBA and ChatGPT (2 Viewers)

[jwcolby54]

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

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 Function

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.

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 Function

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"

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 Function

And finally code to get a very crude metadata string...

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

 

[Isaac]

Just be careful as sometimes it does make things up. Literally today I was trying to remember if you could omit the third argument to t sql's substring function. I was hoping that you could (much like the mid function in VBA). AI told me that you definitely could omit it, but I quickly found that to be false!

 

[jwcolby54]

Yea, with the utmost confidence!

We have to be the adult in the room with Chat.

 

[arnelgp]

don't you think you need to give acknowledgement/credit to ChatGPT on your presentation after all he does the heavy lifting?