I'm using adodb throughout my app so far but that doesn't mean I have to keep that way (although I do need to keep using ODBC DSN Connection strings, not references to filenames).

I'm trying to get hold of all the user table names in my (Access) database.

If I go into Access and run an SQL query of
Code:
"SELECT Name FROM msysobjects WHERE (type = 1) AND (flags = 0) ORDER BY name;"
I get all the table names, as expected. But if I try it in VB, as in

Code:
' adoSearch has been Dim'd As New adodb.Recordset elsewhere
Dim db As ADODB.Connection

Set db = New Connection

db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;dsn=CARS;uid=cars;pwd=cars;"

' Open with SQL
adoSearch.Open "SHAPE {SELECT Name FROM msysobjects WHERE (type = 1) AND (flags = 0) ORDER BY Name}", db, adOpenStatic, adLockPessimistic

' ...
I get runtime error -2147217911 (80040e09):
Record(s) cannot be read; no read permission on 'msysobjects'. And the adoSearch.Open line is highlighted.

What's wrong with that? Why can't I do it in VB if it works in Access?

Is there another better/easier/working way to do it? I presume this isn't an obscure thing to do!

Thanks all,

AndyC
London

------------------
* * * * * * * * * * * * * * * * * * * * * *
* *
* AndyC *
* London *
* email: [email protected] *
* *
* * * * * * * * * * * * * * * * * * * * * *