you need to create an ODBC connection in Administrative Tools (or whatever it is called in XP) called db1, you then point this ODBC to your access database.

are you using the JdbcOdbc driver? (sun.jdbc.odbc.JdbcOdbcDriver)?

make sense?
--chad

I'm wanting to do exactly the same. I've basic Java skills, but a little thin on JDBC etc. I need to connect to an MS Access database from a unix box, and I'm hoping I can use Java to do this. Is it possible anyone? If so can anyone point me in the right way forward ??

cheers

Originally posted by hello
(please keep any replies very simple/self explanatory - this is the first time I've had to do this, and I'm feeling quite clueless)

I am having trouble connecting to a database in MS Access using Java.

Could anyone give me some suggestions
or refer me to a web site that may help?

At the moment I am just using code that I found on a java tutorial.
I tried adding the database as a datasource as some of the tutorials describe. However, it seems that I am not doing it correctly. (I'm using Windows XP.)

I keep getting the following errors.

problems connecting to jdbc:odbc:db1
problems with SQL sent to jdbc:odbc:db1: null

C:\magedb>java db1
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
NOT CONNECTED TO THE TO THE DB.
problems with SQL sent to jdbc:odbc:db1: null

Hi,
Search for "atinav aveConnect JDBC Driver for MS Access" in a search engine. You can download the trial-versions of the driver for free from their website.

-den

Originally posted by ukzmcha
I'm wanting to do exactly the same. I've basic Java skills, but a little thin on JDBC etc. I need to connect to an MS Access database from a unix box, and I'm hoping I can use Java to do this. Is it possible anyone? If so can anyone point me in the right way forward ??

cheers

den,

cheers. Would you be able to help me a little further? I hope so as I'm new to using jdbc. The ms access dbase is on an NT server, under a particular filepath. I have an oracle dbase running on a unix box and i need to select data from the mdf, and load it into oracle tables.

the java code is held in the oracle dbase. I'm a little unsure of what goes where though. The JDBC Driver will be implemented as part of te java source code so will reside on my unix box yeah? how does my unix box see the .mdf file though? is it in the java code I specify the ip address and filepath of teh nt server and mdf file?

if there is any example code you may have I'd really appreciate a cpy of it?

thanks ...

Hi,

Use two JDBC drivers, one for MS Access and another for Oracle.

Access the MS Access data and write it to Oracle database through a java program.

Using Java code you can communicate with any platform including UNIX boxes.

I attach a sample code to use with aveConnect Driver for MS Access.

-den

Originally posted by ukzmcha
den,

cheers. Would you be able to help me a little further? I hope so as I'm new to using jdbc. The ms access dbase is on an NT server, under a particular filepath. I have an oracle dbase running on a unix box and i need to select data from the mdf, and load it into oracle tables.

the java code is held in the oracle dbase. I'm a little unsure of what goes where though. The JDBC Driver will be implemented as part of te java source code so will reside on my unix box yeah? how does my unix box see the .mdf file though? is it in the java code I specify the ip address and filepath of teh nt server and mdf file?

if there is any example code you may have I'd really appreciate a cpy of it?

thanks ...

Here is the sample code....

I've attached your example code with a couple of small questions, your help is really appreciated thanks again ...

but why am I needing two jdbc drivers though? wouldn't the atinav aveConnect JDBC Driver on the unix server (implemented in the java code on the unix box) be enough to read the ms access mdb file on the NT server ?

or do I need the atinav aveConnect JDBC Driver on the NT server? does this allow MS access to be accessed by java code?

anychance you could explain the architecture a little more ...

much appreciated

ukz

What u have written the attached file is correct.

Driver for Access can not be used for accessing Oracle databases. That is why we need 2 drivers suited to database types.

Driver is needed with the client Java programs.

-den

thanks again, I see why 2 drivers are needed. we already have an Oracle JDBC Driver available to us ... so in your example I'd have to add code to ;

//implement the oracle driver ...
Class.forName("my oracle driver.jdbc.Driver");
//id the location and name of the oracle dbase
String url ="jdbc:myoracledriver:myUnixDatabaseServer:portnumbe r:bdaseFilePath";
//and set up a new connection to it
Connection cn = DriverManager.getConnection(url,"admin","");

then I'd simply write to my oracle dbase info from the access record set ?

I'll have one java class implementing 2 drivers, one to access and read from MS Access and one to access and Write to Oracle.

One final question if you can help again ... I understand what we've discussed and can now try this out for real ... but i don't understand how my unix box (from where my java code will be executed) has the privilage/rights to access the NT Server (although it will have its DNS name / ip address and port number) ... do we have to set up on the NT server an account and provide username and passwords to connect to the NT Server from my java code via my atinav aveConnect JDBC Driver ?? am I making sense ?

thanks again

thanks again, I see why 2 drivers are needed. we already have an Oracle JDBC Driver available to us ... so in your example I'd have to add code to ;

//implement the oracle driver ...
Class.forName("my oracle driver.jdbc.Driver");
//id the location and name of the oracle dbase
String url ="jdbc:myoracledriver:myUnixDatabaseServer: portnumber:bdaseFilePath";
//and set up a new connection to it
Connection cn = DriverManager.getConnection(url,"admin","");

then I'd simply write to my oracle dbase info from the access record set ?

I'll have one java class implementing 2 drivers, one to access and read from MS Access and one to access and Write to Oracle.

One final question if you can help again ... I understand what we've discussed and can now try this out for real ... but i don't understand how my unix box (from where my java code will be executed) has the privilage/rights to access the NT Server (although it will have its DNS name / ip address and port number) ... do we have to set up on the NT server an account and provide username and passwords to connect to the NT Server from my java code via my atinav aveConnect JDBC Driver ?? am I making sense ?

thanks again

den

do we need to install part of this driver on the NT machine where the MS Access dbase resides ? i'm reading something about installing teh atinav access server component ...

cheers
Mark

Yes, you need to install the Server part in the machine where the *.mdb files reside.

From Java, you are actually communicating to this server.

-den


Originally posted by ukzmcha
den

do we need to install part of this driver on the NT machine where the MS Access dbase resides ? i'm reading something about installing teh atinav access server component ...

cheers
Mark

den - thanks vrey much .. I've cracked this and managed to install drivers, server componaents write sufficient java to both extract from .mdb files and load to oracle tables ...

just one last question if you can help ... I've currently got 2 java classes working seperatley, 1 for extracting from mdb, and 1 for inserting into oracle. is there anyway i can have one class which does both and which will therefore implement 2 jdbc drivers at teh same time ? I'm having trouble with this ...

Mark.

hi den,

yeah thanks, I've got this working quite nicely nicely now. thanks for all your help.

One problem I've got at the minute though, if you've got any ideas ... my class works a treat from my client machine, but when I load my class to my unix box, i'm not able to access and retrieve from MSAccess ... my code is held in the oracle dbase as a java stored procedure (so all the imported classes are uploaded too to oracle).

I think it maybe that i need to specify the machine details in the tnsnames.ora file on my unix box, in order for it to know where to look? I'm trying that now, but incase there's something else, you any ideas ???

mark.