Issue accessing MS Access from Spotfire

'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 = (
    "Provider=Microsoft.Ace.OLEDB.12.0;"
    "Data Source=\\\olgwfap1\Data\Development Division\Res Surv Eng\Personal Folders\Pruet\Data Team\SPOTFIRE\TIDELANDS\Tidelands_Spotifre.accdb;"
)

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.

2 Comments

Yes, I can load data tables from Access into Spotfire by using the Spotfire GUI. My issue is getting IronPython to run SQL in Access.
andrew.pruet - Jan 12, 2016 - 12:04pm ::
First thing is can you go to Access via add data table from Spotfire and get data? If not then start there and get that to work. Second is that 32bit and 64bit drivers really can't co-exist for Access/Office. 64bit Spotfire from version 6 on can access 32bit Access now but there are some limitations.
wirlybird - Jan 12, 2016 - 11:44am ::
+ Add a Comment

(1) Answer

Login