Topic: Database Problems...

[futbolfan]

I'm trying to enumerate all of the tables in an access database file stored locally.  I'm currently using .NET and C++.  Any suggestions as to how I could achieve this using ADO?

[ober]

I normally use DAO for that type of thing because the code is somewhat simpler, but then again, that's in VB6.  I assume that probably wouldn't help you much.  I assume you want the actual ADO implementation, but the method is to access the mdb's system database and grab the names of the tables out of there.  There is a field which indicates whether it is a table or a field name.

So yeah.... I don't think that helped you very much.

[futbolfan]

Haha, don't worry about it ober.  Even something in DAO would fly right now.  Even if it's in VB too.  As you can tell, I'm getting frustrated and desperate.

[ober]

If I remember about it, I'll check at work on monday for an example... I hope I didn't delete that project (it was never really put to use because a web app replaced it).

[ober]

Like I said, I don't know if this helps or not, but this is what I had going... and I think this was for Access if I remember right.  We've now switched over to MS SQL Server.

Code: [Select]

Dim catDB As ADOX.Catalog
Dim tblList As ADOX.Table
Dim colList As ADOX.Column

   On Error GoTo Fill_Error

    SetReady "Grabbing tables/columns...", 500
   If CURLOC <> "" Then
       Set catDB = New ADOX.Catalog
       ' Open the catalog.
       catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CURLOC
   
        ' Loop through all the tables, but not queries,
        ' and print their names and types.
        ReDim TABLENAMES(0)
        ReDim COLNAMES(0)
        i = 0
        j = 0
        For Each tblList In catDB.Tables
            If tblList.Type <> "VIEW" And tblList.Type <> "ACCESS TABLE" And tblList.Type <> "SYSTEM TABLE" Then
                ReDim Preserve TABLENAMES(i + 1)
                TABLENAMES(UBound(TABLENAMES) - 1) = tblList.Name
                For Each colList In tblList.Columns
                    ReDim Preserve COLNAMES(j + 1)
                    lvAvailable.AddItem tblList.Name & "." & colList.Name
                    COLNAMES(UBound(COLNAMES) - 1) = tblList.Name & "." & colList.Name
                    j = j + 1
                Next
                i = i + 1
          End If
       Next
       
       Set catDB = Nothing
       lblq.Caption = lvQuery.ListCount
       lblav.Caption = lvAvailable.ListCount
   End If

[futbolfan]

Thanks ober.  I ended up using Ole because the ADO extensions aren't yet implemented in .NET.  However, your code did give me some terms to search for that made things a lot easier.

And for all those who might have the same problem at some point in the future, look up GetOleDbSchemaTable

[ober]

Glad that mess could be of some assistance.