sql query to MS Access from Java

Hi, I am trying to send SQL queries to MS Access 97 using Java but I only seem to get any response if I have a simple query of the form SELECT * FROM CUSTOMERS.

If I want to send something just slightly more complicated then it doesn't work, for example SELECT SURNAME FROM CUSTOMERS.

The code that I am using is:
Statement stmt = dbCon.createStatement ();
ResultSet rs = stmt.executeQuery (sqlStatement); //error occurs here
ResultSetMetaData rsmd = rs.getMetaData();

Is there a particular format that Access needs when sending a query or is this a problem with it?

Thanks
Andrea

Thanks for the suggestion, I hadn't thought of that. Unfortunately it didn't work.

If anyone has any bright ideas about why this won't work then they are all gratefully received.

Cheers.

Thank you for the suggestion, the problem is now resolved.

For your information (and anyone else who reads this) the actual problem was caused by a corrupt ODBC driver as discovered when I reinstalled Office and the problem disappeared!!!

Cheers

Hi,

I have linked to a MSAccess database using Microsoft Visual J++ which as far I am away is virtually the same in Java.

Try usingthe following code:-

private ResultSet rs;
protected String querystring;

try{
// *** Load driver classes. ***
Class.forName("com.ms.jdbc.odbc.JdbcOdbcDriver");
// *** Create the connection. ***
Connection con = DriverManager.getConnection("jdbc dbc:logic");
// *** Compose the SQL statement. ***
Statement stmt = con.createStatement();
querystring="SELECT name FROM customers";
this.rs=stmt.executeQuery(this.querystring);
combobox.removeAll();
while(rs.next())
{
this.combobox.addItem(rs.getString(1));
}
rs.close();
con.close();
stmt.close();
}catch(Exception e1)
{}

I hope that code helps - that was how I connected to the MSAccess databse using a DSN.

Let me know if it helps you.

Kevin

sorry - the getconnection bit is:-

Connection con = DriverManager.getConnection("jdbc:odbc:logic");

sorry - i didn't realise it put a face in there....

Kevin

This looks ok from an Access standpoint. You might try

SELECT Customers.surname
FROM Customers;