-
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] *
* *
* * * * * * * * * * * * * * * * * * * * * *
-
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
[email protected]
[email protected]
ICQ#: 51055819
[This message has been edited by Serge (edited 12-07-1999).]
-
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
......
-
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
