|Task:||Programmatically get the structure of an Access table using code|
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.
' 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).