Using ADO to determine all the table names in access DB

[Nath]

Hi

I am using an ADO recordset to determine all the table
names within my database that begin with "tbl_", i have
used the following code:

Dim rst, connect As Object
Dim sql As String

sql = "SELECT MSysObjects.Id, MSysObjects.Name,
MSysObjects.Type "
sql = sql & "FROM MSysObjects "
sql = sql & "WHERE (((MSysObjects.Name) Like """
sql = sql & "tbl_*"""
sql = sql & ") AND ((MSysObjects.Type)=1));"

Set rst = CreateObject("Adodb.recordset")
Set connect = Application.CurrentProject.Connection

rst.Open sql, connect, 1

after the statement that opens the recordset, with the
results of the sql statement sql, there will be a
loop.However the loop is based on:

while(not(rst.eof))

** Loop **

Wend

As soon as the recordset is openened both EOF and BOF are
true, suggesting there is no data.When i do,
currentdb.createquerydef "tmpQry",sql, there are 10
records in the query using the same sql

Can anyone help.

TIA

Nath

 

[Scott McDaniel]

You're not creating your Connection object ... add this line somewhere
before your Set Connect line:

Set connect = CreateObject("ADODB.Connection")

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

"Smash forehead on keyboard to continue ... "