ADO

[kuvo]

Hi,

I want to connect to "MyDatabase.mdb", which has 3 tables: employees,
sales,and purchases. I've got to hardcode the name of the table "employees" to open the table.
Is there anyway to get how many tables are there in MyDatabase as well as
the names of all the tables ? Thanks



Set cnnData = New ADODB.Connection
Set cmdData = New ADODB.Command
Set rsData = New ADODB.Recordset


strCnn = "..."

cnnData.DefaultDatabase = "MyDatabase"
cnnData.ConnectionString = strCnn
cnnData.ConnectionTimeout = 30
cnnData.Open strCnn
rsData.CursorLocation = adUseServer
rsData.LockType = adLockReadOnly


rsData.Open "employees", cnnData, , , adCmdTable

[Marc L'Ecuyer]

You can use the OpenSchema method of the connection object

Dim rs = CreateObject("ADODB.Recordset")
Dim cn = CreateObject("ADODB.Connection")

'open the connection
...

Set rs = cn.OpenSchema(adSchemaTables);

Now, with the RecordCount property of the recordset, you know the number of tables.
If you want to know the name of all the tables :

While Not rs.EOF
MsgBox rs("TABLE_NAME")
rs.MoveNext
Wend


That's it.

Marc

[kuvo]

Hi Marc,
The answer is really cool. Thanks alot for that.