I have recently found a script from ArcScripts on how to get an Access table in ArcGIS programmatically and it works well. But this is for Access 2003 (.mdb extension) and earlier. The code is posted below, and I want to know how to modify it for using Access 2007 (.accdb extension) and later databases.
Attribute VB_Name = "Access_connect"
Sub Open_Access_Connect()
'V. Guissard Jan. 2007
On Error GoTo EH
Dim data_source As String
Dim pTable As ITable
Dim TableName As String
Dim pFeatWorkspace As IFeatureWorkspace
Dim pMap As IMap
Dim mxDoc As IMxDocument
Dim pPropset As IPropertySet
Dim pStTab As IStandaloneTable
Dim pStTabColl As IStandaloneTableCollection
Dim pWorkspace As IWorkspace
Dim pWorkspaceFact As IWorkspaceFactory
Set pPropset = New PropertySet
' Get MDB file name
data_source = GetFolder("mdb")
' Connect to the MDB database
pPropset.SetProperty "CONNECTSTRING", "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data source=" & data_source & ";User ID=Admin;Password="
Set pWorkspaceFact = New OLEDBWorkspaceFactory
Set pWorkspace = pWorkspaceFact.Open(pPropset, 0)
Set pFeatWorkspace = pWorkspace
' Get table name
TableName = SelectDataSet(pFeatWorkspace, "Table")
' Open the table
Set pTable = pFeatWorkspace.OpenTable(TableName)
'Create Table collection and add the table to ArcMap
Set mxDoc = ThisDocument
Set pMap = mxDoc.FocusMap
Set pStTab = New StandaloneTable
Set pStTab.Table = pTable
Set pStTabColl = pMap
pStTabColl.AddStandaloneTable pStTab
' Update ArcMap Source TOC
mxDoc.UpdateContents
Exit Sub
EH:
MsgBox "Access connect: " & Err.Number & " " & Err.Description
End Sub
Public Function GetFolder(Optional aFilter As String) As String
' Open a GUI to let the user select a Folder path name (by default) or :
' Set aFilter = "shp" to get a shapefile name
' Set aFilter = "mdb" to get an MS Access file name
' Return the Folder Path or phath & file name As String
' V. Guissard Jan. 2007
Dim pGxDialog As IGxDialog
Dim pFilterCol As IGxObjectFilterCollection
Dim pCurrentFilter As IGxObjectFilter
Dim pEnumGx As IEnumGxObject
Select Case aFilter
Case "shp"
Set pCurrentFilter = New GxFilterShapefiles
aTitle = "Select Shapefile"
Case "mdb"
Set pCurrentFilter = New GxFilterContainers
aTitle = "Select MS Access database"
Case Else
Set pCurrentFilter = New GxFilterBasicTypes
aTitle = "Select Folder"
End Select
Set pGxDialog = New GxDialog
Set pFilterCol = pGxDialog
With pFilterCol
.AddFilter pCurrentFilter, True
End With
With pGxDialog
.Title = aTitle
.ButtonCaption = "Select"
End With
If Not pGxDialog.DoModalOpen(0, pEnumGx) Then
Smp = MsgBox("No selection : Exit", vbCritical)
End
'Exit Function 'Exit if user press Cancel
End If
GetFolder = pEnumGx.Next.FullName
End Function
Public Function SelectDataSet(pWorkspace As IWorkspace, Optional theDataType As String) As String
' Open a GUI to let the user select a DataSet into a Workspace
' (Table or Request into an MS Access Database or a Geodatabase File)
' Set pWorkspace to the DataSet IWorkspace
' Set theDataType = "Table" to select a Table name of the DataSet
' Return the selected Table or Request Table name As String
' V. Guissard Jan. 2007
Dim aDataset As Boolean
Dim boolOK As Boolean
Dim DataSetList As New Collection
Dim datasetType As Integer
Dim n As Integer
Dim pDataSetName As IDatasetName
Dim pListDlg As IListDialog
Dim pEnumDatasetName As IEnumDatasetName
' Set the Dataset Type
Select Case theDataType
Case "Table"
datasetType = 10
Case Else
Answ = MsgBox("Need a Dataset Type : Exit", vbCritical, "SelectDataset")
End
End Select
' Get the Dataset Names included in the workspace
Set pEnumDatasetName = pWorkspace.DatasetNames(datasetType)
' Create the Dataset Names List Dialog
aDataset = False
Set pListDlg = New ListDialog
pEnumDatasetName.Reset
Set pDataSetName = pEnumDatasetName.Next
Do While Not pDataSetName Is Nothing
pListDlg.AddString pDataSetName.name
DataSetList.Add (pDataSetName.name)
Set pDataSetName = pEnumDatasetName.Next
aDataset = True
Loop
' Open a GUI for the user to select a dataset
If aDataset Then
boolOK = pListDlg.DoModal("Select a " & theDataType, 0, Application.hwnd)
n = pListDlg.choice
If (n <> -1) Then
SelectDataSet = DataSetList(n + 1)
Else
Sup = MsgBox("No DataSet selected : EXIT", vbCritical, "SelectDataset")
End
End If
End If
End Function
Here is the link to the ArcScript: http://arcscripts.esri.com/Data/AS14882.bas
PS I know this code is written in VBA and I don't know if a modified version is in VB.NET or whatever else language.
Thanks, Adrian
When trying to figure out connection properties for ArcObjects, I find it helpful to set up an OleDB connection in arccatalog using the GUI. Once it tests successfully, I then run this VBA script to list the connection properties, which I can then copy and paste into my code.
Sub ListConnProps()
Dim pGxApp As IGxApplication
Set pGxApp = Application
If Not TypeOf pGxApp.SelectedObject Is IGxDatabase Then
Debug.Print "select a geodb first"
Exit Sub
End If
Dim pGXdb As IGxDatabase2
Set pGXdb = pGxApp.SelectedObject
Dim names As Variant, values As Variant
Debug.Print pGXdb.WorkspaceName.WorkspaceFactoryProgID
pGXdb.WorkspaceName.ConnectionProperties.GetAllProperties names, values
Dim l As Long
For l = 0 To UBound(names)
Debug.Print names(l), values(l)
Next l
End Sub
Update: Here's some code that I tested successfully after downloading the provider from here, and a test accdb file from here. I wasn't using a password, so couldn't test that.
public static void TestOleDB()
{
IWorkspaceFactory wsf = new OLEDBWorkspaceFactoryClass();
IPropertySet ps = new PropertySetClass();
string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;"
+ @"Data Source=D:\Projects\AmberGIS\Forums\MikeGarage\MikeGarage.accdb;"
+ "Persist Security Info=False";
// if you're using a password, use next line (??)
//((IOleDBConnectionInfo)wsf).SetParameters(connStr, "");
ps.SetProperty("CONNECTSTRING", connStr);
IWorkspace ws = wsf.Open(ps, 0);
IEnumDatasetName enumDsn = ws.get_DatasetNames(esriDatasetType.esriDTAny);
IDatasetName dsn;
while((dsn=enumDsn.Next())!= null)
Debug.Print(dsn.Name);
}