Important alert: (current site time 4/18/2014 1:13:49 AM EDT)
 

article

Access Database Manipulation via JDBC (for Microsoft Access)

Email
Submitted on: 3/1/2002 3:50:09 AM
By: Jeff Patterson  
Level: Beginner
User Rating: By 597 Users
Compatibility: Java (JDK 1.1), Java (JDK 1.2)
Views: 622692
author picture
(About the author)
 
     This will teach you how to connect to a Microsoft Access database. It's also a great overview of JDBC. Once you are connected, you may run any SQL statement that is allowable on Access, such as SELECT, etc. You don't even have to have MS Access installed to run this tutorial - it shows you how to make a blank one without Access!

 
 
Terms of Agreement:   
By using this article, you agree to the following terms...   
  1. You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
				

Sorry if the formatting is a little screwed up on this - PlanetSourceCode seems to modify my HTML just a little when I upload it...it should still all be readable enough...

If you find this useful, please vote for me!

How to manipulate a Microsoft Access Database via JDBC
and it's also
A Super Quick Overview of JDBC Basics

This will teach you how to connect to a Microsoft Access database. Once you are connected, you may run any SQL statement that is allowable on Access, such as:

  • a SELECT statement to retrieve data
  • an INSERT statement to add data
  • a DELETE statement to remove data
  • an CREATE TABLE statement to build a new table
  • a DROP TABLE statement to destroy a table
This document goes at a pretty slow pace, so you may not need to cover every little detail here. If you are entirely new to JDBC, you shouldn't have too much trouble following along. So let's get going!

Steps to take:

There are three things we need to do to manipulate a MS Access database:
1) Set up Java to undestand ODBC,
2) Get a connection to our MS Access Database,
3) Run a SQL statement.

1) First we need to set up Java to understand how to communicate with an ODBC data source

2) After we set up the DriverManager, we need to get a Connection
There are two ways to get a connection from your Microsoft Access Database:
  1. Get a connection by accessing the Database Directly
    The simpler way, but may not work on all systems!
  2. Set the Access Database up as an ODBC DSN and get a connection through that
    A little more complex, but will work on any system, and will work even if you don't already have a Microsoft Access Database!

3) Once you have gained access to the Database (been granted a connection), you are ready to try:



In addition, please refer to the section at the end of this document:

Step 1) Set up your DriverManager to understand ODBC data sources BACK TO TOP
The first thing we must do in order to manipulate data in the database is to be granted a connection to the database. This connection, referenced in the Java language as an Object of type java.sql.Connection, is handed out by the DriverManager. We tell the DriverManager what type of driver to use to handle the connections to databases, and from there, ask it to give us a connection to a particular database of that type.

For this tutorial, we are interested in accessing a Microsoft Access database. Microsoft has developed a data access method called ODBC, and MS Access databases understand this method. We cannot make a connection directly to an ODBC data source from Java, but Sun has provided a bridge from JDBC to ODBC. This bridge gives the DriverManager the understanding of how to communicate with an ODBC (ie a MS Access) data source.

So the first thing we'll do is set up our DriverManager and let it know that we want to communicate with ODBC data sources via the JDBC:ODBC bridge. We do this by calling the static forName() method of the Class class. Here is an entire program that accomplishes what we're after:

class Test
{
 public static void main(String[] args)
 {
 try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 }
 catch (Exception e) {
System.out.println("Error: " + e);
 }
 }
}
//save this code into a file called Test.java and compile it
Notice the TRY-CATCH block. The forName() method might throw a ClassNotFoundException. This really can't happen with the JDBC:ODBC bridge, since it's built in to the Java API, but we still have to catch it. If you compile and run this code, it's pretty boring. In fact, if it produces any output, then that means that you've encountered an error! But it shows how to get your DriverManager set.

We're now ready to try and get a connection to our specific database so we can start to run SQL statements on it!


Step 2 method 1) Get a connection by direct access BACK TO TOP
One way to get a connection is to go directly after the MS Access database file. This can be a quick and easy way to do things, but I have seen this not work on some windows machines. Don't ask me why - I just know that it works sometimes and it doesn't others...

Here is a complete sample program getting a connection to a MS Access database on my hard drive at D:\java\mdbTEST.mdb. This sample includes the lines required to set the DriverManager up for ODBC data sources:

import java.sql.*;
class Test
{
 public static void main(String[] args)
 {
 try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// set this to a MS Access DB you have on your machine
String filename = "d:/java/mdbTEST.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end 
// now we can get the connection from the DriverManager
Connection con = DriverManager.getConnection( database ,"",""); 
 }
 catch (Exception e) {
System.out.println("Error: " + e);
 }
 }
}
//save this code into a file called Test.java and compile it

Notice that this time I imported the java.sql package - this gives us usage of the java.sql.Connection object.

The line that we are interested in here is the line

 Connection con = DriverManager.getConnection( database ,"","");
What we are trying to do is get a Connection object (named con) to be built for us by the DriverManager. The variable database is the URL to the ODBC data source, and the two sets of empty quotes ("","") indicate that we are not using a username or password.

In order to have this program run successfully, you have to have an MS Access database located at filename location. Edit this line of code and set it to a valid MS Access database on your machine. If you do not already have an MS Access database, please jump down to Set the Access Database up as an ODBC DSN section, which shows how to create an empty MS Access database.

If you do have a MS Access database, and this is working correctly, then you're ready to Run an SQL Statement!

Step 2 method 2) Set up a DSN and get a connection through that BACK TO TOP
Microsoft has provided a method to build a quick Jet-Engine database on your computer without the need for any specific database software (it comes standard with Windows). Using this method, we can even create a blank Microsoft Access database without having MS Access installed!

