Add Field Descriptions, New Table

[Bob]

Add Field Descriptions, New Table
I use Office XP Developer with W2K.

We are currently working on a questionnaire database. We paste field
descriptions from a Word document into the description, but this is not
fun - there are hundreds of questions...

Below p/o the code used to create the new table. What is the syntax to add
descriptions for each field?

Sub CreateTable()

Dim tbl As New Table
Dim cat As New ADOX.Catalog

'Open the catalog.
cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\My Documents\HIA\HIA_be.mdb"

tbl.Name = "tblHIA03"

tbl.Columns.Append "HIA03Q1", adVarWChar, 25 ' Person reporting
information
tbl.Columns.Append "HIA03Q2", adVarWChar, 25 ' Other (SPECIFY
RELATIONSHIP)
cat.Tables.Append tbl


End Sub

Thanks for looking.

Bob

 

[Douglas J. Steele]

Sorry if I seem rude, but you're going about it the wrong way if you're
trying to store meaningful information about the cryptic field names in the
description. It looks to me as though you're trying to store each question
as a field in your table. That doesn't follow the rule of database
normalization.

You might want to go to
http://www.rogersaccesslibrary.com/OtherLibraries.asp and download Duane
Hookom's "At Your Survey" to see how best to model a survey.

 

[Bob]

We downloaded the sample, discussed it, and are convinced.

Just for general knowledge, I'd like to know how to assign descriptions with
vba.


Thanks for your comment.

Bob

 

[Douglas J. Steele]

I know it can be done through ADOX, but to be honest, I've never bothered to
figure out how.

Using DAO, you set the object's Description property. The trick is that the
Description property doesn't exist by default: you have to create it before
you can use it. (Once it's been created, you use it the same as any other
property).

Try something like the following untested air-code:

Sub SetDescription( _
DAOObject As Object, _
DescriptionTX As String _
)

On Error GoTo Err_SetDescription

Dim prpNew As DAO.Property

' Attempt to set the specified property.
DAOObject.Properties("Description") = DescriptionTX

End_SetDescription:
Exit Sub

Err_SetDescription:

' Error 3270 means that the property was not found.

If Err.Number = 3270 Then
' Create property, set its value, and append it to the
' Properties collection.
Set prpNew = DAOObject.CreateProperty( _
"Description", _
dbText, _
DescriptionTX)
DAOObject.Properties.Append prpNew
Else
' If different error has occurred, display message.
MsgBox Err.Description & " (" & Err.Number & ")"
End If
Resume End_SetDescription

End Sub

Instantiate a reference to the table or field in code, and call the sub:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("MyTable")
SetDescription tdfCurr, "Description for MyTable"
Set fldCurr = tdfCurr.Fields("MyField")
SetDescription fldCurr, "Description for MyField in MyTable"

Set fldCurr = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing

(If you're using Access 2000 or 2002, be aware that you'll have to add a
reference to DAO for this code to work. With any code module open, select
Tools | References from the menu bar, scroll through the list of available
references until you find the one for Microsoft DAO 3.6 Object Library, and
select it.)