JDBC call to Query in Microsoft Access

Posted on 2003-03-17
Medium Priority
Last Modified: 2008-03-03
Hi experts,

Is it possible to use JDBC to call a Query object in Microsoft Access and retrieve back a ResultSet ?

I tried to use a CallableStatement object and supply it with the name of the Query, but receive a SQLException say that: "Invalid SQL Statement. Expected INSERT, SELECT, UPDATE, PROCEDURE, DELETE". So, I guess my approach is wrong.

Can someone please show me the correct way to do it.

Question by:SuperKinhLuan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 92

Expert Comment

ID: 8156960
CallableStatements are used for calling stored procs.
Use either a Statement, or a PreparedStatement.

Author Comment

ID: 8156999
thanks for your comment.
can you show me how to call a Query in Access using Statement or PreparedStatement ?
LVL 92

Expert Comment

ID: 8157027
Statement s = connection.createStatement();
ResultSet rs = s.executQuery("select * from table");

For more info have a look at:

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

LVL 30

Expert Comment

by:Mayank S
ID: 8157135
Something like:

String query = "SELECT * FROM .... ; " ;
Class.forName ( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;
Connection con = DriverManager.getConnection ( "jdbc:odbc:<database-name>", "username", "password" ) ;
// if not password protected, leave the username and password blank, as ""
Statement stmnt = con.createStatement () ;
ResultSet rs = stmnt.executeQuery ( query ) ;    

while ( rs.next () )
  int intVal = rs.getInt ( "IntegerAttributeName" ) ;
  String str = rs.getString ( "StringAttributeName" ) ;

} // end while

con.close () ;


String update = "INSERT INTO EMPLOYEE VALUES ( ?, ? ) ; " ;
Class.forName ( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;
con = DriverManager.getConnection ( "jdbc:odbc:<database-name>", "username", "password" ) ;
PreparedStatement ps = con.prepareStatement ( update ) ;
ps.setInt ( 1, 89 ) ;
ps.setString ( 2, "Don Henley" ) ;
ps.executeUpdate () ; // add the record to the 'EMPLOYEE' table
con.close () ;

Hope that helps!


Author Comment

ID: 8157257
I really appreciate all of your help, but those are not what I'm looking for.
I'm sorry for not being clear in my question. What I meant is that I have a Query object named 'PersonQuery' in my Access database file (Query is a first class object in Access, the 2nd tab below Table). This query returns a sub-table of a main table (kind of a View in other database engine). Now, my question is whether JDBC can access PersonQuery and receive a ResultSet ? if the answer is yes, could you pls show me a sample code ? thanks in advance.
LVL 92

Accepted Solution

objects earned 225 total points
ID: 8157277
Not sure. Try "select * from PersonQuery".
If that doesn't work then I'm guessing you cannot access it.

Author Comment

ID: 8157374
hi, thanks. that works.
LVL 92

Expert Comment

ID: 8157391
Good stuff :-)

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Suggested Courses
Course of the Month13 days, 5 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question