As we learned earlier, MS Access data bases can be connected to via ODBC. Instead of accessing the database directly, we can access it via a Data Source Name (DSN). Here's how to set up a DSN on your system:

  1. Open Windows' ODBC Data Source Administrator as follows:
    • In Windows 95, 98, or NT, choose Start > Settings > Control Panel, then double-click the ODBC Data Sources icon. Depending on your system, the icon could also be called ODBC or 32bit ODBC.
    • In Windows 2000, choose Start > Settings > Control Panel > Administrative Tools > Data Sources.
  2. In the ODBC Data Source Administrator dialog box, click the System DSN tab.
  3. Click Add to add a new DSN to the list.
  4. Scroll down and select the Microsoft Access (.MDB) driver
  5. Type in the name "mdbTEST" (no quotes, but leave the cases the same) for the Data Source Name
  6. Click CREATE and select a file to save the database to (I chose "d:\java\mdbTEST.mdb") - this creates a new blank MS Access database!
  7. Click "ok" all the way out
Now our data source is done! Here's a complete program showing how to access your new DSN data source:
import java.sql.*;
public class Test
{
 public static void main(String[] args) 
 {
 // change this to whatever your DSN is
 String dataSourceName = "mdbTEST";
 String dbURL = "jdbc:odbc:" + dataSourceName;
 try { 
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(dbURL, "",""); 
 }
 catch (Exception err) {
System.out.println( "Error: " + err );
 }
 }
}
//save this code into a file called Test.java and compile it

As stated in the code, modify the variable dataSourceName to whatever you named your DSN in step 5 from above.

If this complies and runs successfully, it should produce no output. If you get an error, something isn't set up right - give it another shot!

Once this is working correctly, then you're ready to Run an SQL Statement!

Step 3) Running a SQL Statement on your Access Database BACK TO TOP
Once you have your connection, you can manipulate data within the database. In order to run a SQL query, you need to do 2 things:
  1. Create a Statement from the connection you have made
  2. Get a ResultSet by executing a query (your insert/delete/etc. statement) on that statement
Now lets learn how to make a statement, execute a query and display a the ResultSet from that query.

Refer to the following complete program for an understanding of these concepts (details follow):

This code assumes that you have used the DSN method (Step 2 method 2) to create a DSN named mdbTest. If you have not, you'll need to modify this code to work for a direct connection as explained in Step 2 method 1.

import java.sql.*;
public class Test
{
 public static void main(String[] args)
 {
 try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/* the next 3 lines are Step 2 method 2 from above - you could use the direct
access method (Step 2 method 1) istead if you wanted */
String dataSourceName = "mdbTEST";
String dbURL = "jdbc:odbc:" + dataSourceName;
Connection con = DriverManager.getConnection(dbURL, "",""); 
// try and create a java.sql.Statement so we can run queries
Statement s = con.createStatement();
s.execute("create table TEST12345 ( column_name integer )"); // create a table
s.execute("insert into TEST12345 values(1)"); // insert some data into the table 
s.execute("select column_name from TEST12345"); // select the data from the table
ResultSet rs = s.getResultSet(); // get any ResultSet that came from our query
if (rs != null) // if rs == null, then there is no ResultSet to view
while ( rs.next() ) // this will step through our data row-by-row
{
/* the next line will get the first column in our current row's ResultSet 
 as a String ( getString( columnNumber) ) and output it to the screen */ 
System.out.println("Data from column_name: " + rs.getString(1) );
}
s.execute("drop table TEST12345");
s.close(); // close the Statement to let the database know we're done with it
con.close(); // close the Connection to let the database know we're done with it
 }
 catch (Exception err) {
System.out.println("ERROR: " + err);
 }
 }
}
//save this code into a file called Test.java and compile it

If this program compiles and runs successfully, you should see some pretty boring output:

   Data from column_name: 1   

While that may not seem like much, let's take a quick look at what we've accomplished in the code.

  1. First, we set the DriverManager to understand ODBC data sources.
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    
  2. Then, we got a connection via the DSN as per Step 2 method 2:
     String dataSourceName = "mdbTEST";
     String dbURL = "jdbc:odbc:" + dataSourceName;
     Connection con = DriverManager.getConnection(dbURL, "",""); 
    
    We could have used the direct method instead to get our connection.
  3. Next, we created a java.sql.Statement Object so we could run some queries:
     Statement s = con.createStatement();
    
  4. Then came the exciting stuff - we ran some queries and made some changes!
     s.execute("create table TEST12345 ( column_name integer )"); // create a table
     s.execute("insert into TEST12345 values(1)"); // insert some data into the table
     s.execute("select column_name from TEST12345"); // select the data from the table
    
  5. The next part might be a little strange - when we ran our select query (see above), it produced a java.sql.ResultSet. A ResultSet is a Java object that contains the resulting data from the query that was run - in this case, all the data from the column column_name in the table TEST12345.
     ResultSet rs = s.getResultSet(); // get any ResultSet that came from our query
     if (rs != null) // if rs == null, then there is no ResultSet to view
     while ( rs.next() ) // this will step through our data row-by-row
     {
    /* the next line will get the first column in our current row's ResultSet 
    as a String ( getString( columnNumber) ) and output it to the screen */ 
    System.out.println("Data from column_name: " + rs.getString(1) );
     }
    
  6. As you can see, if the ResultSet object rs equals null, then we just skip by the entire while loop. But since we should have some data in there, we do this while ( rs.next() ) bit.

    What that means is: while there is still data to be had in this result set, loop through this block of code and do something with the current row in the result set, then move on to the next row.

    What we're doing is looping through the result set, and for every row grabbing the first column of data and printing it to the screen. We are using the method provided in the result set called getString(int columnNumber) to get the data from the first column in our result set as as String object, and then we're just printing it out via System.out.println.

    We know that the data in our ResultSet is of type String, since we just built the table a couple of lines before. There are other getXXX methods provided by ResultSet, like getInt() and getFloat(), depending on what type of data you are trying to get out of the ResultSet. Please refer to the JSDK API for a full description of the ResultSet methods.

  7. After that we just cleaned up our database by dropping (completely removing) the newly created table:
     s.execute("drop table TEST12345");
    
  8. Lastly, we need to close the Statement and Connection objects. This tells the database that we are done using them and that the database can free those resources up for someone else to use. It is very important to close your connections - failure to do so can over time crash your database! While this isn't too important with a MS Access database, the same rules apply for any data base (like Oracle, MS SQL, etc.)
     s.close(); // close the Statement to let the database know we're done with it
     con.close(); // close the Connection to let the database know we're done with it
    

