Extracting table and column names from Access

[peekay]

I need to know which tables are in an Access database. The closest I can get is :

Dim schemaTable As DataTable = Cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

I do not know however how to extract the table names from this. I also need to know which fields (columns) are in a certain table once I have its name.

Thanks

PK Odendaal

 

[jebenson]

First, add a reference to Microsoft ADO Ext. 2.x for DDL and Security to your project (I have 2.5 on my machine, but yours may be different). Also, add a reference to Microsoft ActiveX Data Objects 2.x Library.

Then, use this code:

Dim Conn As New Connection
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim rs as New Recordset

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Path\And\DB Name.mbd;Persist Security Info=False"

'Note: replace Path\And\DB Name.mbd with the path and filename of your access database

Conn.CursorLocation = adUseClient
Conn.Open

Set cat.ActiveConnection = Conn

For Each tbl In cat.Tables
    MsgBox tbl.Name
Next

Then, once you know the table name you want, you can get the fields like this:

rs.Open "SELECT * FROM tablename WHERE 1=0", Conn

For f = 0 to rs.Fields.Count - 1
    MsgBox rs.Fields(f).Name
Next f

The "WHERE 1=0" ensures that no records will be returned, but you will still get all of the fields.

Of course, you probably don't want to use MsgBox in the loops that show the table names and field names. I just put them there for illustration.

Hope this helps.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[MarkSweetland]

There's a few examples of using Microsoft ADO Ext. 2.x for DDL and Security here: thread709-752030


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach