By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,801 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Iterate through table fields in an ADP?

P: n/a
Hi! Is it possible to iterate through table fields in an ADP? What
I'd like to do is:

Sub PrintTableFields()
Dim dbs As Object
Dim tbl As AccessObject
Dim fld As ????

Set dbs = Application.CurrentData
For Each tbl In dbs.AllTables
Debug.Print tbl .Name
For Each fld in tbl.????
Debug.Print " " & fld.Name
Next fld
Next tbl
I can't seem to get to the Fields collection of a table. Any help
would be greatly appreciated!
TIA,
DStark

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Make sure you have a reference to the library:
Microsoft ADO Ext. 2.x for DDL and Security

You can then examine the Columns in the Tables of the Catalog, like this:

Function ShowAllTables()
Dim cat As New ADOX.Catalog 'Root object of ADOX.
Dim tbl As ADOX.Table 'Each Table in Tables.
Dim col As ADOX.Column 'Each Column in the Table.

'Point the catalog to the current project's connection.
Set cat.ActiveConnection = CurrentProject.Connection

'Loop through the tables.
For Each tbl In cat.Tables
Debug.Print tbl.Name, tbl.Type
'Loop through the columns of the table.
For Each col In tbl.Columns
Debug.Print , col.Name, col.Type
Next
Debug.Print "--------------------------------"
Next

'Clean up
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DStark" <st****@acm.org> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
Hi! Is it possible to iterate through table fields in an ADP? What
I'd like to do is:

Sub PrintTableFields()
Dim dbs As Object
Dim tbl As AccessObject
Dim fld As ????

Set dbs = Application.CurrentData
For Each tbl In dbs.AllTables
Debug.Print tbl .Name
For Each fld in tbl.????
Debug.Print " " & fld.Name
Next fld
Next tbl
I can't seem to get to the Fields collection of a table. Any help
would be greatly appreciated!

Nov 13 '05 #2

P: n/a
Thanks, Allen!

That worked great. I assume there is no way to get field info from
AllTables collection. ADOX must be used.

DStark

Nov 13 '05 #3

P: n/a
You don't have to use ADOX...you can do this:

Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM INFORMATION_SCHEMA.COLUMNS",
CurrentProject.Connection

Do While Not rs.EOF
Debug.Print rs("TABLE_NAME"); vbTab; rs("COLUMN_NAME")
rs.MoveNext
Loop

Set rs = Nothing

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.