Add table description via DAO (97)

Topic

New Reply

My application creates a number of linked tables in the current database. No problem getting the linked tables defined, but I would like to add a description field to the table definition so it appears in the Access UI. The following code fails with Error Code 3219. Does anybody know the correct syntax? Thanks.

Dim prp As DAO.Property
Dim tdf As DAO.TableDef

[ snip]
Set tdf = CurrentDb.CreateTableDef(sTablename & “_AOWWeb”)
tdf.Connect = “;Database=” & txtWebBEPath
tdf.SourceTableName = sTablename
Set prp = tdf.CreateProperty(“Description”, dbText, “_AOWWeb”)
‘ tdf.Properties.Append prp <==== Fails here with error code #3219
CurrentDb.TableDefs.Append tdf

Reply | Quote

Replies

Try appending the table to the database first, then create the property and append it to the table. You can’t create a property for a table until the table is actually added to the TableDefs collection. Also, be aware that using CurrentDb that way uses a separate databae pointer each time you reference it, which is eliminated if you dim a dbs As DAO.Database and then set it to CurrentDb.

Reply | Quote

You need to change two things:

1. You must declare an object of type DAO.Database and use that:

Dim dbs As DAO.Database
Set dbs = CurrentDb

and replace CurrentDb by dbs in the code.

2. You must append the TableDef to the TableDefs collection before setting the Description property.

Dim prp As DAO.Property
Dim tdf As DAO.TableDef
Dim dbs As DAO.Database

[snip]

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef(sTablename & “_AOWWeb”)
tdf.Connect = “;Database=” & txtWebBEPath
tdf.SourceTableName = sTablename

dbs.TableDefs.Append tdf

Set prp = tdf.CreateProperty(“Description”, dbText, “_AOWWeb”)
tdf.Properties.Append prp

[snip]

Set prp = Nothing
Set tdf = Nothing
Set dbs = Nothing

Reply | Quote

Charlotte and Hans
Thanks much. That did it!

Reply | Quote