topic List access table names, VBA ArcObject in ArcObjects SDK Questions

List access table names, VBA ArcObject

DavidBirkigt — Mon, 24 Jan 2011 18:52:45 GMT

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

Re: List access table names, VBA ArcObject

RuchiraWelikala — Mon, 24 Jan 2011 19:26:18 GMT

I would think you have to use AccessWorkspaceFactory (esriDataSourcesGDB) instead of OLEDBWorkspaceFactory.

Re: List access table names, VBA ArcObject

DavidBirkigt — Tue, 25 Jan 2011 01:34:08 GMT

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

Re: List access table names, VBA ArcObject

RuchiraWelikala — Tue, 25 Jan 2011 12:32:07 GMT

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.

Re: List access table names, VBA ArcObject

AlexanderGray — Tue, 25 Jan 2011 12:53:03 GMT

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.

Re: List access table names, VBA ArcObject

RuchiraWelikala — Tue, 25 Jan 2011 13:11:05 GMT

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,

Re: List access table names, VBA ArcObject

AlexanderGray — Tue, 25 Jan 2011 14:28:15 GMT

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.

Re: List access table names, VBA ArcObject

DavidBirkigt — Tue, 25 Jan 2011 17:50:07 GMT

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