[RESOLVED] How to list table content of an MS Access database?

[camoore]

A combination of DriveListBox, DirectoryListBox and FileListBox enables a user to browse for and establish a path to a desired file. Once the path has been established, the file can be opened / accessed within a VB program and used. So far so very good.

In my application I have a MS Access database which contains several similar, but different Tables. Call it XYZ.mdb.

The Drive/Directory/File lists enable the XYZ.mdb to be found, but I wish to list one stage further then - the individual Tables within the XYZ.mdb. - so that the user can select which one to use in the program. This I have achieved at runtime by allowing the user to type into a Text box the path to a Table name, but the wish is to be able to present to the user a list of available Tables such that one may be selected by a mouseclick.

Can this easily be achieved please?

camoore

Wales, UK

[techgnome]

This should be in Database Development, where database related questions go. (I've asked the mods to move it, so you shouldn't need to worry about it).

Might I suggest you change the title of your thread (edit the original post) ... something like "How to list tables in Access DB" ...

Actually if you search the forums for "List Tables Access" ... you should find some posts that will help. I know I've seen this asked several times before.

-tg

[Hack]

This should be in Database Development

Agree...thanks for reporting it.

Moved

[jp26198926]

try:

1 DriveListBox = Drive1
1 DirectoryListBox = Dir1
1 FileListBox = File1
1 Listbox = List1
1 Command Button = Command1

Option Explicit

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


Private Sub Command1_Click()

Dim DB_Path As String

DB_Path = File1.Path & "\" & File1.FileName

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_Path

Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))

Do Until rs.EOF
    List1.AddItem " " & rs!TABLE_NAME
    rs.MoveNext
Loop

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub

[camoore]

Thank you jp26198926 for your helpful suggestion.

I have adapted your code into my TestFileList program. This allows a user to select a drive, directory and file. It then writes the full path to that file in text box 1. If, however, this is a file of extension ".mdb" the program then goes further using your code, as adapted a bit, to write all the embedded tables into a list box from which the user may select any one table. That selected .mdb table is shown at Text 2.

One comment : Initially the code failed at the first line.
Dim cn as ADODB.Connection
was rejected by my VB6 and threw up an error. After a search of the Forum I found reference to the need to select the correct ActiveX Data Objects Library.

My system was using Microsoft ActiveX Data Objects Library 2.0. I found that this has to be unchecked, and Library 2.1 selected instead. (This is done in VB6 design stage by going to PROJECT > REFERENCES and unchecking / checking the desired libraries). Hopefully 2.1 contains just additions to 2.0 and not omissions which will cause other aspects of programs not to run. If both 2.0 and 2.1 are enabled, a conflict error is flagged up.

Attached as a .zip is my test program. Your suggestion was very helpful. I will mark the thread RESOLVED.

camoore

Wales, UK

[camoore]

Although the original query has been resolved, thanks especially to jp26198926, I would now like to ask one further theme related question please.

I am now able to allow the user to browse from Drive to Directory to .mdb file to Table name, and to display a list of available tables in a list box. Great!

Can I now go one stage further and, upon clicking on one of the listed Tables, populate a second list box with the field (ie, column) names of that table? I have used the Forum search routine, but could not find a reference to this particular action.

Am working in VB6. The MS Access database was as released with MS Office 2000.

camoore

Wales, UK

[jp26198926]

this same code with a little changes.
place this code on ur List1 Click Event.

Dim DB_Path As String


DB_Path = File1.Path & "\" & File1.FileName

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_Path

Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, Trim(List1.List(List1.ListIndex))))

List2.Clear

Do Until rs.EOF
    List2.AddItem " " & rs!COLUMN_NAME
    rs.MoveNext
Loop

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

[camoore]

Hi Again jp26198926. Thank you for another VERY useful piece of code. I adapted it just a little to suit my test program and it ran first time.

That SCHEMA routine is very good, but I could find no reference to it anywhere in any of my VB6 textbooks.

My test program has now been updated to "TestFileList1". It is as before, but now when the user clicks on a .mdb Table name in ListBox1 all the fields are listed at ListBox2 and when one of these is clicked its name appears in TextBox3.

This absolutely satisfies my present need, and will come in handy on many future occasions.

Thank you for taking the trouble to reply to me again.

The TestFileList1 demmo program is attached as a .zip.

camoore

Wales, UK