Specifying version of Microsoft ODBC for Oracle (32/64 bit) in connection string for DSN-less connection (Access 2013 (32 bit))

Question

Per

http://www.accessmvp.com/djsteele/DSNLessLinks.html

I've attempted to replace DSN-created table defs in Access with a DSN-less connection.

No luck trying the driver from Microsoft:

Microsoft ODBC for Oracle

because of this specific issue:

https://support.microsoft.com/en-us/kb/942976

On Windows 7 64 bit, you have to manage the ODBC driver (DSN) using the 32 bit ODBC Admin panel, if you want to use Access 2013 32 bit.

However, that doesn't solve the issue because I'm trying to go DSN-less in the first place.

I assume that the reason the connection doesn't work is that Windows is "helpfully" choosing the 64-bit version of the driver when I use this as my connection string:

strConnectionString = _
    "ODBC;DRIVER={Microsoft ODBC for Oracle};" & _
    "Server=" & ServerName & ";" & _
    "UID=" & UID & ";" & _
    "PWD=" & PWD & ";"

The issue is I can't specify that I want the 32-bit version of the driver, because both versions (32 and 64) have the same name.

Is there a way around this?

Thanks, Chad.

Answer 1

The location of driver you can check in your Registry. In case of x86 (32 bit) check HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\Microsoft ODBC for Oracle\Driver. Most likely the value should be %WINDIR%\system32\msorcl32.dll

Now, in case you run a x86 application like your MS Access folder %WINDIR%\system32\ is automatically redirected to %WINDIR%\SysWoW64\ (see WoW64), so you (or MS Access) will find file msorcl32.dll in folder c:\Windows\SysWOW64\.

In principle for x64 you would have to check Registry value HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Microsoft ODBC for Oracle\Driver, however Microsoft ODBC Driver for Oracle is deprecated and available only for 32 bit! In case you need a 64 bit version you have to download and install ODBC driver from Oracle.

Your 32bit Access should work without any problem using 32 bit ODBC drivers.

Answer 2

There is no confusing issues, since an “in-process” x32 program will only load and use the x32 driver. And an “in-process” x64 bit program will only load and use the x64 driver. They both can have the same name, but they are installed and in different locations on the OS.

And if you read your linked articles, the simple solution is to always launch the correct version of the ODBC manager. And the simplest way to do this is to create the link from inside of access since access will ALWAYS launch the correct ODBC manager.

And at the end of the day, since you using DSN less, then this further means the links you use have zero to do with your question and issue.

So while the drivers might have the same name, the OS and software can ONLY use the same bit size software and thus that what will be used in your case.

The only issue you want to check is while in Access you want to launch on the external data tab from import and link the ODBC manager. At that point you link a single table to oracle using that manger and then access will grab (copy) that connection string. And you note that if no oracle driver is displayed, then you have to install the oracle driver (but likely the oracle driver already exists).

Then AFTER verifying that the link works, then simply hit ctrl-g to jump to the debug window, and display the connection string setup with this command:

? currentdb.TableDefs("table I just linked").Connect

You then see the connection string used, and the name/syntax of the oracle drive will be shown – that’s what you use in your connection string.

I should also point out that when launching the ODBC manager from Access the “default” tab is “File data source” – this will ALWAYS create a DSN less connection in Access. So Access by default will always use and create a DSN-less connection. The File Data Source is ONLY used at create time. Once you created the linked table then you could for example move the database to a different computer and the table link will continue to just work fine due to Access using DSN links by default (so the information is “copied” to the table def connection string for that linked table – the original file data source from that point on that you saved via the ODBC panel is ignored and not used.

Bottom line: All those links to those articles don’t matter and are NOT your problem or issue. You simply have to create a linked table using the GUI in Access to oracle. If that works, then simply look at the connection string for that linked table as per above and it will show/display the correct driver string you need to use.

Answer 3

Here's the issue and how it was solved:

Legacy system that started probably in Access 97 (.mdb) that is currently in Access 2013 (.accdb) format. (We aren't budgeted to re-platform the system until next year)

There are hundreds of linked ODBC tables to Oracle data sources, only a handful of which are actually used. Only 15 tables were either being used directly or being subsequently linked by other .accdb databases.

---

The root of the problem was that four of the tables had been created using either a now missing DSN, a missing or out-of-date user id, or a bad password.

Users would see the "ODBC link failed" message during one of their processes and have to go to linked table manager and puzzle through the names of the tables to figure out which ones to re-link. (Usually they'd select all 200 of them).

Then they'd get prompted to update the DSN/UID/PWD.

Of course when doing that, Access 2013 doesn't save that information to the tabledefs... so every time the users would try to run their processes again, they'd see the ODBC failure message again.

The solution was to drop and re-add the four linked tables with the up-to-date DSN.

I also added a button for them on the main form that went through the shortened list of ODBC linked tables (only 15 of them) and ran TableDef(_TABLENAME_).RefreshLink on the table def for them.

---

Thank you to both Albert and Wernfried for the insights.

_{(I'm hoping to be part of the team that replatforms the system to exist all in SQL Server and/or Oracle (backend) with the front end in HTML 5 next year, so this was a good stop gap).}