Browsing for Tables within an ADO Database 2

[Currivan]

I'm familiar with the VB method of using a commondialog box to browse for and select an ADO database, such as Access, to open. However, I do not know how to, then, open a dialog box to select a table within the database from which to cull the requisite data. I'm sure there must be a way to do this. Your assistance in this matter will be greatly appreciated.

 

[Swi]

You could just populate a list box or combo box with the list of table names from you Access database. You can use ADOX. Here is an example using a list box.

Private Sub Form_Load()
' Add a reference to:
'Microsoft ADO Ext. X.X for DDL and Security
Dim sCat As ADOX.Catalog
Dim sTable As ADOX.Table

Set sCat = New ADOX.Catalog
sCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\UsersDB.mdb;"

For Each sTable In sCat.Tables
' Checks for system tables
If sTable.Type <> "ACCESS TABLE" And _
sTable.Type <> "SYSTEM TABLE" Then List1.AddItem sTable.Name
Next

Set sCat = Nothing
Set sTable = Nothing

MsgBox "Done!", vbInformation
End Sub

Swi

 

[jebenson]

You can do this with the Microsoft ADO Extension 2.7 for DDL and Security. First reference it and ADO in your project. Go to Project->References and select "Microsoft ActiveX Data Objects 2.5 Library" and "Microsoft ADO Ext. 2.7 for DDL and Security". Note that the version numbers may be different on your machine.

On a form, place a listbox and the following code in the Form_Load event:

Private Sub Form_Load()
Dim conn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim t As ADOX.Table

Set conn = New ADODB.Connection
Set cat = New ADOX.Catalog
Set t = New ADOX.Table

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<Your Database Path And Name Here>;Persist Security Info=False"

conn.Open

cat.ActiveConnection = conn

For Each t In cat.Tables
List1.AddItem t.Name
Next

End Sub

This code loads all of the table names into the listbox. You can then allow users to select a table name, and use that in your data access code.

Hope this helps.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Currivan]

Swi and jebenson, you've both been very helpful! However, I'm not sure I understand Swi's code where it checks for system tables and queries:

If sTable.Type [b]<>[/b] "Access Table"...Then 
    List1.AddItem sTable.Name

Would this be the right condition if I am using an Access database? This doesn't seem intuitive, it seems contradictory.

(I probably will continue using a combo box that will only be populated when a preceding textbox for the database file name has been populated.)

Thanks

 

[jebenson]

Run the code I sent, but replace the line:

List1.AddItem t.Name

with this:

List1.AddItem t.Type & ": " & t.Name

From this you will see that there are 4 table types in the database: SYSTEM TABLE, ACCESS TABLE, VIEW and TABLE. The ones with the type TABLE are the tables you created. VIEWs are queries. SYSTEM TABLE and ACCESS TABLE are tables used by Access to manage the database, and you most likely don't want your users to see them (unless of course the users are DB admins!)

 

[Currivan]

Thanks jebenson! You not only answered the original question, but also my next question as to whether you can use this procedure to access queries. You've been really helpful. I haven't been able to put this into action yet, but I'm sure it'll work just fine.

Thanks again!