chamill

Can anyone tell if it is possible to load an access table's description into a variable using VBA

Thanks

Conor M Hamill

PWNettle

You can get a lot of information about a table from a recordset or tabledef object created for it. For example, you can get the field names, types, and sizes with a recordset with something like this:
<PRE>Public Sub DescribeTable(TableName As Variant)
Dim rstDesc As Recordset
Dim strInfo As String
Dim intIndex As Integer
Dim strType As String

Set rstDesc = CurrentDb.OpenRecordset(TableName)

With rstDesc
strInfo = strInfo & "Description of " & TableName & " (" & .Fields.Count & " fields)" & vbCrLf & vbCrLf
strInfo = strInfo & "Field Name Type Length" & vbCrLf
strInfo = strInfo & "---------- ---- ------" & vbCrLf
For intIndex = 0 To .Fields.Count - 1
strInfo = strInfo & .Fields(intIndex).Name & String$(32 - Len(.Fields(intIndex).Name), " ")
Select Case .Fields(intIndex).Type
' You can add other types to this Select by looking up the contants under
' "Type Property" in the MS Access help file.
Case dbLong
strType = "Long"
Case dbDate
strType = "Date"
Case dbText
strType = "Text"
Case Else
strType = "Unknown"
End Select
strInfo = strInfo & strType & String$(12 - Len(strType), " ")
strInfo = strInfo & .Fields(intIndex).Size & String$(8 - Len(CStr(.Fields(intIndex).Size)), " ")
strInfo = strInfo & vbCrLf
Next intIndex
End With

Debug.Print strInfo

Set rstDesc = Nothing

End Sub</PRE>This probably doesn't directly address your desires but might give you some ideas. The routine above creates a formatted output string for a table name (passed to it) and Debug.Prints it...which ends up looking like this (using a table in my test database):
<PRE>Description of Employees II (5 fields)

Field Name Type Length
---------- ---- ------
EmpID Long 4
FirstName Text 16
LastName Text 32
DepartmentID Long 4
DateUpdated Date 8 </PRE>Paul