Creating a description of a table

[aziz rasul]

I have created the code below to create a description of a table.

Public Sub ChangeTableDescriptionProperty(strTableName As String, strDescription As String)

    Dim tdf As DAO.TableDef
    Dim prp As DAO.Property
    
    Set tdf = CurrentDb(strTableName)
    Set prp = tdf.CreateProperty("Description", dbText, " ")
    
    tdf.Properties.Append prp

    tdf.Properties("Description") = strDescription
    
End Sub

I get an error (3420 - Object invalid or no longer set) on line tdf.Properties.Append prp!!!

 

[LPurvis]

Hi

Create and maintain a Database object variable for use when instantiating your tabledef.
i.e.

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim prp As DAO.Property
 
    Set db = CurrentDb
    Set tdf = db(strTableName)

See this When to use CurrentDb and when to set a variable thread for an explanation why.

Cheers

 

[aziz rasul]

Thanks LPurvis. That did the trick.

 

[aziz rasul]

I thought of doing the same exercise for a query, but it doesn't like it.

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prp As DAO.Property
    
    Set db = CurrentDb
    Set qdf = db(strQueryName)
    Set prp = qdf.CreateProperty("Description", dbText, " ")
    
    qdf.Properties.Append prp
    qdf.Properties("Description") = strDescription

On line Set qdf = db(strQueryName) I get an error 'Item not found in this collection'.

 

[LPurvis]

I'd thought about mentioning that in the previous code - but figured it must be understood to have been used, but the code could have come from anywhere - therefore the problem with the change in concept now.

The earlier code:
Set tdf = db(strTableName)
is assuming the default collection of the database object. Which is the TableDefs collection. Therefore you can omit it. But you're really requesting:
Set tdf = db.TableDefs(strTableName)

Obviously the default collection can't change due to context (or any other factor at runtime). Consequently you need to specify the collection you're using. Namely the QueryDefs.
Set qdf = db.QueryDefs(strTableName)
Cheers.

 

[aziz rasul]

Thanks Leigh.