Retrieving Table names

[Andy Collyer]

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

"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


' 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: andy.collyer@bigfoot.com *
* *
* * * * * * * * * * * * * * * * * * * * * *

[Serge]

It happens propbably because you don't have the admin rights for the user you specified: cars. Check the access level in SQL Server for this user.

------------------

Serge

Software Developer
Serge_Dymkov@vertexinc.com
Access8484@aol.com
ICQ#: 51055819 (http://www.icq.com/51055819)



[This message has been edited by Serge (edited 12-07-1999).]

[hayessj]

Try this...

Dim cnDB As ADODB.Connection
Dim rs As New ADODB.Recordset

Set cnDB = New ADODB.Connection

cnDB.Open "Connect string here "

Set rs = cnDB.OpenSchema(adSchemaTables)

Do Until rs.EOF
Select Case rs!TABLE_TYPE
Case "SYSTEM TABLE"
Debug.Print "System : " & rs!Table_Name
Case Else
Debug.Print rs!Table_Name
End Select
rs.MoveNext
Loop
rs.Close
......

[Andy Collyer]

That's it! Absolutely perfect. :D

heyessj, you are a genius (that's not to say that you aren't, Serge! :))

Thanks again,

AndyC
London