If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Using ADO.net to get table names

Hi
I would like to be able to provide users a list of available tables (table names) when they connect to a new database (Access 2003). The tables will have have been made externally by the user and they point to the name of the file (e.g. MyAccess.mdb) to access the data.
Does anyone know how to access this information? I found articles on "TableDirect" but have not been able to see examples of implementation and the help files seem to indicate this is used for reading from the tables.
Much appreciate any help
Thanks

The following information is for Access 2000. Access 2003 may be different, but you should be able to discover any differences. I usually use ole automation to get this information, but you should be able to query the MSysObjects table for the name of those items with Type = 1 (4 for linked tables). In Access 2000 I went to Tools/Options. On the view tab I checked System Objects to make the table visible. You can check the fields/values there for your query.

First add a reference to the Microsoft Access Object Library in your project's COM references.

The following job displays the name of the tables in the Output Window, use the same to fill your list:

Code:

Dim app As New Microsoft.Office.Interop.Access.Application
app.Visible = False 'Otherwise Access appears to the user
app.OpenCurrentDatabase("YourDatabase.mdb")
For Each table As dao.TableDef In app.CurrentDb.TableDefs
Debug.WriteLine(table.Name)
Next
app.Quit() 'Otherwise Access keeps running invisible in the background

In order for the Microsoft.Office.Interop namespace to be available, the Microsoft Office PIA (Primary Interop Assembly) must be installed on the development computer as well as on all the user stations. The PIA is installed when you specify that you want the .NET components when installing Office. It can also be installed independantly through a download on the Microsoft web site.