Results 1 to 4 of 4

Thread: Retrieving Table names

  1. #1

    Thread Starter
    Member
    Join Date
    Jan 1999
    Location
    London, UK
    Posts
    58

    Post

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

  2. #2
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    Post

    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



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

  3. #3
    Lively Member
    Join Date
    Dec 1999
    Posts
    106

    Post

    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
    ......

  4. #4

    Thread Starter
    Member
    Join Date
    Jan 1999
    Location
    London, UK
    Posts
    58

    Post

    That's it! Absolutely perfect.

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

    Thanks again,

    AndyC
    London

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width