ARH64
asked on
Can I Add A Description to Fields in an Access Table Linked To SQL Server Via ODBC
I have numerous databases that are built on tables that are linked to our MRP SQL server. I am documenting these databases and would like to add a description to the fields of these tables but am concerned it will somehow affect the SQL backend. I understand I cannot modify the Fields or Data Types... I just want to add a Description. I tried one field with TEST and it saved even though it barked a bit (cannot remember what the notice was).
Bottom line: Will this cause problems with the SQL data?
Thanks.
Bottom line: Will this cause problems with the SQL data?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That must have changed with a recent version. I can think of a lot of other examples where it would be nice to get the description properties of tables and columns.
To be clear, yes, I only tried it in Acc 2013, here at work (the only version I have access to here)
To be honest, I never actually tried this until now, I just presumed that you could not change anything in "design view" of a linked table...
Like you, I am a bit curious as to where this data is being stored,
Its like, for linked tables, all the attributes are ReadOnly, except for the description...
Jeff
To be honest, I never actually tried this until now, I just presumed that you could not change anything in "design view" of a linked table...
Like you, I am a bit curious as to where this data is being stored,
Its like, for linked tables, all the attributes are ReadOnly, except for the description...
Jeff
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hmmm... sounds like I've opened a can of curious worms. It DOES work, but I also wonder where it is stored and if it will compromise the integrity of the SQL data. Maybe it WILL just disappear when the table is relinked. Odd. Jeffrey, I will take a look at your link. Thanks.
P.S. I am using Access 2007.
P.S. I am using Access 2007.
ASKER
Jeffrey, I have a tool to retrieve. I just want to know if I can safely add descriptions.
I really do not think that it will effect the SQL end, ...but you may just want to run this on a test system first.
As you know, MS might have made this possible, ...but if "issues" arise in the future, this feature may be dropped...
Remember, at the low end, you could make a table in Access "tbl_SQL_TablexxxFields" and use code to load it with the SQL table's field names, ...then add your own "descriptions" column to this Access table...
Keep us posted
As you know, MS might have made this possible, ...but if "issues" arise in the future, this feature may be dropped...
Remember, at the low end, you could make a table in Access "tbl_SQL_TablexxxFields" and use code to load it with the SQL table's field names, ...then add your own "descriptions" column to this Access table...
Keep us posted
ASKER
I just bit the bullet and filled in the entire table, saved, and opened. No errors and everything seems to be working ok. Relinking the table did not cause the Field Descriptions to disappear. I am going to proceed with caution but it is killing me... where is this information stored??
AFAICT, in the table design...
Beyond that, you will have to contact MS...
Beyond that, you will have to contact MS...
When Access links to tables, it stores various properties locally including the description. I would NOT modify the description even though you can. You don't really have control over it and even if it remains on a refresh, I'm not sure what would happen if you changed the SQL Server schema and then relinked. My guess is that Access will update its stored properties and your descriptions will be lost. I'm with the others who suggest that you make shadow tables and document there. At least you know the descriptions are safely in your app.
I have a documentation tool I made for myself that loops through the fields collection of the table defs collection and adds the fields to a dictionary table. I then add descriptions to that table.
The following code is intended to be used from the documentation database against the database specified on the form prior to running the code. You'll need to create tblTableFields based on the column names in the code below and also a delete query if you want to empty the table before importing new values.
If you want to polish this and make it more like a real data dictionary. I would import into a temp table and then run an append query to add new columns to the main table. You should also run an update query to mark deleted columns as deleted. What you can't identify is name changes. They just look like adds and deletes.
PS - now that you have actually updated the linked table's descriptions, I would run the code I posted so you will at least capture your work should relinking against a schema change blow away your hard work.
I have a documentation tool I made for myself that loops through the fields collection of the table defs collection and adds the fields to a dictionary table. I then add descriptions to that table.
The following code is intended to be used from the documentation database against the database specified on the form prior to running the code. You'll need to create tblTableFields based on the column names in the code below and also a delete query if you want to empty the table before importing new values.
If you want to polish this and make it more like a real data dictionary. I would import into a temp table and then run an append query to add new columns to the main table. You should also run an update query to mark deleted columns as deleted. What you can't identify is name changes. They just look like adds and deletes.
Sub Create_tblTableFields()
Dim db As DAO.Database
Dim tblLoop As DAO.TableDef
Dim fldLoop As DAO.Field
Dim TD1 As DAO.TableDef
Dim QD1 As DAO.QueryDef
Dim TempSet1 As DAO.Recordset
Dim strDatabase As String
Dim ThisDB As DAO.Database
Dim CountTables As Integer
On Error GoTo Create_tblTableFields_Error
On Error GoTo Err_Create_tblTableFields
'strDatabase = "C:\hartman\LinkDB2.mdb"
strDatabase = Forms!frmPrintDoc!txtDBName
CountTables = 0
Set ThisDB = CurrentDb()
If strDatabase = "" Then
Set db = CurrentDb()
Else
Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
End If
db.Containers.Refresh
Set QD1 = ThisDB.QueryDefs!QdeltblTableFields
QD1.Execute
Set TD1 = ThisDB.TableDefs!tblTableFields
Set TempSet1 = TD1.OpenRecordset
' Loop through TableDefs collection.
For Each tblLoop In db.TableDefs
' Enumerate Fields collection of each
' TableDef object.
CountTables = CountTables + 1
Forms!frmPrintDoc!txtTableCount = CountTables
Forms!frmPrintDoc!txtTableName = tblLoop.Name
Forms!frmPrintDoc.Repaint
If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 2) = "xx" Or Left(tblLoop.Name, 2) = "zz" Or Left(tblLoop.Name, 1) = "~" Then
Else
For Each fldLoop In tblLoop.Fields
TempSet1.AddNew
TempSet1!TableName = tblLoop.Name
TempSet1!FieldName = fldLoop.Name
TempSet1!OrdinalPosition = fldLoop.OrdinalPosition
TempSet1!AllowZeroLength = fldLoop.AllowZeroLength
TempSet1!DefaultValue = fldLoop.DefaultValue
TempSet1!Size = fldLoop.Size
TempSet1!Required = fldLoop.Required
TempSet1!Type = fldLoop.Type
TempSet1!ValidationRule = fldLoop.ValidationRule
TempSet1!Attributes = fldLoop.Attributes
On Error Resume Next ' the following property is only available when it is not null
TempSet1!Description = fldLoop.Properties("Description")
TempSet1!FieldType = GetType(fldLoop.Type)
TempSet1!Caption = fldLoop.Properties("Caption")
If fldLoop.Attributes And dbAutoIncrField Then 'performs bitwise operation
TempSet1!AutoNum = True
TempSet1!Required = True
Else
TempSet1!AutoNum = False
End If
TempSet1.Update
Next fldLoop
End If
Next tblLoop
Exit_Create_tblTableFields:
db.Close
Exit Sub
Err_Create_tblTableFields:
Select Case Err.Number
Case 3043
MsgBox "Please select a valid database", vbOKOnly
Case 91 ' db was not opened so it cannot be closed.
Exit Sub
Case Else
MsgBox Err.Number & "-" & Err.Description
End Select
Resume Exit_Create_tblTableFields
On Error GoTo 0
Exit Sub
Create_tblTableFields_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Create_tblTableFields of Module DocumentCollections"
End Sub
PS - now that you have actually updated the linked table's descriptions, I would run the code I posted so you will at least capture your work should relinking against a schema change blow away your hard work.
ASKER
Thanks to everyone. Haven't yet decided how I am going to proceed. So far, so good with the description fields.