At runtime, how do I get the list of columns in a table in Access? I don't want the data in the table, just the column names and types.
EDIT: I forgot to mention that I'm using C#.
2 Answers
You want to iterate through the fields property of the table definition. I've listed two ways you can loop through the property.
C# Solution (uses System.Data and System.Data.OleDb)
https://stackoverflow.com/a/864382/2448686
VBA Solution
Public Function GetTableDetails()
Dim db As Database
Dim td As TableDef
Set db = CurrentDb
Set td = db.TableDefs("tableName")
Dim i As Long
For i = 0 To td.Fields.Count - 1
Debug.Print td.Fields(i).Type
Next
Dim f As Field
For Each f In td.Fields
Debug.Print f.Type
Next
End Function
6 Comments
Jonathan Allen
What library are you getting these objects from?
ashareef
I believe if not explicitly stated otherwise, it defaults to DAO.
Barranka
Better use
DAO explicitlyashareef
@JonathanAllen I think the library in C# is
Microsoft.Office.Interop.Access and Microsoft.Office.Interop.Access.Dao. I will have to confirm a bit later.Jonathan Allen
I'd rather avoid anything in that namespace. I can't really count on them having office installed. Straight DAO via COM interopt, on the other hand, would be reasonable.
|
Using VBA:
dim db as DAO.Database, rs as DAO.Recordset, f as DAO.Field
set db = currentDb()
set rs = db.openRecordset("yourTable", dbOpenDynaset, dbReadOnly)
with rs
for each f in rs.Fields
' Simple example: print to the inmediate window
debug.print f.Name
next f
end with
Untested: Instead of a Recordset object, use a TableDef object:
dim db as DAO.Database, t as DAO.TableDef, f as DAO.Field
set db = currentDb()
set t = db.TableDefs("yourTable")
with t
for each f in t.Fields
debug.print f.name
next f
end with
2 Comments
Jonathan Allen
Um, wouldn't that send the whole table to the client?
Barranka
@JonathanAllen Updated to address your concern. AFAIK when you use
dbOpenDynaset rows are retreived when required (if you used dbOpenSnapshot the whole recordset would have to be loaded in RAM)
Set db = CurrentDb() Set rs1 = db.OpenRecordset("Table1") Dim fld As DAO.Field For Each fld In rs1.Fields 'do stuff Next