[GIS] Connecting to non geodatabase MS Access database using ArcObjects

arcobjectsms accessvb.net

I am trying to connect to an MS Access database from a program I have written in VB.Net with ArcObjects 10.0. My MS Access database is not a personal geodatabase so apparently I have to connect to Access using an OLEDBWorkspaceFactory.

Dim propset As IPropertySet2 = New PropertySet
propset.SetProperty("CONNECTIONSTRING", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtbox1.Text & ";User Id=admin;Password=;")

Dim oleWorkspace As IWorkspaceFactory2 = New OLEDBWorkspaceFactory
Dim m_workspace As IWorkspace2

If oleWorkspace IsNot Nothing Then m_workspace = oleWorkspace.Open(propset, 0)

The above code works except for one minor detail. When I call oleWorkspace.Open(propset, 0) I get the ArcGIS Database Connection dialogue asking what driver to use and for connection information (which I have already provided in my propertyset).

Is there a way to avoid this dialogue?

Best Answer

You should be able to use the AccessWorkspaceFactory co-class even though it's not a personal geodatabase. You could also use ADO.NET. I am not sure why your code isn't working but I do know that working with property sets is an exercise in frustration.

Related Solutions

[GIS] Embedding map into Access Database

Use a Web Browser Control on your Access form. Then set the Control Source to (for example, using a text box on my form called txtTotalShops and a field called Address1)

="https://maps.googleapis.com/maps/api/staticmap&zoom=7&size=600x300&maptype=roadmap&markers=color:orange%7Clabel:" & [txtTotalShops] & "%7C" & Replace([Address1]," ","+") & ",NSW,Australia&key=AAAAAAAA"

You can change the zoom, size, markers etc as you wish (Note that the marker properties need %7C between them, other properties just an ampersand). Any spaces must be replaced with a plus sign (as in the example for Address1)

You can have up to 15 markers if you are using a human readable address (or unlimited if you use latitude and longitude)

AAAAA needs to be replaced with your own Google API key (free for 25,000 map loads per day)

If the whole statement becomes too long for Access, split it into several hidden text boxes on your form and then concatenate them (=txtWebAddress1 & txtWebAddress2 & txtWebAddress3) etc.

Full documentation (and instructions to get your API key) at https://developers.google.com/maps/documentation/static-maps/intro#Markers

arcobjects – Converting an Access Table into a Geodatabase Table Using ArcObjects

To save yourself a bit of code you could use the geoprocessor and call DataManagementTools.CopyRows. See also: How to run a geoprocessing tool

e.g.:

// Create the geoprocessor. 
Geoprocessor GP = new Geoprocessor();

// Create the tool process object.
ESRI.ArcGIS.DataManagementTools.CopyRows copyrowsTool = new
    ESRI.ArcGIS.DataManagementTools.CopyRows();

// Set parameter values.
copyrowsTool.in_rows = @"D:\St_Johns\data.mdb\roads_table";
copyrowsTool.out_table = @"D:\St_Johns\data.gdb\roads_table";

// Execute the tool. 
GP.Execute(copyrowsTool, null);

As an aside, you can use LINQPad to quickly prototype programs that are more like snippets like this. e.g.:

void Main()
{
    if (ESRI.ArcGIS.RuntimeManager.ActiveRuntime == null)
        ESRI.ArcGIS.RuntimeManager.BindLicense(ESRI.ArcGIS.ProductCode.EngineOrDesktop);
    var gp = new Geoprocessor();
    var copyRowsTool = new CopyRows();
    copyRowsTool.in_rows = @"C:\GISData\Database1.mdb\Table1";
    copyRowsTool.out_table = @"C:\GISData\Database1.gdb\Table1";
    gp.Execute(copyRowsTool, null);
}

LINQPad Screenshot

Add the necessary references (remember to show GAC assemblies) and namespaces in the query properties in LINQPad and off you go!

Image http://img850.imageshack.us/img850/9576/5878c5bb83924c1b9ab7c92.png Image http://img194.imageshack.us/img194/4394/ca8934bca75f47748eb4840.png

Notes:

If you want to use the older version of LINQPad (2.x) that targets .NET 3.5, and you are on a 64-bit OS, you will want to grab the LINQPad for .NET Framework 3.5 - x86 version of LinqPAD from this page. This is because ArcGIS Desktop/Engine is 32-bit and some assemblies (specifically the ESRI.ArcGIS.Version assembly) won't work with the standard LINQPad 2.x executable's build target of AnyCPU on 64-bit OS's. LINQPad 4.x (which targets .NET 4.x) does not seem to have this issue.
Also be sure to set "Always use fresh application domains" under Edit-Preferences-Advanced to avoid "COM object that has been separated from its underlying RCW cannot be used" errors when running a query/program a second time.

Best Answer

Related Solutions

[GIS] Embedding map into Access Database

arcobjects – Converting an Access Table into a Geodatabase Table Using ArcObjects

Related Question