topic Re: Microsoft Access db odbc driver issues in Talend Studio

Microsoft Access db odbc driver issues

Anonymous — Sat, 16 Nov 2024 14:02:28 GMT

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

Re: Microsoft Access db odbc driver issues

Anonymous — Fri, 13 Mar 2009 06:14:21 GMT

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

Re: Microsoft Access db odbc driver issues

Anonymous — Fri, 13 Mar 2009 12:27:52 GMT

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.

Re: Microsoft Access db odbc driver issues

Anonymous — Wed, 18 Mar 2009 12:42:58 GMT

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

Re: Microsoft Access db odbc driver issues

_AnonymousUser — Wed, 25 Mar 2009 09:42:48 GMT

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

Re: Microsoft Access db odbc driver issues

Anonymous — Wed, 25 Mar 2009 11:51:28 GMT

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.

Re: Microsoft Access db odbc driver issues

Anonymous — Wed, 22 Jul 2009 10:19:17 GMT

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.

Re: Microsoft Access db odbc driver issues

Anonymous — Wed, 22 Jul 2009 13:33:15 GMT

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

Re: Microsoft Access db odbc driver issues

Anonymous — Thu, 23 Jul 2009 07:48:13 GMT

Does anyone have news on this?
It is urgent.

Re: Microsoft Access db odbc driver issues

Anonymous — Thu, 23 Jul 2009 14:32:07 GMT

Not giving up on this...

Re: Microsoft Access db odbc driver issues

Anonymous — Tue, 28 Jul 2009 10:11:50 GMT

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

Re: Microsoft Access db odbc driver issues

Anonymous — Tue, 28 Jul 2009 10:26:44 GMT

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

Re: Microsoft Access db odbc driver issues

Anonymous — Tue, 28 Jul 2009 13:55:29 GMT

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.

Re: Microsoft Access db odbc driver issues

Anonymous — Wed, 29 Jul 2009 13:47:50 GMT

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

Re: Microsoft Access db odbc driver issues

Anonymous — Wed, 29 Jul 2009 14:00:51 GMT

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&displaylang=en
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..
Enjoy!
Nitin

Re: Microsoft Access db odbc driver issues

Anonymous — Wed, 29 Jul 2009 15:10:07 GMT

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;

Re: Microsoft Access db odbc driver issues

Anonymous — Thu, 30 Jul 2009 03:25:25 GMT

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

Re: Microsoft Access db odbc driver issues

Anonymous — Fri, 18 Dec 2009 19:37:30 GMT

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!

Re: Microsoft Access db odbc driver issues

Anonymous — Mon, 21 Dec 2009 07:19:36 GMT

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

shong

Re: Microsoft Access db odbc driver issues

_AnonymousUser — Tue, 09 Feb 2010 10:06:42 GMT

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.