How to Query MS-ACCESS table to get all columns names along their type?

Answer 1

Create a table to store the information - see comments for what was used in this code. If necessary, add a module to your database (Create --> Module) and copy and paste the following code. To run the code, position the cursor in the code module and press the F5 function key!

Sub BuildTableFieldList()

'Create a list of tables and fields in the current database (exclude system tables)

'Requires a table called tlkpDatabaseObjects with 3 short text fields (ObjectType, ObjectName, ObjectParent)

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim tdf As DAO.TableDef

Dim fld As DAO.Field

Dim ssql As String

Dim strTblName As String

Dim strFldName As String

Set db = CurrentDb

For Each tdf In db.TableDefs

If Not tdf.Name Like "MSys*" Then

Set rs = db.OpenRecordset(tdf.Name)

strTblName = tdf.Name

ssql = "Insert into tlkpDatabaseObjects(ObjectType, ObjectName, ObjectParent) VALUES ('Table'," & Chr(34) & strTblName & Chr(34) & ", 'Database');"

db.Execute ssql

'get the fields in each table

For Each fld In rs.Fields

strFldName = fld.Name

ssql = "Insert into tlkpDatabaseObjects(ObjectType, ObjectName, ObjectParent) VALUES ('Field'," & Chr(34) & strFldName & Chr(34) & ", " & Chr(34) & strTblName & Chr(34) & ");"

db.Execute ssql

Next

End If

Next tdf

MsgBox "All set!"

rs.Close

Set rs = Nothing

db.Close

Set db = Nothing

End Sub