One Star

Talend Profiler working with MS Access

probably the answer to my question is no it can't be done. But bearing in mind the clever people that are around, is there a work around / solution.
Simply I have an Access Database, yeah sorry I know, but that is what I have and I want to be able to profile the data in it using profiler?
There, simple.
Any help gratefully received
Colin

5 REPLIES
Employee

Re: Talend Profiler working with MS Access

We do not supported this database yet, but you can try to connect with the generic JDBC driver.
One Star

Re: Talend Profiler working with MS Access

Thanks that sounds easy, but I have never used JDBC before, have done ODBC connections. Any pointers you can give. I did ask Mr Google and came back with doing something wiht Java Classes, which kind of looks a bit scarey!
Thanks
Employee

Re: Talend Profiler working with MS Access

Hi Colin,
you can use jdbc driver as follows:
-1 Select Db type "Generic JDBC" on connection wizard.
-2 Select Driver jar :rt.jar (.../jre/lib/rt.jar).
-3 List drivers, Select driver class name "sun.jdbc.odbc.JdbcOdbcDriver".
-4 Fill in Url such as "jdbcSmiley SurpriseddbcSmiley Very HappyRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\mydata.mdb ", don't forget replace "c:\mydata.mdb" with your own file.
-5 Test and finish.
However you may not succeed when you perform finish button. See this bug 7532 on bugtracker.
Alternatively i recommend another way around:
you could use TOS by transfer your data from MS Access to Other db which TOP supported(e.g Mysql,Oracle), Then enjoy your data profiling.
One Star

Re: Talend Profiler working with MS Access

This work-around, looks nice. Yet, I get an error when asking to test or finish: "Can't create data provider!"
Any hint on this?
One Star

Re: Talend Profiler working with MS Access

1 (optional). If you're using TDQ and not TOP, put the file close to the root folder. The total length of path + filename must be less than 50 chars to side-step a current bug when storing the catalog name.
2. In TOP/TDQ, create a generic ODBC connection, specifying the DSN name, "Admin" as the login, no password.
This worked just fine for me.
TOP/TDQ treat it a bit like a file DSN, where each access file under the specified path is treated as a separate schema in the same data source.
IE: I created a system DSN for an access DB in a folder that had a total of 3 access DBs.
When I created a generic ODBC connection to that DSN, it picked up all 3 access DBs in the folder like separate scgemas, then list each of the tables under that.