How to add/modify the 'Description' property value for LinkTable in VBA?

Question

I have a LinkTable which points to an existing table of SQL server in my Access database. I need to change the 'Description' property value for that table link.

I had tried many ways but failed to do that.

Can any any one help me in this regard ?

Denverdeny

Answer 1

Some notes.

Dim db As DAO.Database
Dim tdf As TableDef

Set db = CurrentDb
Set tdf = db.TableDefs("Table1")

On Error Resume Next
tdf.Properties("Description") = "Link"

If Err.Number = 3270 Then 'property not found
    Set prp = tdf.CreateProperty("Description", dbText, "Link")
    tdf.Properties.Append prp
End If

Bondon

Wow ! awesome..! It worked like a charm. Thanks a lot.. as i was already being harashed and concluded thinking there must be no solutions for it. Again thanks... – Denverdeny

But would you like to explain me why this property value needs to be created although its showing for the link table in the "Table Properties" window ? It would be great if you can.. :-) – Denverdeny

@Denverdeny If you mean there is text showing for description, then it does not have to be created and the code above will not create it. If there is no text showing, it probably does not exist for the table you are viewing, but it may exist for other tables. – Bondon

Answer 2

@Fionnuala's answer gets us 90% of the way there, but to change the description of a linked table, you need to connect to the db the linked table is stored in.

    Dim tblName As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    tblName = "Table1"
    Set db = CurrentDb
    Set tdf = db.TableDefs(tblName)
    
    If Left(tdf.Connect, 10) = ";DATABASE=" Then
        'if this is a linked table, connect to linked db
        Set db = OpenDatabase(Mid(tdf.Connect, 11))
        Set tdf = db.TableDefs(tblName)
    End If
    
    On Error Resume Next
    tdf.Properties("Description") = "Link"
    
    If Err.Number = 3270 Then 'property not found
        Dim prp As DAO.Property
        Set prp = tdf.CreateProperty("Description", dbText, "Link")
        tdf.Properties.Append prp
    End If

Without connecting to the linked table's db, the description will be stored in the currentdb, but the table's actual description will not be effected.

Satiate