Getting the data types and sizes from Access

[PeterF]

I would like to write an SQL query that would bring back a list of all the tables, fields, data types and sizes in Access. I can do this in SQL server but cannot find the field information in the hidden tables.

Any ideas would be appreciated.

Kind regards

PeterF

[MMcCarthy]

If you just need the information you can retrieve it using the documenter.

Tools - Analyse - Documenter

Highlight the tables you want and the documenter will create a report showing this information. Make sure you change the options.

If you need an actual table you will need to use a function similar to the one below:

 
Function TableStructure(tblName As String)
Dim db as Database
Dim rs as Recordset
Dim i as Integer
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset("TableToStoreValues")
 
    For i = 0 To db.tabledefs(tblName).Fields.Count - 1
        rs.AddNew
        rs!TableName = tblName
        rs!FieldName = db.tabledefs(tblName).Fields(i).Properties(4).Value
        rs!FieldSize = db.tabledefs(tblName).Fields(i).Properties(3).Value
    Next i
End Sub
 
 

I would like to write an SQL query that would bring back a list of all the tables, fields, data types and sizes in Access. I can do this in SQL server but cannot find the field information in the hidden tables.

Any ideas would be appreciated.

Kind regards

PeterF