List access table names, VBA ArcObject

3051
7
01-24-2011 10:52 AM
New Contributor III
Hi,

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?

Thanks


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
Reply
0 Kudos
7 Replies
New Contributor III
I would think you have to use AccessWorkspaceFactory (esriDataSourcesGDB) instead of OLEDBWorkspaceFactory.
Reply
0 Kudos
New Contributor III
Thanks,

Your nudge in the right direction helped a lot.
Do you know when you would you use the OLEDB connection factory as opposed to the access factory when working with .mdb files? I am new to arcobject programming with .mdb and more information is always nice.

D
Reply
0 Kudos
New Contributor III
It's been a while since I've used any of those interfaces but I think AccessWorkspaceFactory is used more for tabular data while oleDBWorkspaceFactory works with GeoDatabases which can store features.
Perhaps someone else can elaborate on this further (or correct me because I might be wrong).  I'm kind of curious now too.
Reply
0 Kudos
Regular Contributor II
The AccessWorkspaceFactory is used to read personal geodatabases, which are geodatabases stored in an access mdb.  The OleDBWorspaceFactory is used to connect to a database using an OLE driver, could be an Access database, could be another type of database.  The OLE connection would typically be used on a database that is not a geodatabase.  Since OLE connections are not necessarily connecting to Access, they have more parameters to define.  Typically you would define a DNS in the dataconnection utility in the Advanced section of the control panel(XP) and reference that file to connect.  In my experience I have found OLE connections difficult to work with, slow and flaky so I try to avoid them if I can.  The personal geodatabase was a favoured data format prior to the file geodatabase so the AccessWorkspaceFactory was used a lot and is typically quite reliable.  It works on non-geodatabase access mdbs as well.  Aside from limitations of mdbs themselves, the chief complaint about the AccessWorkspace I have heard about i is that it offers too much latitude in what it will allow you to do.
Reply
0 Kudos
New Contributor III
Alex, you have bailed me out yet again on these forums! Thank you!
Furthermore, if I were to be dealing with just tabular data (without any spatial attributes), would it be better (more efficient) to use the OLEdB connection interface in .NET (ADO.NET)?.

Thanks,
Reply
0 Kudos
Regular Contributor II
It is really hard to say which is more efficient without setting both up and doing a real test.  It depends on you measure of efficiency, table sizes etc.  If you are dealing with small tables, a 100% performance gain could only be milliseconds anyway.  Development time and ease of support might be bigger concerns.

You wouldn't do an OLE DB connection to access, you would just use the ODBC driver for Access, ADO.Net supports that.  My suspicion would be that ADO.NET would be faster.  ESRI Cursors tend to be on the slow side, if you don't need to do any ArcObjects with the data it is an interesting route.   Visual Studio has a lot of interesting features when it comes to datatables and data adapters and Linq that work very well with ADO.NET connections.  VS has some drag and drop features that make life simple in this regard.  If you are trying to populate a .net listbox or datagridview  you can bind the control directly to the datatable and edit the data.
Reply
0 Kudos
New Contributor III
Ruchira, Alexander

Again thanks to both of you, I appreciate the clarification it is somewhat difficult for me to find this basic information regarding non-spatial .mdb data manipulation and arcObjects.

Thanks
Reply
0 Kudos