1 2 Previous Next 16 Replies Latest reply on Mar 13, 2015 7:38 PM by mdwyer

How do I get an Access tab for a new Connection?

mdwyer Mar 11, 2015 9:05 AM

SQL Developer 2.1.1.64

Go to View / Connections

Click the green + (plus) symbol

A New / Select Database Connection dialog appears

On the right side, below the connection name, username, and password fields is a tabbed area for Role, Connection Type, and connection details. The tab says Oracle.

The documentation for migrating the MS Access Northwind database says to create a connection to the Access database using the Access tab on this dialog.

Where is it/how do I get it?

The same documentation also suggests that after installing and launching SQL Developer, to go to Help / About / Extensions and review the list of installed extensions. While looking for Oracle Migration Workbench, I found Oracle SQL Developer Migrations, and several other hyphenated entries, including - Microsoft Access. The identifier for this is oracle.sqldeveloper.migration.msaccess, v.11.1.1.64.45, status=Loaded. This seems to suggest that the Access module was installed, but that's only a guess.

As the OTN forums seem about as reliable as the documentation, if someone has an answer to post here, could you also send an email XXXXXX com?

Thanks!

Message was edited by: PhHein Email address removed

1. Re: How do I get an Access tab for a new Connection?

rp0428 Mar 10, 2015 10:51 PM (in response to mdwyer)

Please edit your post and REMOVE the personal information - that is NOT appropriate for the forum.

That is a pretty old version. There have been MANY bugs and enhancements since that version was released.

I suggest that you update to the current version of Sql Developer and then repost your question if you have the issue with the new version.
Like Show 0 Likes(0)

Actions
2. Re: How do I get an Access tab for a new Connection?

PhHein Mar 11, 2015 9:05 AM (in response to rp0428)

MOD: email address removed
Like Show 0 Likes(0)

Actions
3. Re: How do I get an Access tab for a new Connection?

mdwyer Mar 11, 2015 8:45 PM (in response to rp0428)

Upgraded to v4.0.3 for win7-64, supposedly with jdk 7. But I have jdk 8, and it isn't clear to me where or how to switch to jdk 7 -- because apparently java 8 does not have an odbc bridge, per Jeff Smith.

My specific error:
Specified driver could not be loaded due to system error 126: The specified module could not be found. (Microsoft Access Driver(*.mdb), C:\WINDOWS\system32\odbcjt32.dll).

FWIW, I have odbcjt32.dll in C:\WINDOWS\sysWOW64
Like Show 0 Likes(0)

Actions
4. Re: How do I get an Access tab for a new Connection?

Gary Graham-Oracle Mar 11, 2015 9:21 PM (in response to mdwyer)
As Jeff notes in the other discussion you commented on:
4.1 EA1 - Connections to Access-DB
you need to find out why the dll is not found, perhaps due to some 32-bit versus 64-bit mismatch in your installation environment.

Otherwise, for your Java JDK concerns...
Use the Help > About > Properties tab, enter java.version in the the Search widget to see which java SQL Developer thinks it is using.
For 4.0.3, control the java version via SetJavaHome in product.conf in C:\Users\<yourUserID>\AppData\Roaming\sqldeveloper\1.0.0.0.0, where Windows treats AppData as a hidden directory.
Like Show 0 Likes(0)

Actions
5. Re: How do I get an Access tab for a new Connection?

mdwyer Mar 11, 2015 10:16 PM (in response to Gary Graham-Oracle)

Help / About has java.version 1.7.0_55
C:\Users\<me>\AppData\Roaming\SQL Developer has folders:
SqlHistory
system1.5.5.59.69
system2.1.1.64.45
system4.0.3.16.84
tmp

1.0.0.0.0 does not exist. No product.conf in the system4... folder.

Googling for info on odbc dll's.
Like Show 0 Likes(0)

Actions
6. Re: How do I get an Access tab for a new Connection?

Gary Graham-Oracle Mar 12, 2015 12:51 AM (in response to mdwyer)
There will be
C:\Users\<me>\AppData\Roaming\SQL Developer\
C:\Users\<me>\AppData\Roaming\sqldeveloper\1.0.0.0.0\
Look in the second directory structure for product.conf.
Like Show 0 Likes(0)

Actions
7. Re: How do I get an Access tab for a new Connection?

mdwyer Mar 12, 2015 3:27 PM (in response to Gary Graham-Oracle)

