topic Re: [resolved] Unable to access *.accdb even after installing AccessDatabaseEngin.exe in Talend Studio

[resolved] Unable to access *.accdb even after installing AccessDatabaseEngin.exe

jagadish_dasari — Thu, 01 Aug 2013 08:08:57 GMT

Hi All,
I am trying to access data in a MS Access database file in 2007 format (*.accdb). To access the data I am first trying to create a DB metadata connection to the *.accdb file. However, when I Test the connection, I am seeing an error:
"Connection failure. You must change the Database Settings.
Data source name not found and no default driver specified"
I have gone through the articles related to this issue on forum and finally found the Bugtracker " https://community.talend.com/t5/Archive/inner-join-in-tMap/td-p/169181".
As per the above article, we need to install the driver for *.accdb files and driver could be installed by executing the AccessDatabaseEngine.exe downloaded from the " http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en ".
Even after these changes I see no progress.
Below is my machine configuration:
1. Win8 OS 64-bit
2. MS Office 2007 32-bit
3. JETL-win-x86_64
Please suggest me how to fix this issue.
NOTE: Has this issue got anything to do with the MS Office 2007 (32/64 bit)?
Thanks in advance,
Jagadish.

Re: [resolved] Unable to access *.accdb even after installing AccessDatabaseEngin.exe

Anonymous — Thu, 01 Aug 2013 08:41:11 GMT

Hi,
From the Microsoft Download Center

The System Requirements
Supported Operating System
Windows 7, Windows Server 2003, Windows Server 2008, Windows Vista, Windows XP
This download works with the following Office program:
2007 Microsoft Office system

Best regards
Sabrina

Re: [resolved] Unable to access *.accdb even after installing AccessDatabaseEngin.exe

jagadish_dasari — Thu, 01 Aug 2013 09:34:03 GMT

Hi Sabrina,
Thanks for the reply, but I have done this update prior posting the error.
- Downloaded the AccessDatabaseEngine.exe from " http://www.microsoft.com/en-us/download/details.aspx?id=23734"
- Installed the driver by executing AccessDatabaseEngine.exe
- Re-started the JETL as well as My machine
- Tested the DB Connection (Access 2007). But I see the same error:
"Connection failure. You must change the Database Settings.
Data source name not found and no default driver specified"
FYI, we are able to access the data from *.accdb file using "JETL-win32-x86.exe" but not using "JETL-win-x86_64.exe". But all our previous jobs are run on "JETL-win-x86_64.exe" and makes no point to revert to "JETL-win32-x86.exe" for this one off.

Re: [resolved] Unable to access *.accdb even after installing AccessDatabaseEngin.exe

Anonymous — Thu, 01 Aug 2013 09:55:38 GMT

Hi,
You should use 64 bit studio consistent with your 64 bit OS.
Please check your ODBC Data Source Administrator to see if there is MS Access Driver.
Best regards
Sabrina

Re: [resolved] Unable to access *.accdb even after installing AccessDatabaseEngin.exe

jagadish_dasari — Thu, 01 Aug 2013 11:54:13 GMT

Sabrina,
I am attaching the screen captures of both 32 & 64 bit ODBC drivers listed.
Also, I see all drivers available for 32 bit ODBC but not for 64 bit ODBC and also 64 bit ODBC shows platform as 32-bit.
In the 64 bit ODBC admin window the "Add" & "Configure" buttons are disabled.
When I tried to add the drivers for MS Access Database (as shown in the third screen capture), I see no appropriate drivers available.
Could this be the reason for not being able to access data from *.accdb file?
If so, what is the way out?

Re: [resolved] Unable to access *.accdb even after installing AccessDatabaseEngin.exe

alevy — Fri, 02 Aug 2013 03:00:24 GMT

If you are running the 64-bit studio (or for exported jobs using 64-bit Java) then you must have and use 64-bit ODBC drivers for your job. Similarly, if you are running the 32-bit studio (or for exported jobs using 32-bit Java) then you must have and use 32-bit ODBC drivers for your job.
The "Data source name not found and no default driver specified" error means there is no DSN of the name you specified of the correct architecture (32/64-bit) matching that of the application you are running.
You'll get a different error ("The specified DSN contains an architecture mismatch between the Driver and Application" or "Invalid string or buffer length") if you try to use a 32-bit driver with a 64-bit application or vice versa.

Re: [resolved] Unable to access *.accdb even after installing AccessDatabaseEngin.exe

jagadish_dasari — Wed, 25 Sep 2013 10:29:27 GMT

Hi All,
Thanks for your suggestions.
We have fixed the issue finally.
Resolution:
It has to do with compatibility of MS Office version.
Earlier we were using Office 2007, which creates DB in *.mdb format.
Office 2010, creates DB in *.accdb format.
To run JETL for accessing *.accdb format DB, we have upgraded Office from 2007 to 2010 (which has updated the drivers automatically).
On upgrading Office and restarting, JETL now working fine.