ADO and table names?

Via visual basic code, I need to be able the dynamicly get the names of the tables in a database.

I was able to get the field names of a recordset easy enough, but I want table names within a connection.

ie.     Order_Header_CN.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & strPath & "Order_Header.mdb", there are three tables within this database, how can list them out, via VB code??
LVL 1
Dean_ReedyAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
What do you mean by «had the same problem and couldn't find his way around»? Which problem?

Here is the code I just test and it works perfectly!
Private Sub Command1_Click()
Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String

    Set cnn1 = New ADODB.Connection
    strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False"
    cnn1.Open strCnn
    Set rstSchema = cnn1.OpenSchema(adSchemaTables)
    Do Until rstSchema.EOF
        Debug.Print "Table name: " & rstSchema("TABLE_NAME") & _
            vbCr & "Table type: " & rstSchema("TABLE_TYPE") & vbCr
        rstSchema.MoveNext
    Loop
    rstSchema.Close
    cnn1.Close
End Sub
0
 
DawsonBCommented:
Select*
from Sysobjects
where
0
 
DawsonBCommented:
Oops, prssed TAB then enter accidentally, andyhow, what I was trying to type was:-
with your connection already established, do the following

dim rs as new ADODB.Recordset
dim sql as string

sql = "SELECT Name FROM sysobjects WHERE type = 'U'"
rs.Open sql,cn


rs will now be populated with the names of the User databases in your db

Hope this helps Dean
 
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Dean_ReedyAuthor Commented:
I tried it, but I get an error "micrsoft database jet cannoot find table or 'sysobjects' query......

I tried this:
Dim Order_Header_CN As New ADODB.Connection
Dim rs As New ADODB.Recordset
 Dim sql As StringOrder_Header_CN.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & strPath & "Order_Header.mdb"
    sql = "SELECT Name FROM sysobjects WHERE type = 'U'"
    rs.Open sql, Order_Header_CN
0
 
Éric MoreauSenior .Net ConsultantCommented:
A better solution that sysObjects is to use the OpenSchema method. This is the example from the ADO help file:
Public Sub OpenSchemaX()  
Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset  
Dim strCnn As String      

   Set cnn1 = New ADODB.Connection
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   cnn1.Open strCnn        
   Set rstSchema = cnn1.OpenSchema(adSchemaTables)  
   Do Until rstSchema.EOF
      Debug.Print "Table name: " & _
         rstSchema!TABLE_NAME & vbCr & _
         "Table type: " & rstSchema!TABLE_TYPE & vbCr      rstSchema.MoveNext
   Loop  
   rstSchema.Close      
   cnn1.Close  
End Sub
0
 
DawsonBCommented:
Dean:
 Sorry, should have seen the Jet bit in the connection string, my query will work in SQL server, don't know in Access.
emoreau: I've not used OpenSchema before, but it looks very interesting, I'll have to have a look.


0
 
DawsonBCommented:
Thanks emoreau, I'll DEFINATLY be using that!
your debug.print didn't work for me, though, so I changed it to this below, I've just included it in case Dean had the same problem and couldn't find his way around.
        Do Until rstSchema.EOF
     
        Debug.Print "Table name: " & rstSchema("TABLE_NAME") & _
            vbCr & "Table type: " & rstSchema("TABLE_TYPE") & vbCr
         rstSchema.MoveNext
        Loop

cool
0
 
Dean_ReedyAuthor Commented:
Thanks to everyone!
Dean-Iowa
0
 
DawsonBCommented:
What I mean by had the same problem etc is this:..
 this bit cut from your original suggestion,
      Debug.Print "Table name: " & _
         rstSchema!TABLE_NAME & vbCr & _
         "Table type: " & rstSchema!TABLE_TYPE & vbCr      

And from

My rewrite

Debug.Print "Table name: " & rstSchema("TABLE_NAME") & _
            vbCr & "Table type: " & rstSchema("TABLE_TYPE") & vbCr
         

recordset!Table_Name
is different to
recordset("Table_Name")

:-}
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.