That's it!! Now you know the basics for connecting to a MS Access Database via JDBC!


If you found this useful, please vote for me!






What I assume you already know BACK TO TOP
This document assumes that you are working on a Windows machines since we'll be connecting to a Microsoft Access database.

I assume you are familiar with database concepts. If you don't know anything about what a database is or what it is for, please take 5 minutes and read this description from Webopedia.

I do assume that you understand Java syntax to a degree, and that you are comfortable compiling and executing Java code. If not, please point your browser to the Java Tutorials provided by Sun Microsystems - they'll get you started.


If you found this useful, please vote for me!


Other 2 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

3/1/2002 3:58:26 PMcdb8

Excellent article. I am 2 wks new to Java. I was able to fully understand and follow the steps outlined. Thanks Jeff!
(If this comment was disrespectful, please report it.)

 
3/4/2002 9:07:01 AMJose M Serrano

Good Article !!! 5 from me
(If this comment was disrespectful, please report it.)

 
3/20/2002 1:58:44 AMZebastion

Great, keep up the good work dude!
(If this comment was disrespectful, please report it.)

 
3/20/2002 2:06:11 AMKen

Very well done, thank you for speaking plain english.
(If this comment was disrespectful, please report it.)

 
3/23/2002 10:19:50 AMTailgunner

Great! I'll need for my university project, thanks! 5 globes
(If this comment was disrespectful, please report it.)

 
3/24/2002 9:41:18 PMpedro santana

very good - keep up the good work!!
(If this comment was disrespectful, please report it.)

 
4/5/2002 3:45:17 AMRic.

Very good article , thanks.
(If this comment was disrespectful, please report it.)

 
5/4/2002 4:33:17 AMSarfraz Ahmed

Its perfect.I was searching these things for many days.Tell me one thing,can it run on jdk1.3.Please mail me ahmed_pk82@hotmail.com
(If this comment was disrespectful, please report it.)

 
5/13/2002 8:00:39 AMkulani

I wrote a program to describe to to
connect to Microsoft Access as you decribed above but i got this error
Java.sql.sqlException[Microsoft][ODBC Driver Manager ]Data source name not found and default driver specified
(If this comment was disrespectful, please report it.)

 
5/13/2002 8:24:41 AMkulani

I ve been trying to use JDBC to connect to Microsoft Access as you described above but i got this error java.sql.sqlException [Microsoft][ODBC Driver Manager]Data source name not found and no default driver specified please help,i am doing a project and i am stuck,my project is due soon
(If this comment was disrespectful, please report it.)

 
6/5/2002 12:25:26 PMpradeep

well this is nice article for the beginers who want to work with MS Access
iam sure those people who want to make such programme with MS Access they will get most important tips from this article
thanking you.
(If this comment was disrespectful, please report it.)

 
6/15/2002 3:37:24 AMSharif

Really a nice article. I m very much new to java and just know nothing about JDBC. But it made me very confident to work on DB using Java. Thanks a lot to the Author !!!
(If this comment was disrespectful, please report it.)

 
7/2/2002 6:45:29 AMsuresh

The way of explanation is very good.Really new ones can easily go through.
(If this comment was disrespectful, please report it.)

 
7/17/2002 11:57:01 PMarvin

hey the code is very good!! really i can use this one, can i request you something? can you give me a bean that could connect to SQL server and the output in JSP i know you can do it. also example of add edit delete appreciate it if you give me soon, you can email me at arvinski@pinoymail.com
(If this comment was disrespectful, please report it.)

 
7/22/2002 12:57:34 AMsam

thanks to the author, you really give me a big hand on this topic!!! but i want to ask here is, is it possible i use similar method to put database in my applet? and the more frightening thing is, how can i update and store it ? i wanna build up some database in the game, after getting the score, it would store it in a list.how can i do that? thanks for regard.
P/s: I m not quite understand about
1. s.execute("create table TEST12345 ( column_name integer )"); // create a table
2. s.execute("insert into TEST12345 values(1)"); // insert some data into the table
3. s.execute("select column_name from TEST12345"); // select the data from the table
where the table TEST12345 we had created?


(If this comment was disrespectful, please report it.)

 
8/24/2002 5:14:29 PMp.pradeep

The best website i have ever found in the
Net World .

(If this comment was disrespectful, please report it.)

 
9/5/2002 12:45:25 AM

Hi. Good article. I'm working on a RMI project that need a DB. Since I can't install any DB drivers I was going to have my server save is datas in XML format. With this, I now know that I can, and may use, an Access DB for this project. It will also give me the oportunity to normalise the data a little bit.

I still want to do a XML-DB manager. But for now I don't have the time.

Tanks.
(If this comment was disrespectful, please report it.)

 
9/19/2002 9:25:54 PM

when i compile the
(If this comment was disrespectful, please report it.)

 
9/22/2002 10:28:22 PM

VERY VERY GOOD,I USE IT IN MS SQL200,AND PASS,
(If this comment was disrespectful, please report it.)

 
9/26/2002 5:53:40 AM

super concise, clear and detailed explanation with encouraging simplicity.
The author is one step away of beating even the legendary Peter Norton in simplicity.
(If this comment was disrespectful, please report it.)

 
9/26/2002 11:17:33 PMJosh McCullough

