Microsoft Access db odbc driver issues

[Anonymous]

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

[Anonymous]

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...).

[Anonymous]

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: jdbc dbc SN=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=5 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.

[Anonymous]

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.

[_AnonymousUser]

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.

[Anonymous]

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

[JimmyM]

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.

[Anonymous]

You can fix Access db ODBC driver issues on your own, by following simple manual fixes. I have caught into the following error in may access database: “[Microsoft][ODBC Microsoft Access Driver] Cannot Update. Database Or Object Is Read-Only”. 

So, tryout the fixes mentioned here in this post, you may find something helpful, like as I have found:

http://www.accessrepairnrecovery.com/blog/fix-odbc-microsoft-access-driver-cannot-update-database-ob...