CODEWITHSUNDEEP
ms-accessodbcms-access-2010
BIBD
BIBD

Reputation: 15414

How can I connect to a System ODBC connection is Access 2010 on Windows 7

I'm trying to connect an Access DB (2010 32-bit) to a System ODBC connection on a Windows 7 PC (64-bit)

However, when I try to link to the Machine Data source for it, I can't find the ODBC data source I created.

I know there's an issue between 32 bit apps and the 64 driver. And that there are two version of the ODBC Datasource Administrator

I've tried both, and neither one seems to be able to create an ODBC source that is visible to me. How can I get this working?

Note: User data sources seem to work fine, but system data sources to not.

Data source I'm working with is a MS SQL Server 2008 DB on a separate machine.

Upvotes: 2

Views: 46381

Answers (2)

nick
nick

Reputation: 630

It's likely the shortcut for setting ODBC data sources is pointing to the 32bit data sources instead of 64bit.

Go to control panel -> administrative tools --> select data sources(ODBC) --> then right click on that file --> go to properties --> in the shortcut tab -> change the path from %windir%\System32\odbcad32.exe to

%windir%\SysWOW64\odbcad32.exe

and make your connection. the driver for MS Access will work fine now.

Upvotes: 1

Albert D. Kallal
Albert D. Kallal

Reputation: 49319

Have you tried making one from Access? When you choose external data sources, odbc, you are given a chance to create the DSN from inside of Access. And you can at that point in time create a system DSN. (Access will launch the correct version of the ODBC manager for you).

Because Access is a 32 bit, then the location of these system DSN's are thus here:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\name of SYSTEM DSN

So you MUST launch the 32 bit version.

(but, it REALLY it is a lot easier to let Access launch this for you).

So, if you launch the 32 bit version of the ODBC manager, this one:

C:\Windows\SysWOW64\odbcad32.exe

Then you should be fine (so do NOT use the one from the control panel).

So, if you create the system DSN using the 32 bit version (the one from sysWOW), then you should see it in Access when you launch the same odbc manager. You see this:

enter image description here

In the above, I created two system DSN's, one from letting Access launch the manager, and the other from laucnhes the syswow version from the cmd prompt - either way, you should see this list.

Upvotes: 4

Related Questions