very nice, 5 from me!
(If this comment was disrespectful, please report it.)

 
10/18/2002 4:02:34 AMGareth Stretch

great work, just starting out, it helped alot thanks
(If this comment was disrespectful, please report it.)

 
10/20/2002 11:01:37 PM

Thats a great article, thks so much to the author. As a beginner in java, I find this very helpful and easy to understand and this has helped my project.
(If this comment was disrespectful, please report it.)

 
10/20/2002 11:45:29 PM

Thanks Jeff It has helped me a lot
(If this comment was disrespectful, please report it.)

 
11/2/2002 2:32:30 PM

I found an error during the process of compiling my application..
(If this comment was disrespectful, please report it.)

 
11/2/2002 2:43:04 PM

I was working with an application that uses JDBC-ODBC bridge and I had problems.. the message was "[Microsoft][JDBC-ODBC Driver for Microsoft Access]Degree of derived table does not match column list" What can I do? Can you help me?
(If this comment was disrespectful, please report it.)

 
11/22/2002 1:59:31 PM

Excellent! I was running queries within an hour! Thank you!
(If this comment was disrespectful, please report it.)

 
12/27/2002 11:51:24 AM

Very concise article. i spent 3 days prior to reading this article to connect to an access database. I managed to do in in under 10 minutes with the help of this article. Thanks
(If this comment was disrespectful, please report it.)

 
1/3/2003 6:44:07 PM

Concise yet complete - thanks!
(If this comment was disrespectful, please report it.)

 
1/16/2003 8:25:44 PM

This code was very usefull now I do not need to use my c++ code to access the db. I am currently building a project which communicates via serial port to a cnc, this code makes it alot easyer for me to maintain file address table for storage and retrieval. In closing I have tryed to get this table info for quite some time. Thank you and keep up the excellent work.
(If this comment was disrespectful, please report it.)

 
1/16/2003 9:59:55 PM

Additional SQL statements can be found @
http://www.geocities.com/SiliconValley/Vista/2207/sql1.html
(If this comment was disrespectful, please report it.)

 
2/18/2003 9:50:29 PM


(If this comment was disrespectful, please report it.)

 
2/18/2003 9:54:59 PM

String aa="INSERT INTO PRODUCT VALUES("+a1.getText()+","+a2.getText()+",12.50,25.75,5000)";
stmt.executeUpdate(aa);< BR>THERE
IS ERROR:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
(If this comment was disrespectful, please report it.)

 
2/27/2003 8:11:34 PM

Really helpful and clear.
(If this comment was disrespectful, please report it.)

 
4/3/2003 1:10:42 AM

Brilliant article. You should definitely go into teaching!
(If this comment was disrespectful, please report it.)

 
4/5/2003 8:49:46 PM

I┤m sorry, but i couldnt vote for you, because i get a login and the validation aplication didn┤t let me to the log. I try it 3 times and always an error message stating page not found apears. never the less i┤ll give you for the help that this code gave to me 5 stars.Thank you.
(If this comment was disrespectful, please report it.)

 
4/28/2003 11:32:07 AMlevissimard

Perfect article, Really helpful and clear. Thanks
(If this comment was disrespectful, please report it.)

 
4/30/2003 1:17:06 PM

briliant article.just fabulous
(If this comment was disrespectful, please report it.)

 
5/16/2003 8:32:35 AM

Jeeez, it provided me with everything I needed, and could not find pouring over 200 pages of jdbc specification.
Clean, straight to the point, and simple.
Keep it like this :)) Good job :)
(If this comment was disrespectful, please report it.)

 
5/19/2003 3:51:49 AM

Excelent!
That's that I needed to start programming in JAVA.
THANKS!


(If this comment was disrespectful, please report it.)

 
5/26/2003 10:23:41 AM

While your code is useful, it would have been nice for you to tackle what is seemingly the most difficult portion of Java and Access, Date functions.
(If this comment was disrespectful, please report it.)

 
6/2/2003 1:32:32 PM

U help me to have a good start on Database with Java from this good example.... : )
(If this comment was disrespectful, please report it.)

 
6/7/2003 10:27:17 PM

Absolutely fantastic article! I wish more people would contribute their knowledge so clearly and freely. You should be a teacher!
(If this comment was disrespectful, please report it.)

 
7/1/2003 3:57:54 PM

Beautiful article. I have been using JDBC for several months with Oracle; it is nice to see it proven that the same techniques will work equally well on the smaller scale.
(If this comment was disrespectful, please report it.)

 
7/4/2003 4:36:46 PM

Jeff....you've done it man. I was struggling with my project on database connectivity but your straight forward approach done it. Thumbs up.
madmax2@excite.com
(If this comment was disrespectful, please report it.)

 
7/31/2003 12:35:58 AM

Nice tutorial. Hope u can submit a downloadable form as Word document,pdf or etc.
(If this comment was disrespectful, please report it.)

 
8/3/2003 3:19:47 AM

Thank you.you are the best.
(If this comment was disrespectful, please report it.)

 
8/18/2003 3:44:17 AM

yes!!! that very nice code but how if that every time i run this program and the database table already create like your code below :
s.execute("create table TEST12345 ( column_name integer )");

that will be the problem that because the table already exits in the database right?
so before the coding of create a new table u should code a statement for or something else that to check whether the table is exit or not right? ^_^
i know u can improve your code ...
(If this comment was disrespectful, please report it.)

 
10/1/2003 4:07:55 PMDilshan Jesook

Ever think of writing a book? you got what it takes. By the way any one knows how to connect to a MS SQL DB ?
(If this comment was disrespectful, please report it.)

 
10/2/2003 3:52:17 AM

