Microsoft Access: Programmatically get the structure of an Access table using code

Platform:Microsoft Access
Task:Programmatically get the structure of an Access table using code
Discussion:
You need to get the structure of an Access table 'on the fly'. I needed to do this to write an entire database to a sql script for upscaling to SQL Server. I wanted my code to cycle through each table, determine what the column names and data types were and build SQL insert queries for each table's data.
Example:
' copy this code into a module and type GetTableFieldTypes("YourTableName") into the immediate window
Public Function GetTableFieldTypes(TableName As String)
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & TableName & ";", dbOpenDynaset)
    Dim i As Integer
    i = 0
        Do While i < rs.Fields.Count
            GetTableFieldTypes = GetTableFieldTypes & "ColumnName: " & rs(i).Name & " ColumnType: " & rs(i).Type & " ColumnValue: " & rs(i) & vbNewLine
            i = i + 1
        Loop
        i = 0
        GetTableFieldTypes = GetTableFieldTypes & vbNewLine
        rs.MoveNext
    MsgBox "Table Structure For " & TableName & vbNewLine & GetTableFieldTypes
    rs.Close
End Function
' NOTE:  Each table column has a data type represented above by rs(i).Type.  In case you were wondering what those
' types are see below*:
'
' id(4)
' text(10)
' memo(12)
' byte(2)
' integer(3)
' longinteger(4)
' single(6)
' double(7)
' replicationid(15)
' decimal(20)
' boolean(1)
' datetime(8)
'
' *You may need to know these types to generate SQL scripts since 
' "INSERT INTO MyTable(Column1) VALUES (" & rs(3) & ");" is not the same as
' "INSERT INTO MyTable(Column1) VALUES ('" & rs(3) & "');" (If Column1 is a text field you need to know the column type
' in order to detect that an correctly quote the record's value).
Back to Index