One Star

Microsoft Access db odbc driver issues

Hi.
I've got some fairly good experience w/Talend on a previous project, but am having an issue with using a t_AccessInput to grab a table from an access db.
Environment:
Windows XP
Talend 3.0.2
JDK: 1.6.011
Access Version: Office 2003
I can do this on my development box, but if I use another box where Talend is installed on a minimal OS, I get the following error message when trying to run the job:
Starting job testjob at 11:13 12/03/2009.
Exception in component tAccessInput_1
java.sql.SQLException: Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at test.testjob_0_1.testjob.tAccessInput_1Process(testjob.java:402)
at test.testjob_0_1.testjob.runJobInTOS(testjob.java:661)
at test.testjob_0_1.testjob.main(testjob.java:577)
Job testjob ended at 11:14 12/03/2009.
The job just queries all from a table (select * from table1), and the schema is just the first two columns, an int and a string.
It can't seem to connect to the db either if I hit the ... button next to the db name - it just flashes something real quick then never responds.
Is this a driver issue? Any ideas?
Thanks, Eric

25 REPLIES
Community Manager

Re: Microsoft Access db odbc driver issues

Hello
java.sql.SQLException: Data source name not found and no default driver specified

As the message said, If you use a ODBC source name in your computer, you must re-create the ODBC source name into destination computer also.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Microsoft Access db odbc driver issues