This stuff was really useful to me and my friends. Thanks a lot. keep it up
(If this comment was disrespectful, please report it.)

 
10/10/2003 8:47:54 AMRoyscki

tnx! you helped me a lot dude.
(If this comment was disrespectful, please report it.)

 
11/19/2003 2:57:54 PM

Good one..indeed!
(If this comment was disrespectful, please report it.)

 
12/4/2003 9:20:17 PM

Yo yo yo.
Da great mate.
(If this comment was disrespectful, please report it.)

 
12/29/2003 2:21:27 AM

great article u got there. i'm a student and currently i'm looking for articles like urs. i need help from u (if u're willing). i'm looking for article or source code that helps me to use form using sun java application (same like vb. u double click the control and u'll be able to write code for that event). i couldn't get my program work. any ideas?
(If this comment was disrespectful, please report it.)

 
1/13/2004 8:19:54 PM

I'm using the following code:
ResultSet rs = stmt1.executeQuery(
(If this comment was disrespectful, please report it.)

 
1/13/2004 8:22:50 PM

I'm using the following code: ResultSet rs = stmt1.executeQuery("SELECT * FROM prereq WHERE CourseDept = " + dept + " AND CourseCode = " + code);
And getting this error: "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1." . Anyone know how to fix this problrm ?
(If this comment was disrespectful, please report it.)

 
2/27/2004 2:33:49 AM

Very nice work. I'm intersted similar document using oracle. can you help me?

(If this comment was disrespectful, please report it.)

 
3/4/2004 11:47:10 PM

FANTASTIC!!! I searched the internet for quite a while before finding your page. This was exactly what I needed. I'm no newbie to DB coding - but I am new to the Java/MSAccess relationship. I couldn't make that first step until now. Thank you thank you thank you!
(If this comment was disrespectful, please report it.)

 
3/18/2004 4:33:00 AM

Super! I followed your instructions and I got it to work on my database in less than a minute! (Note: I'm a Java newbie)
(If this comment was disrespectful, please report it.)

 
4/19/2004 3:04:44 AM

superb work !
(If this comment was disrespectful, please report it.)

 
4/20/2004 4:41:41 PM

EXELLENT! Both the code and the explanation. I have a question if you are willing to lend an eyeball or two. I've compiled a derivative of your code and am able to do SQL query of my accessDB as I like while in MS command tool. My Problem is that I want to pass the records to Javascript in an HTML doc. When I invoke as using Netscape 7, the Loading of the Applet Fails. The Java console reports a runtime error: java.lang.ClassCastException sun.applet.AppletPanel.createApplet(Unknown Source) sun.plugin.AppletViewer.createApplet(Unknown Source) sun.applet.AppletPanel.runLoader(Unknown Source) sun.applet.AppletPanel.run(Unknown Source) java.lang.Thread.run(Unknown Source) Any pointers or even a smack on the head would be greatly appreciated. Bob
(If this comment was disrespectful, please report it.)

 
5/26/2004 10:55:50 PM

ur d best.. i got an idea quickly through your code.. it beats any book iv read.. tnx a lot.. U R D BEST.. wish u luck in all your endeavors..
(If this comment was disrespectful, please report it.)

 
6/2/2004 5:00:08 AM

thanks for this absolute guideline, im able to connect to database bcoz of this coding thanks.
(If this comment was disrespectful, please report it.)

 
6/3/2004 4:53:56 AM

it was excellent,i really thanks the author and ask him to keep updating us with his valueable knowledge,
in all the fields.
(If this comment was disrespectful, please report it.)

 
6/3/2004 11:50:36 PMcsf23

Great article. I'm new to Java but I can understand it.
(If this comment was disrespectful, please report it.)

 
7/18/2004 8:51:44 AM

I am trying to use this code from within an application running as a server. It compiles OK but when I try to make a connection to a database I keep getting the error message:

Error: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name too long

The combined String used to make the connection is:

jdbc:odbc:{Driver= Microsoft Access Driver (*.mdb)} DBC = C:/TEST3.mdb; DriverID=22; READONLY=true

Has anyone had this problem and is there a solution.
It doesn't use the main() method of the class, it uses the constructor.
(If this comment was disrespectful, please report it.)

 
7/23/2004 9:48:20 PM

hey pal I really appreciate your help..pls teach me how to access the printer thru java thx! pls i really nid it for my project as a first year..pls send it to my mail asap plsssssss...disasterpiece069@yahoo.com..thanks and more power!
(If this comment was disrespectful, please report it.)

 
7/23/2004 9:51:52 PM

I really appreciate your help it was very helpful in my part..sir could you plss send me an article or source code on accessing the printer thru the java language, i really nid it for my project..thnx! plss send it asap pls plsplsplspls my mail:disasterpiece069@yahoo.com..thx and more power!
(If this comment was disrespectful, please report it.)

 
8/3/2004 2:52:50 PM

Nice article. I was reading a Oracle JDBC book and trying to figure how to adapt the code for Access...but it looks like you did the work for me. Thanks! ;)
(If this comment was disrespectful, please report it.)

 
8/4/2004 8:54:07 AM

Very Usefull...
One very less ppl use access+jdbc....
but its really good
(If this comment was disrespectful, please report it.)

 
8/5/2004 10:30:50 PM

Iam very new to java,it would be better if we could get coding in the same way for remote MsAccess database.
(If this comment was disrespectful, please report it.)

 
8/23/2004 12:33:33 AM

Thanks a lot man you code rocks, it was my first to use that and it works great.
(If this comment was disrespectful, please report it.)

 
8/29/2004 2:39:49 AM


small applications on struts .
EJB and MVC architecture.will be best.
(If this comment was disrespectful, please report it.)

 
9/12/2004 2:08:19 AM

thanx alot my dear , relly its good artical
(If this comment was disrespectful, please report it.)

 
9/12/2004 11:57:40 AM

Very consize and straightforward. Understood the explanations completely....cos is written in an easy to digest manner. well done!!
(If this comment was disrespectful, please report it.)

 
10/12/2004 8:26:43 AM

How can i find JDBC for MS Access2002?
Please help me!
(If this comment was disrespectful, please report it.)

 
10/20/2004 8:11:30 AM

It's very simple but It's useful for me.
thankalot
(If this comment was disrespectful, please report it.)

 
11/2/2004 6:16:48 AM

Very Useful, especially for beginners. this kind of explanation we cannot get in books.
(If this comment was disrespectful, please report it.)

 
11/17/2004 3:53:57 AM

very nice and simple article to understand the basics. good work man keepitup
(If this comment was disrespectful, please report it.)

 
12/7/2004 5:27:16 PM

Thank you! I searched through a bunch of articles, and your connection string was the only one I could get to work.
(If this comment was disrespectful, please report it.)

 
12/22/2004 8:43:45 PM

I have looked at several websites based upon subjects regarding JDBC, ACCESS, URL and the like; This article was the only one that directly answered all the the questions that I had in a matter of minutes!! Versus other sites which like most simply talking around the issue or question at hand.


(If this comment was disrespectful, please report it.)

 
1/4/2005 7:05:52 AM

great work mr.author but i need more stuffs like this simulating like a complete application.....
(If this comment was disrespectful, please report it.)

 
3/7/2005 11:34:22 AM

Thank's, This Article is great. Send me another great article's

my email agush@indosat-m3.net
(If this comment was disrespectful, please report it.)

 
3/9/2005 7:18:05 AM

I have a form in microsoft access in which i have created a chart. How do i retrieve it using a java code
(If this comment was disrespectful, please report it.)

 
3/9/2005 7:20:18 AM

i have created a form in microsoft access in which i have created a chart. How do i retrieve the chart using a java code

(If this comment was disrespectful, please report it.)

 
3/17/2005 8:46:39 PM

Thanks !!! this is very useful especially if you're new to java. It helps me a lot, especially all those explanation. This is a straight forward programming that will help thousands of programmers who are new to java.
This is execellent !!!
(If this comment was disrespectful, please report it.)

 
3/24/2005 5:39:59 AM

As simple as you can make it!
Thanks
OK I am waiting for the next instalment.
Also Java and MySQL
(If this comment was disrespectful, please report it.)

 
3/25/2005 2:50:53 PM

The Code is really good and I managed to follow and got it working.

I'm stuck on the filename though. The example for connecting directly is to a database on the local drive. I've tried to specify the location on the server but its throwing up a ERROR: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. I've replaced
(If this comment was disrespectful, please report it.)

 
3/25/2005 2:53:35 PM

..follow on from previous email.. I've repleced
d:/java/mdbTEST.mdb with http://localhost:8080/ora/ch11/emp.mdb. Could someone please tell me where I've gone wrong. many thanks.
(If this comment was disrespectful, please report it.)

 
3/31/2005 6:05:08 AM

Thanks for the code, it helped me a lot.

Now i am having problem using date and time format, please give me example on it.
my id rajunagidi@yahoo.co.in
(If this comment was disrespectful, please report it.)

 
4/6/2005 4:29:40 AM


hello sir
can you explain me this two line in details please,just i have stared to study java

String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= filename.trim() + ";DriverID=22;READONLY=true}";
(If this comment was disrespectful, please report it.)

 
4/11/2005 5:32:31 AM

sir, can you tell me how to display data from microsoft access to the Graphic User Interface.. Hope to hear from you A.S.A.P
(If this comment was disrespectful, please report it.)

 
4/14/2005 9:18:58 PM

Jeff:

Thanks a lot! You had the solution to my problem. The clear code examples made it a snap!
(If this comment was disrespectful, please report it.)

 
4/19/2005 9:55:22 AM

good stuff
(If this comment was disrespectful, please report it.)

 
4/23/2005 4:17:07 AMEvilgenious

The real thing is missing.How i am going to get the column name. The methods getString(column_name) or getString(column_number) returns the column values but not the column name. Plz help me. I want to get the column name from code.
(If this comment was disrespectful, please report it.)

 
4/24/2005 3:09:30 AMEvilgenious

Thanks Jeff. I have made it. 5G
(If this comment was disrespectful, please report it.)

 
4/29/2005 5:04:38 AMKarthik Veeramani

A drop in the ocean, still, you have my vote!

Please clear a small doubt - Suppose I need to distribute my app to other windows users, is there a way to distribute the DSN along with the code, and make our code read the db location from the DSN file? Ofcourse, your first method addresses this, but you say it doesnt work always. I'm wondering if the "file dsn" option in ODBC control panel can be used... pls advise.
(If this comment was disrespectful, please report it.)

 
5/3/2005 6:25:45 AM

thanks very much jeff... it's pratically perfect the way u explain the subject...impossible not to understand! thanks very much!! ;-)
(If this comment was disrespectful, please report it.)

 
7/1/2005 8:42:50 PM

HXTT Access, a pure Java type 4 JDBC driver for MS Access, is at http://www.hxtt.net/en/software/access/index.html .
(If this comment was disrespectful, please report it.)

 
7/7/2005 5:41:43 AM

Nice for new it professional in J2E
(If this comment was disrespectful, please report it.)

 
8/24/2005 2:53:02 AMjak

if (rs != null){
while ( rs.next() ) {

Date tDate;
Double tUsr, tSys, tWio, tIdle;
tDate = rs.getDate("Week");
tUsr = rs.getDouble("usr");
tSys = rs.getDouble("sys");
tWio = rs.getDouble("wio");
tIdle = 1 - (tSys + tUsr + tWio);
java.sql.Date sqlDate = new java.sql.Date( tDate.getTime() );

Cpu.moveToInsertRow();
Cpu.updateDate("Date", sqlDate);
Cpu.updateDouble("Usr", tUsr);
Cpu.updateDouble("Sys", tSys);
Cpu.updateDouble("Wio", tWio);
Cpu.updateDouble("Idle", tIdle);
Cpu.updateString("Plant_Id", "AC02");
Cpu.insertRow();
}
}

/*
** I still get the Error in Row error
** Error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Error in Row
*/



(If this comment was disrespectful, please report it.)

 
8/24/2005 2:53:50 AMjak

Also:

BTW I did change the "Date" to "Date_Col"

I printed the Timestamp to the screen and this is what it looks like:
2004-03-24 00:00:00.0

Timestamp tDate;
tDate = rs.getTimestamp("Week");

System.out.println("Date: " + tDate ); //:=2004-03-24 00:00:00.0

Cpu.moveToInsertRow();
Cpu.updateTimestamp("Date_Col", sqlDate);
Cpu.insertRow();

I still get the following error
Error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Error in row
(If this comment was disrespectful, please report it.)

 
12/21/2005 3:31:56 AM...

Very impressive. This certaintly saved at least one frustrated coder from selling his soul to the devil.
(If this comment was disrespectful, please report it.)

 
4/10/2006 2:26:49 PMGarima Mishra

these code are useful to understand the connectivity os access with jdbc.
inthis we directly give the queries.if we want frm user how we perform it .kindly give suggestion regarding this.
(If this comment was disrespectful, please report it.)

 
4/23/2006 8:27:25 PMsd

Its just ok.
(If this comment was disrespectful, please report it.)

 
4/27/2006 6:30:00 PMSelman

a very useful article..Thanks Pattersonn
(If this comment was disrespectful, please report it.)

 
6/8/2006 4:14:06 PMSaqib Saud

I wish if I could kiss u,

u cant imagine how much i searched any found nothing,
ur article solved my problem

:@
(If this comment was disrespectful, please report it.)

 
6/12/2006 5:10:28 PMPedro

The information seems good! But... got a few questions...For starters, does this work well using Java Remote Method Invocation?

(If this comment was disrespectful, please report it.)

 
7/10/2006 4:40:11 PMrad

I ve been trying to use JDBC to connect to Microsoft Access as you described above but i got this error java.sql.sqlException [Microsoft][ODBC Driver Manager]Data source name too long. what could be wrong? help.

(If this comment was disrespectful, please report it.)

 
7/25/2006 1:51:18 PMLouis

Very good refresher!
(If this comment was disrespectful, please report it.)

 
9/12/2006 1:40:55 PMAtul

Good its very helpfull to new programmers
(If this comment was disrespectful, please report it.)

 
10/21/2006 10:22:46 AMkillah

good!!!
(If this comment was disrespectful, please report it.)

 
12/23/2006 4:42:38 PM

Thanqs for the tutorial
(If this comment was disrespectful, please report it.)

 
1/19/2007 2:14:42 AMChe3

your post is totally useful.. 2002 to 2007
5 year also have ppl learn about it.... very thank you i also a beginer. nice work!!!!!
(If this comment was disrespectful, please report it.)

 
2/10/2007 8:22:01 AMAzhar

An excellent attemp for the beginers
(If this comment was disrespectful, please report it.)

 
3/7/2007 12:50:02 AMBhuvan

gr8 work.
i m new to jsp.
u helped me a lot.
cheers.
(If this comment was disrespectful, please report it.)

 
3/7/2007 4:40:15 PMSybert Mawoyo

Brilliant stuff, just what I was looking for. Many thanks
(If this comment was disrespectful, please report it.)

 
3/16/2007 12:08:27 PMNorbert

Excellent work. I needed 5 minutes in order to set up the test program on my XP box with java 1.5.

Thanks a lot!
(If this comment was disrespectful, please report it.)

 
4/2/2007 5:43:23 AMzizipho

hi there Jeff Patterson
What an excellent way of explaining the code to connect to the database. I have learn a lot today cause I used to use the code to connect to the DB but i never Understood every line what it meant. All the best and keep your good work.
(If this comment was disrespectful, please report it.)

 
4/12/2007 2:34:43 AMalex

i'm a newbie to this, but it works. can u mke another one using applet or swing or using a GUI. Thanks!
(If this comment was disrespectful, please report it.)

 
4/14/2007 1:23:21 AMSonali Yadav

Great article. Very well explained, simple yet comprehensive.
Could you do one on accessing Oracle databases using JDBC (using and without using DSN)?! Very confusing!!
(If this comment was disrespectful, please report it.)

 
4/19/2007 4:55:17 AMExcalady

it cool

(If this comment was disrespectful, please report it.)

 
4/28/2007 4:42:53 AMmakouvex

yo jeff thank a lot your explanation really helps me with understanding java database thingy. You explain better than our instructor, well at least you did... Lotfull of thanks!!! Rock on!
(If this comment was disrespectful, please report it.)

 
5/30/2007 9:19:23 AMAshok

I m very much new to java and just know nothing about JDBC. But it made me very confident to work on DB using Java. Thanks a lot to the Author !!!

(If this comment was disrespectful, please report it.)

 
6/28/2007 4:22:37 PMLee

Excellent explanation and it works. Thanks!
Any ideas on how to do the same to connect to an Open-Office.org BASE file?
(If this comment was disrespectful, please report it.)

 
7/19/2007 1:22:39 AMKhem Raj Bhattarai

it is very nice tutorial, it worked properly
(If this comment was disrespectful, please report it.)

 
7/30/2007 11:32:30 PMpogi

TNX JEFF
(If this comment was disrespectful, please report it.)

 
10/25/2007 6:28:59 AMSitty

I think whoever created the site and decided to share with the rest he/she has done a great job. the site was very helpful whatevert i was doing. Cheers!!
(If this comment was disrespectful, please report it.)

 
11/1/2007 2:40:19 PMalps

it was really helpful to understand the basic of Database. Thanks!
(If this comment was disrespectful, please report it.)

 
11/14/2007 12:44:07 PMFrances

It an excellent artical. It gives me an entry to talk to MS Access using java. Thanks.
(If this comment was disrespectful, please report it.)

 
1/3/2008 4:57:14 AMAshwin

Thank you mate!!!
(If this comment was disrespectful, please report it.)

 
1/12/2008 7:55:54 AMMajd

Hi,Plese
(If this comment was disrespectful, please report it.)

 
1/16/2008 8:27:20 PM

Code worked first time, out of the box. Thanks.
(If this comment was disrespectful, please report it.)

 
5/29/2008 6:08:34 AMMiguel Tubia

Hello Mr Patterson
What an awfully outdated comment (this)! isn't it ?
But I think your job deserves an acknowledgement.
Excellent article not only relative to those existent but by itself.
Very useful, indented and commented code, very interesting concepts, very good exposition.
BTW, it's not just still readable enough, despite PlanetSourceCode, but very readable.
All up to the legal terms :-)
Settled the above, and knowing that almost everything is almost everytime improved, perhaps it would have been a good point for the shake of the quality of the rest of the article, you had advised, first, the reader should be able to run a "hello world" class.
I won't have the luck of having a work mate like you.
Eight years after, but i wish you all the best.
(If this comment was disrespectful, please report it.)

 
7/30/2008 10:33:31 AMkhan

Hy man i am new to java ....
I know what is database..
i have following question which already confused me
please give the answer if you have your valuable time free.
1:-I know java basic s but idont know how to connect data with java language.
2:-It is essential that user have Msacess and other database software installed to run the software.
(If this comment was disrespectful, please report it.)

 
8/26/2008 12:40:08 PMNeha

Well done...........keep it up......
(If this comment was disrespectful, please report it.)

 
9/20/2008 7:02:33 AMlavkesh

Excellent Article....
Help me very much....
Keep posting such articles....
(If this comment was disrespectful, please report it.)

 
9/20/2008 7:04:47 AMlavkesh

Excellent Article.....
Help very much.....
(If this comment was disrespectful, please report it.)

 
5/4/2009 6:16:33 AMShazz

Awesome! I spent a lot of search time a few years ago and never came across this article. Just found it now, tried both methods and they work beautifully!
(If this comment was disrespectful, please report it.)

 
6/16/2009 2:04:51 PMnikhil

thanks for everything i needed this
(If this comment was disrespectful, please report it.)

 
8/1/2009 5:08:16 AMluckson.karikoga

really i couldn't have figure out myself. thax
(If this comment was disrespectful, please report it.)

 
10/22/2009 11:41:43 AMJoselal

Excellent tutorial !
Why don't you try writing a book?
(If this comment was disrespectful, please report it.)

 
10/28/2009 6:49:04 PMEvariste UWIZEYIMANA

Very usefull website! Thanks.
(If this comment was disrespectful, please report it.)

 
11/10/2009 5:15:56 AMKoen

Very nice article well explained. Had soem difficulty because I am using acces 2007. I solved my problems by updating the driver http://www.microsoft.com/downloads/thankyou.aspx?familyId=7554f536-8c28-4598-9b72-ef94e0 38c891&displayLang=en
and
the code referring to the driver"Driver={Microsoft Access Driver (*.mdb, *.accdb)"
(If this comment was disrespectful, please report it.)

 
4/16/2010 12:17:20 AMambreesh

The code was excellent to understand and implement for a new candidate who is working on JAVA for the first time
(If this comment was disrespectful, please report it.)

 
10/3/2010 9:48:13 AMNagaraj.S

Thank you sir,
Its very usefull for a newer like me...

(If this comment was disrespectful, please report it.)

 
3/3/2011 5:39:01 AMunusualAtitude

aging like good wine.
helped greatly
(If this comment was disrespectful, please report it.)

 
3/18/2011 4:41:27 AMThomas Wiedmann

Good description, but one blemish:
Some lines in the description are truncated in the browser (I tried it with Internet Explorer 8 as well as with Firefox 3).
Please could you reformat your HTML source text of the description, that it may be displayed correctly.

Thomas
(If this comment was disrespectful, please report it.)

 
5/29/2011 10:15:38 AMsage shah

Excellent !
and good way to teach step by step . this was my 2nd week for searching jdbc tutorial so finally i got this excellent tutorial.
Thanks
(If this comment was disrespectful, please report it.)

 
6/7/2011 1:27:12 AMAma

Excellent!!!!!
good way to teach step by step.
(If this comment was disrespectful, please report it.)

 
6/7/2011 1:30:12 AMAma

Excellent !!! It's good way of teaching step by step. Thanks.
(If this comment was disrespectful, please report it.)

 
6/21/2011 9:25:56 PMXYZ Consult

Please write a book. There are zillions of useless Java programming books out there, and so much hype. Only a few good ones. Yours would be top of my list.
(If this comment was disrespectful, please report it.)

 
9/22/2012 12:30:58 PMAmr

it is very simple.thank you
(If this comment was disrespectful, please report it.)

 
12/24/2012 9:51:09 AMJava

guys, class.forName is loading the driver to memory. also see if my jdbc tutorial helps
(If this comment was disrespectful, please report it.)

 
10/26/2013 12:38:57 PMchandralekha

I have microsoft office 2007 in my system but i cant find Microsoft Access Driver(*.mdb) or Microsoft Access Driver(*.mdb,*.accdb) pls help me how to download the drivers
(If this comment was disrespectful, please report it.)

 
11/19/2013 2:24:04 AMOnuwa Nnachi Isaac

Excellent tutorial brother... It works for me. Thank you
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.