Get Table Information 5

[Jengo]

Is their any way to get the table names and column names in a table through an SQL statement in Access 2000? I know SQL Server and Oracle both have them (select table_name from user_tables).

 

[SBendBuckeye]

This is off the top of my head but it should be close.

Dim db As DAO.Database
Dim TDef As DAO.TableDef
Dim Fld As DAO.Field <= Don't know if you need DAO here

Set DB = CurrentDB

For Each TDef in db.TableDefs
Debug.Print TDef.Name
For Each Fld in TDef.Fields
Debug.Print Fld.Name
Next Fld
Next TDef

Basically what you are doing is spinning through the TableDef collection for the database. If you single step the code above and turn view locals on, you can mess around with the DB object to find lots of interesting stuff.

Good Luck!

 

[cmmrfrds]

You can also use ADO Schema Views. This is kind of like the Schema Views in SQL Server.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset, rs2 As New ADODB.Recordset
Set cn = CurrentProject.Connection

Set rs = cn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, &quot;table&quot)

While Not rs.EOF
Debug.Print rs!table_name
Set rs2 = cn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, &quot;&quot; & rs!table_name & &quot;&quot)
While Not rs2.EOF
Debug.Print &quot; &quot; & rs2!column_name
rs2.MoveNext
Wend
rs.MoveNext
Wend
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing
Set cn = Nothing

 

[HHaskel]

cmmrfrds,

Your code works very well for me, but do you know if it is possible to modify it to get the field description also?


Hakan Haskel

 

[cmmrfrds]

While Not rs.EOF
Debug.Print rs!table_name
Set rs2 = cn2.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, &quot;&quot; & rs!table_name & &quot;&quot)
While Not rs2.EOF
Debug.Print &quot; &quot; & rs2!column_name
Debug.Print &quot; &quot; & rs2!data_type
Debug.Print &quot; &quot; & rs2!description
rs2.MoveNext
Wend
rs.MoveNext
Wend

If you need data_type it is a number that can be checked back against the constants in Access definitions. i.e. 3 = adInteger look in datatypeEnum for more definitions.

 

[Jengo]

Thank you this was extremely helpful.

 

[cmmrfrds]

You are welcome, glad to be of help.

 

[jebry]

Hi!

If anyone is still interested you can use the following SQL to get the table names:

Select Name From MySysObjects Where Type = 1 And Left(Name,5) <> &quot;MySys&quot;

This is especially useful for displaying in a list box.

To display field names in a list box set the RowSourceType to Field List and the set the RowSource to the table or query whose fields you want displayed.

Combining a list box with the table names in it and several list boxes for field lists, you can use the doubleclick event of the list box with the table names to change the RowSource of a field name list box to the table selected allowing the user a lot of flexibility in selecting tables and fields for a variety of uses (queries, reports in particular come to mind)

hth
Jeff Bridgham
bridgham@purdue.edu

 

[HHaskel]

Thanks cmmrfrds,

exactly what I wanted.


Hakan Haskel

 

[Jengo]

Question!

What would I type if I want to see if the field is required or not.

rs!required??
I tried that and it didn't work.

 

[cmmrfrds]

Would that be the same as whether nulls are allowed or not.

rs!is_nullable

Go through the schema for 1 table and print out the fields collection name this will give you all the fields or attributes that can be checked. See previous example.

Dim fld As Field, indx As Integer
Set rs2 = cn2.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, &quot;&quot; & rs!table_name & &quot;&quot)
For indx = 0 To rs2.Fields.Count - 1
Debug.Print &quot; &quot; & rs2.Fields(indx).Name
Next

 

[Jengo]

Quality!

Thank you.