Ok. Oops. I missed that 2nd folder (Roaming\sqldeveloper). Of course, SD403 sees the jdk7 anyway, so that's a moot issue at this point.
-moving on-
The picture I'm seeing in regard to ODBC is this:
64-bit SQL Developer must be using 64-bit ODBC by default, and presumably not configurable.
I am using AccessXP (Access2002), even though my PC is a 64-bit Windows7 machine.
AccessXP is 32-bit software; it does not have a 64-bit odbc driver.
Unless 64-bit SQL Developer 403 can be configured to use 32-bit ODBC drivers, I will have to try running 32-bit SD.

Since the 64/32-bit version of SD403 does not include a jre, I may resolve the odbc issue and find a new java issue. Since jdk7 was included in the 64-bit download, do you know if that jdk can be installed more directly into the Windows environment? For example, by simply copying folders from sqldeveloper to \Program Files\Java, or \Program Files (x86)\Java\? Or, does SD403/64-32 work with \Program Files (x86)\Java\jre6?
Like Show 0 Likes(0)

Actions
8. Re: How do I get an Access tab for a new Connection?

thatJeffSmith-Oracle Mar 12, 2015 3:44 PM (in response to mdwyer)

you want to run sqldev as a 32 bit app, get a 32 bit jdk then edit your product.conf file to point to that new java home

no JRE - has to be a JDK
Like Show 0 Likes(0)

Actions
9. Re: How do I get an Access tab for a new Connection?

Gary Graham-Oracle Mar 12, 2015 3:57 PM (in response to Gary Graham-Oracle)

Just as you can install as many SQL Developer versions as desired, multiple (32-bit and/or 64-bit) JDK installations are fine. Get what you need from
Java SE Development Kit 7 - Downloads | Oracle Technology Network | Oracle
and during the install process you can skip the public JRE installation. All you need is the JDK, then update the product.conf SetJavaHome line.
Like Show 0 Likes(0)

Actions
10. Re: How do I get an Access tab for a new Connection?

mdwyer Mar 12, 2015 4:23 PM (in response to Gary Graham-Oracle)

Still no joy. The 32/64 version is the same build (4.0.3.16.84) as the 64-bit version, so it still uses 64-bit ODBC and gives the error 126 (odbcjt32.dll not found). Even when run with WindowsXP compatibility, the error is the same.

I don't think I can run SD403 as a 32-bit app; I think I need to run a 32-bit SQL Developer. I'm not sure where I downloaded v2.1.1 last week, but maybe I can find it and look for something between 2.1 and 4.0 - unless you have another suggestion. Thanks!
Like Show 0 Likes(0)

Actions
11. Re: How do I get an Access tab for a new Connection?

thatJeffSmith-Oracle Mar 12, 2015 4:39 PM (in response to mdwyer)

there's no such thing as a 32 bit sql developer - the code is the code. The JDK defines the bit level.
Like Show 0 Likes(0)

Actions
12. Re: How do I get an Access tab for a new Connection?

mdwyer Mar 12, 2015 4:51 PM (in response to thatJeffSmith-Oracle)

I was coming to the same conclusion.
So apparently it's jdk7 that does not use 32-bit odbc.
I'm downloading a v6 jdk, and we'll see how that goes.
Like Show 0 Likes(0)

Actions
13. Re: How do I get an Access tab for a new Connection?

Gary Graham-Oracle Mar 12, 2015 6:38 PM (in response to mdwyer)

Comparing the 32-bit (the i586 one ) vs 64-bit (the x64 one) versions of JDK 1.7, we see the JdbcOdbc.dll size on disk changes from 48KB to 64KB. So just make sure you have the 32-bit JDK installed and update product.conf to point to it. You may also wish to verify your PATH environment variable does not contain references to some other 64-bit JDK.

Also, when you update product.conf, keep in mind that on Windows 64-bit versions...
32-bit code installs into C:\Program Files (x86)\
64-bit code installs into C:\Program Files\
Perhaps you are already aware of that, but I just want to be sure. So many ways to get tripped...
Like Show 0 Likes(0)

Actions
14. Re: How do I get an Access tab for a new Connection?

mdwyer Mar 13, 2015 6:28 PM (in response to Gary Graham-Oracle)

What's past is prologue. Or something.

I've acquired an older, 32-bit Windows 7 PC and loaded SQL Developer 4.0.3 and JDK 1.7.55 on it. Now I can create a connection to the Access database. Technically, this resolves the issue in the original post.

Now I am working through the Migration wizard in SQLD. I get to the Source Database step and see a dropdown for Connection, to "Choose the Third Party Database from which you are migrating." Can you guess that the Access connection I just created is not there? (And no others, but that's the only TPD in the SQLDev environment.)

Should we continue this discussion on a new thread? Thanks!
Like Show 0 Likes(0)

Actions

1 2 Previous Next

Go to original post