Solved

Can I Add A Description to Fields in an Access Table Linked To SQL Server Via ODBC

Posted on 2015-01-16
13
37 Views
Last Modified: 2016-06-18
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.
0
Comment
Question by:ARH64
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 168 total points
Comment Utility
From what I remember up to Access 2010 no, a linked table cannot have its schema changed, to include a description.
Even if you can add that in Access, once the table is relinked that description would disappear.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 332 total points
Comment Utility
I just tried this and it worked...
My guess is that while you cannot change the "design" of the linked table, ...the description has nothing to do with the design properties of the table. (add/remove fields, change properties or datatypes)

JeffCoachman
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 332 total points
Comment Utility
ARH64,
If this works for you, then you can use a function like the ones found here to retrieve the description(s):
http://support.microsoft.com/kb/210314
0
 

Author Comment

by:ARH64
Comment Utility
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.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:ARH64
Comment Utility
Jeffrey, I have a tool to retrieve.  I just want to know if I can safely add descriptions.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 

Author Comment

by:ARH64
Comment Utility
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??
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
AFAICT, in the table design...
Beyond that, you will have to contact MS...
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
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

Open in new window


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.
0
 

Author Comment

by:ARH64
Comment Utility
Thanks to everyone.  Haven't yet decided how I am going to proceed.  So far, so good with the description fields.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now