External Use of Descriptions of Fields 2

[JerSand]

I work at an agency where we often read/import ACCESS tables and queries for analysis in statistical packages such as SAS and SPSS. The ACCESS field names become the variable names in the statistical package. When the designer of a table has taken the trouble to fill-out the field description column, we can open the table in design view to copy-and-paste the descriptions as the labels for the fields in SAS and SPSS. This is particularly useful when the field names are very cryptic.

However, this is a field-by-field operation as one writes code in SAS or SPSS. Does anyone out there know of a means of exporting/importing field descriptions from ACCESS tables along with the field names in a single operation? If so, I'd be grateful to hear about it.

Thanks.

JerSand

 

[AvGuy]

Generally the import wizards give you the opportunity to name each field during the import process. You might also try converting the importing files first to Excel and then import into Access where you can import the field name at the same time.

AvGuy

 

[MichaelRed]

There is no generic or built in process which will export the field descriptions. A routine to do so would, however, be easy to generate - but without explicit knowledge of the format which you could utilize w/o the copy/paste it doesnlt seen like it would be helpful. i.e. ther seems little difference in doing a copy paste from Ms. A. than from a standard text file. Of course If you can import tables from Ms. A., it would be possible to write a routine which generated a 'mew' table in the Ms. A. db which included the table name, field name and description. You could then import that table if it would be of any use.

The table, by the way, would not even need to be in the source db, but could be in a 'local' db which included only the code and the table. You would open the db, and start the process (procedure) with the name of the access db you need the info from, and have the new table generated in the 'local' db. From that point, I know not enough re your stats package(s) to advise on the use of the info.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RickSpr]

You can use DAO code to get to the description. It's an Access-added property of the Field object in the Fields collection of the TableDef object.

Public Function FieldDescription(TableName As String, FieldName As String) As String
    Dim db As Database, tdf As TableDef, fld As Field
    
    Set db = CurrentDb
    On Error GoTo ErrorHandler
    Set tdf = db.TableDefs(TableName)
    Set fld = tdf.Fields(FieldName)
    FieldDescription = fld.Properties("Description")
ErrorExit:
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
ErrorHandler:
    Select Case Err.Number
        Case 3265 ' Item not found in this collection
            If tdf Is Nothing Then
                ' TableName was not found
            ElseIf fld Is Nothing Then
                ' FieldName was not found
            Else
                ' Unknown error occurred
            End If
        Case 3270 ' Property not found
            ' Description property was not found
            Resume ErrorExit
        Case Else
            ' Unknown error occurred
    End Select
    Resume ErrorExit
End Function

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[JerSand]

AvGuy, MichaelRed, Rick Sprague: Thanks for your helpful responses. I'll have to find someone who knows VBA to implement your ideas, but it's good to know it's possible. I'm truly grateful.

JerSand