Getting an Access 2007 table (.accdb extension) in ArcMap programmatically

AdrianWelsh — Sat, 11 Dec 2021 15:27:23 GMT

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

Re: Getting an Access 2007 table (.accdb extension) in ArcMap programmatically

StevenHambacher — Wed, 09 Feb 2011 21:12:16 GMT

Our office recently upgraded to Office 2010 and we have the same problem, sort of. I was wondering if the accdb file extension is supported in the latest version of ArcView desktop. We are running 9.3.1 and the file extension won't allow the file to be viewed. A temporary work around is to save & publish the DB file in 2003 format, but that risks losing functionality. Using the connect to instructions ESRI has posted is too cumbersome for us and I'm told everybody using the file would have to do the same thing on their machine. Some don't have Office 2010 yet and still need to view the files.

Re: Getting an Access 2007 table (.accdb extension) in ArcMap programmatically

PaulDowling1 — Thu, 10 Feb 2011 00:50:28 GMT

Do you even need to get ArcGIS involved? What happens if you just change the OLEDB driver to the 2007-compatible one? Change this line:

pPropset.SetProperty "CONNECTSTRING", "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data source=" & data_source & ";User ID=Admin;Password="

to

pPropset.SetProperty "CONNECTSTRING", "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source=" & data_source & ";User ID=Admin;Password="

and (in theory) up goes the donkey.

Re: Getting an Access 2007 table (.accdb extension) in ArcMap programmatically

NathanOgden — Thu, 10 Feb 2011 14:42:19 GMT

http://resources.arcgis.com/content/kbase?fa=articleShow&d=32976
should answer your question.

Re: Getting an Access 2007 table (.accdb extension) in ArcMap programmatically

StevenHambacher — Thu, 10 Feb 2011 17:51:43 GMT

Thank you, I did see something similiar, but still had some problems. I had two fields that were calculated from a query that caused errors in several places, including not showing up in the exported shape file. When I added two new fields in the shape file and re-calculated them, each calculation crashed ArcView and sent a report to ESRI. Calculations worked and data in fields are correct, but I can see this being really bad for many people/situations. Also discovered you can't publish & save to an earlier Access format, as that won't work any better.

topic Re: Getting an Access 2007 table (.accdb extension) in ArcMap programmatically in ArcObjects SDK Questions

Getting an Access 2007 table (.accdb extension) in ArcMap programmatically

Re: Getting an Access 2007 table (.accdb extension) in ArcMap programmatically

Re: Getting an Access 2007 table (.accdb extension) in ArcMap programmatically

Re: Getting an Access 2007 table (.accdb extension) in ArcMap programmatically

Re: Getting an Access 2007 table (.accdb extension) in ArcMap programmatically