Making a connection to an Access Database with SQL Developer v4.0.3 and the JDBC/ODBC Bridge

0
votes

screenshot of SQL Developer ODBC connection issue to MS-Access

http://www.oracle.com/technetwork/articles/sql/o38sql-102034.html (your article at Oracle OTN) I read the Oracle OTN article about making connections to an Access database using SQL Developer, but I can't get it to work.

The error below shows that the default JDBC/ODBC bridge driver or data source name cannot be found.

Status : Failure -Test failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

The Access database exists, and the system tables in the access database are not hidden.

I am using SQL Developer v4.0.3 that uses JDK 1.7. I know that SQL Developer 4.1 uses JDK 1.8, and Access connections are not supported in that latest version of SQL Developer.

I also installed both JDK 1.6 for 64bit and 32bit computing, and changed my system path setting to use of these versions. I have tried both 1.6 versions, without success.

It's my understanding that SQL Developer uses the JDBC/ODBC Bridge and requires no other configuration. A third party JDBC driver is not required to connect to Access.

How do I configure SQL Developer to locate the JDK v1.6 and use the appropriate version (JDK 1.6 64bit or JDK 1.6 32bit) to connect to an Access Database? I've been working on this all day and I'm stumped.

1
oraclems-accessodbc
That's an ODBC error you're looking at. How have you specified the name of the Access database? You may have to set up an ODBC data source to get this to work. – Bob Jarvis - Reinstate Monica
Hi Bob, Thanks for the reply. Yes, the Access database name is specified. The Access database I'm trying to connect to is specified in the connection configuration of SQL Developer v4.0.3. SQL Developer requires a fully rooted path to the Access database (.mdb or accdb file). SQL Developer is supposed to connect without any other configuration, using the JDBC/ODBC Bridge connector located in Java JRE v1.6 or v1.7. The JDBC/ODBC Bridge was deprecated in v1.8. – steve4321
It sounds like you need to ensure that SQL Developer is running under a 32-bit JVM (< v1.8) so it can use the older "Jet" ODBC driver (32-bit only, .mdb only) that ships with Windows. – Gord Thompson
Hi Gord, Thanks for the reply. I am using the JRE 1.7. I confirmed this by running the command java -version from a cmd window. I also have the correct version of SQL Developer v4.0.3. I added a screenshot of the issue in the above screenshot link (first line of this question). I have a system wide DSN configured for MS Access ODBC connections for this computer. See 2nd update link above. Its the 32bit ODBC driver. – steve4321
SOLVED! I downloaded and installed the MS Access v2010 Runtime Engine which contains the 64bit MS Access ODBC driver. This driver works with Office 2007 and Office 2010. microsoft.com/en-us/download/confirmation.aspx?id=13255 – steve4321

1 Answers

0
votes

64bit MS Access ODBC Drivers The solution to enable SQL Developer v4.0.3 to connect to a MS Access database was to install the 64bit ODBC Drivers from Microsoft. I downloaded the Access Runtime Engine version 2010 which works with an Installed 32 bit version of MS Access (and other 32bit 2007 office products) The following links were helpful: https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255 The ODBC drivers are coupled with the MS Access runtime engine.