Issue accessing MS Access from IronPython

Question

I'm trying to run scripts in MS Access using IronPython in Spotfire. Thus far, I can't even get connected to MS Access.

Here is my code for connecting:

import clr
import System
clr.AddReference("System.Data")

from System.Data import DataSet
from System.Data.Odbc import OdbcConnection, OdbcDataAdapter

connectString = (
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
    "Dbq=\\\olgwfap1\Data\Development Division\Res Surv Eng\Personal Folders\Pruet\Data Team\SPOTFIRE\TIDELANDS\Tidelands_Spotifre.accdb;"
    "Uid=Admin;PWd=;"
)

query = "Select * From FOP_Calc"


connection = OdbcConnection(connectString)
adaptor = OdbcDataAdapter(query, connection)
dataSet = DataSet()
connection.Open()
adaptor.Fill(dataSet)
connection.Close()

Executing this, I get the following error:

System.Data.Odbc.OdbcException (0x80131937): ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at stub$296##296(Closure , CallSite , CodeContext , Object ) at Microsoft.Scripting.Actions.MatchCaller.Call2[T0,T1,TRet](Func4 target, CallSite site, Object[] args) at Microsoft.Scripting.Actions.CallSite1.UpdateAndExecute(Object[] args) at Microsoft.Scripting.Actions.UpdateDelegates.Update2[T,T0,T1,TRet](CallSite site, T0 arg0, T1 arg1) at $287##287(Closure , Scope , LanguageContext ) at Spotfire.Dxp.Application.ScriptSupport.IronPythonScriptEngine.ExecuteForDebugging(String scriptCode, Dictionary2 scope, Stream outputStream) at Spotfire.Dxp.Application.Scripting.ScriptService.ExecuteForDebugging(String scriptCode, Dictionary2 scope, Stream outputStream) at Spotfire.Dxp.Application.Scripting.ScriptManager.<>c__DisplayClass14.b__f() at Spotfire.Dxp.Framework.Commands.CommandHistory.Transaction(Executor executor, Boolean visible, Boolean sticky, Guid stickyGuid) at Spotfire.Dxp.Framework.Commands.CommandHistory.Transaction(String displayName, Executor executor) at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.Transaction(String displayName, Executor executor) at Spotfire.Dxp.Application.Scripting.ScriptManager.ExecuteScriptForDebugging(String scriptCode, Dictionary`2 scriptArguments, String& output) at Spotfire.Dxp.Forms.Framework.Scripting.ScriptEditDialog.RunButton_Click(Object sender, EventArgs e)

The file name is definitely correct so I'm assuming it's the default driver giving it trouble.

If it makes any difference, my SpotFire (which hosts the IronPython) is a 64 bit installation and I'm using MSOffice 32 bit (I do have 64 and 32 bit MS Access ODBC drivers installed though).

Any help is appreciated, Thanks.

Provider= is used for OleDb connections. For ODBC, you need something like Driver={Microsoft Access Driver (*.mdb, *.accdb)}; instead. See Microsoft Access accdb ODBC Driver connection strings — HansUp
Good call, I made that change but am still getting the same error: connectString = ( "Driver={Microsoft Access Driver (*.accdb)};" "Dbq=\\\olgwfap1\Data\Development Division\Res Surv Eng\Personal Folders\Pruet\Data Team\SPOTFIRE\TIDELANDS\Tidelands_Spotifre.accdb;" ) — Andrew Pruet
Nuts! Unfortunately my Python is way rusty and I've not done IronPython at all, so I don't know what's wrong now. One point I wonder about though ... are you positive you have both the 32 and 64 bit Access drivers installed? I understood that is not a configuration MS supports. — HansUp
You are correct, I thought I did but trying to actually configure the 64 bit version throws an error saying "The specified DSN contains an architecture mismatch between the Driver and Application". So maybe it is a driver issue? — Andrew Pruet
And yes, I did trying with both *.mdb, *.accdb and just *.accdb — Andrew Pruet

Gord Thompson Gord Thompson · Accepted Answer · 2016-01-13T00:23:22

If your IronPython script is running in a 64-bit environment then you would need to have the 64-bit version of the Access Database Engine installed. Or conversely, if you have the 32-bit version of the Access Database Engine installed (because you have 32-bit Office) then your IronPython script needs to be running in a 32-bit environment.

It is possible to force both the 32-bit and 64-bit versions of the Access Database Engine to reside on the same machine but it is not recommended. It can cause problems with Office and is not a supported configuration.

Issue accessing MS Access from IronPython

1 Answers