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?
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, _
As indicated, you might need to remove/alter the restriction on TABLE_TYPE to get more than native tables.