ADO interaction with TableDefs.

[DaveO]

I'm trying to query tables in an Access db based on the name of the table. If
the name matches a certain criteria I want to run a piece of SQL on it.

In DAO, you have a TableDef object which relates to the tables in a db and
you can then test for a .Name property and then if you want run the SQL.

However, in this brave new world, I'm trying to learn ADO. My question is,
ADO has no TableDef object that I can find and so how do I cycle through the
tables in the db and then run SQL based on the table name?

TIA.

 

[RoyVidar]

I'm trying to query tables in an Access db based on the name of the
table. If the name matches a certain criteria I want to run a piece
of SQL on it.

In DAO, you have a TableDef object which relates to the tables in a
db and you can then test for a .Name property and then if you want
run the SQL.

However, in this brave new world, I'm trying to learn ADO. My
question is, ADO has no TableDef object that I can find and so how
do I cycle through the tables in the db and then run SQL based on
the table name?

TIA.

There are probably several methods, here's the OpenSchema method of
the connection.

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "TABLE"))
'Set rs = cn.OpenSchema(adSchemaTables)
Do While Not rs.EOF
Debug.Print rs.Fields("TABLE_NAME").value, _
rs.Fields("TABLE_TYPE").value
rs.MoveNext
Loop

As indicated, you might need to remove/alter the restriction on
TABLE_TYPE to get more than native tables.

 

[Stefan Hoffmann]

hi Dave,

However, in this brave new world, I'm trying to learn ADO. My question is,
ADO has no TableDef object that I can find and so how do I cycle through the
tables in the db and then run SQL based on the table name?

You will find the corresponding collection in ADOX. You have to set a
reference to Microsoft ADO Extensions.


mfG
--> stefan <--

 

[DaveO]

Roy, works great thanks.

There are probably several methods, here's the OpenSchema method of
the connection.

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "TABLE"))
'Set rs = cn.OpenSchema(adSchemaTables)
Do While Not rs.EOF
Debug.Print rs.Fields("TABLE_NAME").value, _
rs.Fields("TABLE_TYPE").value
rs.MoveNext
Loop

As indicated, you might need to remove/alter the restriction on
TABLE_TYPE to get more than native tables.

 

[Dirk Goldgar]

However, in this brave new world, I'm trying to learn ADO.

Any special reason? Unless you're programming for the web, I can't see
any good reason to use ADO with a Jet database. If you're just trying
to keep up with the latest trends in development, you'd probably do
better to learn ADO.Net.