CODEWITHSUNDEEP
sqlexcelvba
Jamie
Jamie

Reputation:

Excel 2010 32-bit windows 7 64-bit odbc- Connection Issue

We have a Excel spreadsheet that connects to Sage via a vba odbc connection (Username and Password *'d)

Set CN = New ADODB.Connection
With CN
    .Provider = "MSDASQL"
    .ConnectionString = "DSN=SageLine132;UID=******;PWD=******;"
    .CursorLocation = adUseClient
    .Open
End With

This connection works fine on our 32-bit windows xp computers with office 2010, but not on the new windows 7 64-bit computers running office 2010 32-bit.

When I open the odbc32 in the syswow64 folder the system DSN is installed.

Does anyone have any work around for this or a way to connect without accessing the systems DSN (DSN-Less)?

Upvotes: 2

Views: 13011

Answers (1)

fduff
fduff

Reputation: 3811

I suspect this has something to do with the executable being stored in syswow32/ or syswow64/.

I've never used Sage, but I previously had an issue with MyODBC driver on 64b system, the resolution here might help you:

for win 64b users (XP, win7), in order to access your MyODBC driver (or any other DB driver), you need to run the Data Sources from %windir%\SysWOW64\odbcad32.exe.

enter image description here

To make the change permanent, you can add a new Data Sources icon in you Control Panel (must do this from the explorer as it will prompt you for confirmation), name it Data Sources x64, and point it to %windir%\SysWOW64\odbcad32.exe

enter image description here

for the full article, see here.

Upvotes: 3

Related Questions