List access table names, VBA ArcObject

Discussion created by dbirkigt on Jan 24, 2011
Latest reply on Jan 25, 2011 by dbirkigt

I want access the table names in a microsoft access database (.mdb 2003), and populate a list box, allowing the user to select a desired table. Can anyone point me towards some resources that can help or provide some advice.

Embedded is my code that connects to the database. The second part of the code is something I am using as I can not get a list of tables, and is what I want to replace. At the bottom of the post is code that works in Microsoft Access and displays each table names in a msgbox, how could I run this code from Arc?


Private Sub DatabaseConnection()

Dim strProperties As String

'Store the properties required for the OLEDB connection
strProperties = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Data\database.mdb"

Dim pPropSet As IPropertySet
Set pPropSet = New PropertySet                          'Instantiate a new property set
pPropSet.SetProperty "CONNECTSTRING", strProperties     'Set it properties to the db

Dim pWorkSpaceFact As IWorkspaceFactory

'Set a workspace factory to a new OLEDB factory
Set pWorkSpaceFact = New esriDataSourcesOleDB.OLEDBWorkspaceFactory     

'Using the code below each table can be accessed by its name
'I want to replace this code
'With a list of all the tables in the access .mdb

Dim pFeatWorkspace As IFeatureWorkspace
Set pFeatWorkspace = pWorkSpaceFact.Open(pPropSet, 0)

Dim pTable As ITable
Set pTable = pFeatWorkspace.OpenTable("NPRI.Address")

Dim strCount As String
MsgBox pTable.RowCount(Nothing)

End Sub

This code displays table names in microsoft access, this is what I want to do in Arc.

Dim tbl As AccessObject, db As Object
Dim strMsg As String

Set db = Application.CurrentData
For Each tbl In db.AllTables
MsgBox tbl.Name
Next tbl