I'm not using an ODBC data source name. I'm simply pointing to the access database file via the property of the t_Access control (on both boxes btw - see attached image).
So, ODBC data source names are not the issue. (that would've been too obvious)
There's a bug, a setup issue, or something else wrong and I was hoping that someone had some real experience with this?
Is there a "default" driver that Talend is not mapping to correctly? It would seem that my development box has the same set of ODBC drivers as the test box though...
Thanks, Eric.
One Star

Re: Microsoft Access db odbc driver issues

So - is anyone else going to weigh in on this issue? Is this a known issue/bug (doesn't seem to be logged as such).
Thanks, Eric
One Star

Re: Microsoft Access db odbc driver issues

Hi,
I am having this issue too. If anyone finds a solution - please let me know.
Richard
One Star

Re: Microsoft Access db odbc driver issues

Richard,
Thanks for the update w/your issues as well.
OK - just some more info in general. As I mentioned in my last post(s) on this topic, the access connection works fine on my (developer) box but not on another box that I installed for a low-tech user. Like I said - I can use it fine w/direct MS Access connectivity without setting up an ODBC DSN - just point to the file and, as Emeril would say - BAM - it works fine. On the other box, we had to setup a DSN and use a generic ODBC connection.
I figured it was all the dev software on my box, the JDK, whatever...so I installed many of the products that are on my dev box onto the other box just to see what would happen (I'm like a dog w/a bone when it comes to this kinda stuff - I just won't let it go)... I installed:
SQL Server 2008 Express
Visual Basic.NET Express, Visual C# Express
JDK 1.6.011
Latest version of MS Access
Still no go on the other box. I couldn't mirror my box because I have just way too many developer tools to install, so it's obviously something else.
Today I am installing it on yet another analyst's box cuz she needs some quick functionality w/xml files and access db's as well (gee this is a great product btw that really keeps these analysts off my back for little mundane tasks like spinning tables into xml docs!). So, I'll post again after this 3rd install and keep the thread alive.
I was hoping that Volker would reply on this, but with some recent acquisitions @ Talend and changes on the Board of Directors, my guess is Volker is downright slammed right now - and soon to be a very rich man, I would bet! haha! schweet, as Cartman would say!
Cheers, Eric.
One Star

Re: Microsoft Access db odbc driver issues

I also have this issue.

With my TOS 3.1.1 Installation everything works fine.
But in my TOS 3.1.3 Installation, i get this error.
One Star

Re: Microsoft Access db odbc driver issues

BTW: When i test the connection in the metadata-DB-Connection-wizard it says: "Access" Connection successful.
One Star

Re: Microsoft Access db odbc driver issues

Does anyone have news on this?
It is urgent.
One Star

Re: Microsoft Access db odbc driver issues

Not giving up on this... Smiley Wink
One Star

Re: Microsoft Access db odbc driver issues

Well, seems as if i will have to stick with TOS 3.1.1 for now.
Community Manager

Re: Microsoft Access db odbc driver issues

Hello Christian
I tested Access with odbc and it works fine on TOS3.1.3.
What's the error message when you run a job?
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Microsoft Access db odbc driver issues

Shong,
If you actually want to test this then you truly need to recreate the scenario and the conditions thereof. Christian, you need to supply all of your environment info. Otherwise, we're just spinning our wheels.
I would suggest that you:
1. Get a pc setup similar to the one in question with a FRESH install of the OS (i.e. not your cluttered development box) that is identical to Christian's setup.
2. Test again.

I can say there's a 50% chance that this problem will recur on your machine, and a 100% chance that it'll recur on my machine and Christian's machine. Personally, I think it has to do with ODBC driver support in the registry and/or an older loader class being used by Talend that Microsoft no longer uses or maps to incorrectly in the registry.
I wish I had time to check this out further, but I don't. My suggestion to the Talend development community is to test Talend installs on side-by-side windows boxes (XP) that also have Visual Studio .NET installed with the latest .NET sps.
Christian - what are your dev environments on that box? Do you have any VStudio or .NET products installed?
Good luck.
Eric.
One Star

Re: Microsoft Access db odbc driver issues

Hi
I am also facing the same problem. I am using TIS Suite. I am simple trying to pull data from an Access Database using tAccessInput. The job runs fine on my machine but when I try to run the job from the talend server, I get the error:
Exception in component tAccessInput_1
java.sql.SQLException: Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at........................
Has anyone found a solution to it?
Thanks,
Nitin
One Star

Re: Microsoft Access db odbc driver issues

Hey
I just found something on the microsoft site:
2007 Office System Driver: Data Connectivity Components
http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displa...
You need to install it on the server side so as to access Microsoft Office system files (e.g. MS Access) from a Non-ms office application.
Now the job runs on the server too.. Smiley Happy
Enjoy!
Nitin
Employee

Re: Microsoft Access db odbc driver issues

Hi,
For Access; we use the ODBC bridge driver to connect to the database; don't forget to reference into the ODBC manager on every computer the database which you would like use for your Talend job.
This step is outside Talend, and is related to your Operating System (Odbc manager) for Windows or Linux.
Best regards;
Community Manager

Re: Microsoft Access db odbc driver issues

Hello All
Note that: if you connect to Microsoft Access 2007, you must install 2007 Office System Driver: AccessDatabaseEngine.exe on your machine. Here is related 3526.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Microsoft Access db odbc driver issues

I'm running TOS 3.2.2, windows XP, Office 2003 (although I believe this is irrelevant), connecting ONLY to Oracle (NOT any MSFT Office products like Access). I'm still getting the famed ODBC error as others on this post. I'm thoroughly confused by this because I have NOTHING to do with using a MSFT data source for connectivity; it is exclusively Oracle and not going through ODBC or user/system data source at all.
As the error log suggests, it appears to be generated when using the talendStats_DB component (this is referenced in the generated java code only to my knowledge).
I'm just puzzed, again, because I'm nowhere referencing an ODBC connection through user/system datasource so why is this error occuring? If it were one job, I could live with it, but it's happening in almost every job which is a huge pain.
Attached is the 1) error log and 2) the project settings area where logging is defined at the project level (again, in Oracle NOT MSFT).
Please help!
Community Manager

Re: Microsoft Access db odbc driver issues

Hello
In the job settings viewer, try to reload the project settings if you have changed the job settings.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Microsoft Access db odbc driver issues

