How to connect to an Access database from Java

[Blaxo]

Could someone please explain to me how to make a connection to an Access database from Java?
regards,
Blaxo

 

[MoreFeo]

first of all, go to START menu, EXECUTE odbcad32.exe (ODBC administration). In system DNS, add a microsoft access driver (*.mdb), give it a name (ie myDB) and select your database.
In your program, to create the connection:

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"
Connection con = DriverManager.getConnection("jdbcdbc:myDB"

/*your code, to query the database, you'll need a Statement and a ResultSet*/
Statement stm = con.createStatement();
ResultSet rs = stm.executeQuery("your query" //ie
/*......*/
}catch(Exception ex){}

the method forName can throw some exceptions so you must catch them

 

[dbleyl]

//Load the driver.
//Assuming you haven't bought an access driver, you're using
//The bridge - must have the odbc access drivers installed.
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace("Not a valid driver"
}

//Create a url, substitute your db path for<YOURACCESSFILE>.
//Great opportunity to use Property file or XML config.
//Tweak the odbc settings to your taste.
String url= &quot;jdbcdbcRIVER={Microsoft Access Driver
(*.mdb)};UID=admin;UserCommitSync=Yes;Exclusive=Yes;Threads=15;SafeTransactions=1;PageTimeout=10;MaxScanRows=8;MaxBuffe
rSize=65535;FIL=MS Access;DriverId\=281;
BQ=<YOURACCESSFILE>;&quot;;

//Enable driver manager tracing if you want:
DriverManager.setLogWriter(new PrintWriter(new OuputStreamWriter(System.out)));

//You now have a connection, hopefully.
Connection conn = DriverManager.getConnection(url);

//Create a statement to execute.
Statement st = conn.createStatement();

//execute a query for resultset.
ResultSet rs = st.executeQuery(&quot;SELECT * FROM 5_gb_access_table&quot;

//If you want dynamic column info, remember columns
//start with 1 index (sql).
ResultSetMetaData md = rs.getMetaData();

//print the data.
while (rs.next()) {
for(int i = 1; i <= md.getColumnCount(); i++) {
System.out.print(rs.getString(i) + &quot; &quot;
}
//Add new line
System.out.print(&quot;\n&quot;
}

//clean up.
rs.close();
st.close();
conn.close();
rs = null;
st = null;
conn = null;
...

Hope this is somewhat helpful. If you're stuck using jdbc-odbc bridge with Access, you'll find the odbc driver doesn't support a lot of f(x)ality. You can use the ODBC trace feature from admin tools to troubleshoot.

Good luck.

 

[whuang04]

I think you can only use Java (via ODBC-JDBC bridge) to connect with Access ONLY if the Access database is located on the localhost (same machine as the java program). I don't think you can setup connection if Access is located on a remote host. Tell me if I am wrong.

 

[dbleyl]

Looks like DBQ turned into BQ. Oops! File path should be
DBQ=//server/path/accessfilename.mdb

Yes, you can connect to 'remote' database. Just use a url in the dbq field.

 

[Blaxo]

Thank you all for your help! regards,
Blaxo