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()
'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
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.