Hi,
Same Issue for me. i have got this ODBC error.
I use windows XP and i have MS Office 2003 installed.
I try to connect to an Ms-Access database (i try 97 and 2003 mdb files). A simple database with one table and two fields in it.
Same ODBC error with TOS-Win32-r35653-V4.0.0M2 and TOS-Win32-r35442-V3.2.3
i'm looking for an ETL that can connect to MSAccess97, MySQL ans XML files. TOS seems to be a very good alternative to do that.
But it doesn't work for me.
Have someone a solution ?
Thanks.
One Star

Re: Microsoft Access db odbc driver issues

TOS is always using JDBC connections to your database--which is why you get an ODBC error when your connection is specified wrong.
The best tips I can find are these:
* Always specify your database connection info in the Metadata "DB Connections" section, which allows you to test it first and have all your DB-related components use the proven connection settings.
* Be sure that your system has the appropriate database drivers, since many of Talend's JDBC drivers depend on the OS's local drivers. The wiki explains a few ones you need to install separately (http://www.talendforge.org/wiki/doku.php?id=doc:installation_guide_external_software). And as explained earlier in this thread, MS Access 2007 needs a local driver install as well (http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displa...).
One Star

Re: Microsoft Access db odbc driver issues

I had de same issue and I solved like follows:
I created the odbc connection in my Windows 7 (Administrative tools-> datasources) with Microsoft Access Driver (*mdb,*.accdb)
In Talend I created a metadata Db Connection with DB Type: Generic ODBC and String connection: jdbcSmiley SurpriseddbcSmiley Very HappySN=DatasourceName;DBQ=C:\\Tar_cli.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
This string connection only works as I show because if you deleted something in the string (like DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5Smiley Wink it fails.
I generated this string connection from excel (I created a Microsoft query from excel to my access database and copied the string "DSN=DatasourceName;DBQ=C:\\Tar_cli.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" to Talend.
My windows 7 is 64 bit system and this only works if you launch TalendOpenStudio-win32-x86.exe ( with TalendOpenStudio-win32-x86_64.exe it fails) I had to execute the C:\Windows\SysWOW64\odbcad32.exe to configure odbc because in Administrative tools --> datasources launchs the ocbcad 64 bit version and was impossible for me to configure odbc datasource.
I hope this helps to someone.
Best Regards.
One Star

Re: Microsoft Access db odbc driver issues

In my case it was a problem with 64 and 32 bit java executables. Played around with different settings (couldn't make it run with java version I wanted) and finally uninstalled all 64bit versions of Java and left only 32bit versions.
It worked when using tODBCOutput with DSN defined, tAccessOutput with DSN defined, even tAccessOutput pointing directly to file name without DSN. And for all this cases it didn't work before, when Java 64bit version was installed.
Hope it helps.
One Star

Re: Microsoft Access db odbc driver issues

found a solution to this.
in your .bat file.
change the path from "java" to the path in your 32 bit program folder.
"C:\Program Files (x86)\Java\jre7\bin\java.exe"
this will make it work.
see example below.
----------------
%~d0
cd %~dp0
"C:\Program Files (x86)\Java\jre7\bin\java.exe" -Xms256M -Xmx1024M -cp classpath.jar; push_accounts_to_winledge_0_1.Push_Accounts --context=Default %*
----------------------------------------------
this took me 2 days to figure out after trying to change environmental variables to try to run the 32bit version of java.
One Star

Re: Microsoft Access db odbc driver issues

What .bat file are you referring to? We are having this issue and I've tried several things. I'd like to take a look at the .bat file but need to know what it is.
Thanks,
Amy
One Star

Re: Microsoft Access db odbc driver issues

What .bat file are you referring to? We are having this issue and I've tried several things. I'd like to take a look at the .bat file but need to know what it is.
Thanks,
Amy

Inside the folder structure of the zipped, compiled, & exported Talend Job, there is a single .bat file. The text inside it is very similar to that above.