Pushing Field Comments to SQL Server

This is Part 6 in a series of articles discussing field comments on linked tables.

In Part 4, I introduced a function called PullColDescs that pulled column descriptions from a source table in SQL Server and applied them to the corresponding linked table in our Microsoft Access front-end application.

This time around, we're going to run the same process in reverse.  We'll take edits we made to field comments in the front-end linked table and push them to the corresponding back-end table in SQL Server.

The Code: PushColDescs()

The process is straightforward.  We use the ExtractFieldComments function from Part 5 of this series and the UpdateColDescDDL function from Part 3.

'UPSERT column descriptions on a source SQL Server table
'   using the column descriptions from the front-end table link
Sub PushColDescs(td As DAO.TableDef, AdoCnString As String)
    
    'Retrieve the schema and table names from the source table
    Dim SchemaName As String
    SchemaName = Split(td.SourceTableName, ".")(0)
    Dim SrcTblName As String
    SrcTblName = Split(td.SourceTableName, ".")(1)
    
    'Retrieve the field comments from the local linked tabledef
    Dim FldComments As Dictionary
    Set FldComments = ExtractFieldComments(td)
    
    'Open an ADO connection to SQL Server
    Dim AdoCn As Object
    Set AdoCn = CreateObject("ADODB.Connection")
    AdoCn.ConnectionString = AdoCnString
    AdoCn.Open
    
    'Set the ADO command options (bitmask)
    Const adExecuteNoRecords As Long = &H80
    Const adCmdText As Long = &H1
    Dim AdoOptions As Long
    AdoOptions = adExecuteNoRecords Or adCmdText

    Dim Key As Variant
    For Each Key In FldComments.Keys
        Dim FldName As String
        FldName = CStr(Key)
        
        Dim FldComment As String
        FldComment = FldComments.Item(Key)
        
        'Generate the T-SQL needed to create or update the field comment in SQL Server
        Dim UpsertDDL As String
        UpsertDDL = UpdateColDescDDL(SrcTblName, FldName, FldComment, SchemaName)
        
        'Execute the T-SQL directly against the source SQL Server instance
        AdoCn.Execute UpsertDDL, , AdoOptions
        
        Debug.Print "Updated description of "; FldName; " to: "; FldComment
        
    Next Key

End Sub

Sample Usage

Here's a quick test routine:

Sub TestPushColDescs()
    Dim CnStr As String
    CnStr = "Provider=MSOLEDBSQL;" & _
            "Integrated Security=SSPI;" & _
            "Persist Security Info=False;" & _
            "Initial Catalog=NoLongerSet;" & _
            "Data Source=."
    
    PushColDescs CurrentDb.TableDefs("Account"), CnStr
End Sub

And here are the results:

A note about testing in SSMS

When I was testing this against SQL Server, I had the source table open in design view in SSMS.  I had to refresh the connection in SSMS for the changes to appear.  I first tried refreshing just the columns and then refreshing just the table and then refreshing the database.  None of those refreshes seemed to update the column descriptions in the table design view.  It wasn't until I refreshed the connection to the whole SQL Server instance that the changes appeared.

Image by M. Roth from Pixabay