We help IT Professionals succeed at work.

Read data from access database...

Mike Eghtebas
on
Medium Priority
545 Views
Last Modified: 2012-08-14
Sample code exists to read data from an access database (books.mdb attached) in Windows 7 environment.

This code has some other information in it.

Question: How can I simplify the code just to read and display the content of author table in the database?

Also, is it necessary to create odbc connection in the computer I will be testing it? If so how to create such a connection?
 
Thank you.


import java.awt.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;

public class DisplayAuthors extends JFrame {
   
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
   static final String DATABASE_URL = "jdbc:odbc:books";
   
   // declare Connection and Statement for accessing 
   // and querying database
   private Connection connection;
   private Statement statement;
   
   // constructor connects to database, queries database, processes 
   // results and displays results in window
   public DisplayAuthors() 
   {
      super( "Authors Table of Books Database" );
      
      // connect to database books and query database
      try {
         
         // specify location of database on filesystem
         //System.setProperty( "db2j.system.home", "C:/Cloudscape_5.0" );
         
         // load database driver class
         Class.forName( JDBC_DRIVER );

         // establish connection to database
         connection = DriverManager.getConnection( DATABASE_URL );

         // create Statement for querying database
         statement = connection.createStatement();
         
         // query database
         ResultSet resultSet = 
            statement.executeQuery( "SELECT firstName, lastName, gpa FROM authors" );
         
         // process query results
         StringBuffer results = new StringBuffer();
         ResultSetMetaData metaData = resultSet.getMetaData();
         int numberOfColumns = metaData.getColumnCount();
         
         for ( int i = 1; i <= numberOfColumns; i++ )
            results.append( metaData.getColumnName( i ) + "\t" );
         
         results.append( "\n" );
         
         while ( resultSet.next() ) {
            
            for ( int i = 1; i <= numberOfColumns; i++ )
               results.append( resultSet.getObject( i ) + "\t" );
            
            results.append( "\n" );
         }

         // set up GUI and display window
         JTextArea textArea = new JTextArea( results.toString() );
         Container container = getContentPane();

         container.add( new JScrollPane( textArea ) );
         
         setSize( 300, 100 );  // set window size
         setVisible( true );   // display window

      }  // end try
      
      // detect problems interacting with the database
      catch ( SQLException sqlException ) {
         JOptionPane.showMessageDialog( null, sqlException.getMessage(), 
            "Database Error", JOptionPane.ERROR_MESSAGE );
         
         System.exit( 1 );
      }
      
      // detect problems loading database driver
      catch ( ClassNotFoundException classNotFound ) {
         JOptionPane.showMessageDialog( null, classNotFound.getMessage(), 
            "Driver Not Found", JOptionPane.ERROR_MESSAGE );            

         System.exit( 1 );
      }
      
      // ensure statement and connection are closed properly
      finally {
         
         try {
            statement.close();
            connection.close();            
         }
         
         // handle exceptions closing statement and connection
         catch ( SQLException sqlException ) {
            JOptionPane.showMessageDialog( null, 
               sqlException.getMessage(), "Database Error", 
               JOptionPane.ERROR_MESSAGE );
         
            System.exit( 1 );
         }
      }

   }  // end DisplayAuthors constructor
   
   // launch the application
   public static void main( String args[] )
   {
      DisplayAuthors window = new DisplayAuthors();      
      window.setDefaultCloseOperation( JFrame.EXIT_ON_CLOSE );
   }

}  // end class DisplayAuthors

Open in new window

books.mdb
Comment
Watch Question

Awarded 2011
Awarded 2011

Commented:


What other information is there ?

It is just reading and printing the results to JTextArea
Awarded 2011
Awarded 2011

Commented:
I don't think you necessarily need to craete ODBC data source
You may use sucvh connection from
http://www.selikoff.net/2011/07/26/connecting-to-ms-access-file-via-jdbc-in-64-bit-java/


Do you have 32-bit or 64-bit system?

It may be difficult with these new systems as you need to have JVM and driver matching
in a sense of number of bits, etc.

final String fileName = "c:/myDataBase.mdb";
Connection con = null;
try {
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+fileName;
	con = DriverManager.getConnection(url,"","");
} catch (Exception e) {
	// Handle exceptions
	...
} finally {
	try { if(con!=null) {con.close();} } catch (Exception e) {}
}

Open in new window

Mike EghtebasDatabase and Application Developer

Author

Commented:
64-bit
Awarded 2011
Awarded 2011

Commented:
If you have ODBC control and and can find MS Access  driver on your computer (which is not always the case on these Windoes 7  systems)
then you neet to go to Control Panel - Administrative Tools - dataSources - System DSN tab - Add - Microsoft Avccess Driver -
then you give name to data source abnd browse to mdb file - this is much simper if this works on your machine
Awarded 2011
Awarded 2011

Commented:
Anyway - try if you have Microsoft Access Driver (*.mdb) in your Control Panel ?
Mike EghtebasDatabase and Application Developer

Author

Commented:
Could you give me the class I can drop into netbeans and run? Hopfuly, this code will find the database in question and query its table.
Awarded 2011
Awarded 2011

Commented:
No, your code requires to set up ODBC Data Source on your computer
Mike EghtebasDatabase and Application Developer

Author

Commented:
Ok, I will go through the necessary step to prepare for the code.
Awarded 2011
Awarded 2011

Commented:
Yes, check if you can do what I wrote in 37086666
Unfortunately I think they now don't install these drivers by default, so you'll need to
download and install it. But check, I'll try to find longish trails which I had with someone about it in the meantime.
Awarded 2011
Awarded 2011

Commented:
If you don't have the driver, then check if you have 32-bit or 64-bit JDK which you are going to use
to run your java.
I think it is essential that the driver should be with the same bits.
So, you'll need to download and install driver form here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255

and you need to select the driver corresponding to your JDK

AccessDatabaseEngine_x64.exe - this would be 64-bit driver

AccessDatabaseEngine.exe - this is 32-bit

Then when your JDK corresponds to the driver it may work
say with this way:

String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+fileName;

This is a very painful story, as it is easy to make mistakke and they often not match
as a result

I maanged to do it with couple of people, but it was never easy, and I don't have 64-bit machine myslef to test

You can look at this case, where we eventaully succedded:
http://www.experts-exchange.com/Programming/Languages/Java/Q_27238293.html


Mike EghtebasDatabase and Application Developer

Author

Commented:
I have to yet read last few posts. Meanwhile I was adding a question in Access TA for some feedback. I see that you have some additional information for me to follow through. They may just give me the same information. FYI the link is:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27432667.html 
Mike EghtebasDatabase and Application Developer

Author

Commented:
re:> Yes, check if you can do what I wrote in 37086666

As you guessed it, it didn't work for me. This is why I posted the question in Access TA, see the above link.

...
Awarded 2011
Awarded 2011

Commented:
Yes, I think they now do not install office DB drivers by default.
You need to install it form the link i posted above.

So which is your java?

I'm thinking that that  _x64  distribution should jhave both drivers, but I'm not sure
Awarded 2011
Awarded 2011

Commented:
So, if you are using 64-bit java then you definitley eed to install _x64 drivers
Mike EghtebasDatabase and Application Developer

Author

Commented:
re:> So which is your java?

I do I find this. It should be 64 because I have installed the latest of every thing in less than a year.
Awarded 2011
Awarded 2011

Commented:
when you go to C:\Program Files\Java
whatis the name of the folder there?
Mike EghtebasDatabase and Application Developer

Author

Commented:
jdk1.6.0_26      and   jre6
Awarded 2011
Awarded 2011

Commented:
Yes, I guess it is 64-bit, because the latest 32-bit java unless you change the default installs to some filedr which is called x86

try
java -version
Awarded 2011
Awarded 2011

Commented:

go t this page in browssr:
http://www.java.com/en/download/testjava.jsp
and in the middel of the page click link

test the currently installed version of Java

It will show you in the last line
Architecture: either x86 or xmd64


Mike EghtebasDatabase and Application Developer

Author

Commented:
Version 6 Update 29

Is what it shows.
Awarded 2011
Awarded 2011

Commented:

No below that. On the web page like that, what it shows for me:

Your Java configuration is as follows:
 Vendor: Sun Microsystems Inc.
 Version: Java SE 6 Update 19
Operating System: Windows XP 5.1
Architecture: x86
Awarded 2011
Awarded 2011

Commented:
First when you go to
http://www.java.com/en/download/testjava.jsp
it shows like that in the middle of the page (with red coelr etc)

Update Java by clicking the button below:

Free Java Download

Remove old and unsupported versions of Java to maintain your system security.

Click here for instructions.



Skip installation of the current version and (test the currently installed version of Java) <--- this is the link - click on that

and you;ll see
new rectangle in the middel ofr thr page
and inside this rectangle you see:


Your Java configuration is as follows:
 Vendor: Sun Microsystems Inc.
 Version: Java SE 6 Update 19
Operating System: Windows XP 5.1
Architecture: x86




Mike EghtebasDatabase and Application Developer

Author

Commented:
re:> test the currently installed version of Java

This is what I clicked on and there was a progress bar which ran about 15  seconds but (I guess because of some plug-in issues) it just refreshed the page without showing the result in the format you have above.

brb
Awarded 2011
Awarded 2011

Commented:
Anyway, I gues let's go to this page
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255

and install the one with _x64 in the name
Mike EghtebasDatabase and Application Developer

Author

Commented:
but per: 37086821
When I am downloading java (vis red button "Free Java Download")

It says it going to download:


32-bit Java for Windows Internet Explorer
 Recommended Version 6 Update 29 (filesize: ~ 11 MB)

Shall I proceed?
Awarded 2011
Awarded 2011

Commented:
No, I don't think so, why would you need to download another java?

You need to dwonload driver form that microsoft site.
Mike EghtebasDatabase and Application Developer

Author

Commented:
Also, if we go ahead and install both 64 and 32 version of the drive and install them then, we might be able run our code using 32 first and when necessary, using try-catch routine, switch is to 64 version in runt-time. Is this possible?

If so, could start with 32 for now and late could add 64. After we ended up with both then we can use try-catch remedy.
Awarded 2011
Awarded 2011

Commented:
Yes, I think you can have both Java 32-bit and 64-bit installed simulatneously
And denepdening on which /bin/java.exe will be te fisrt in your PATH it will run eiether one ofr aniother

You don;t need any try-catch - we need onec to establsh correct driver
and have it correspond to ocrrect jdk and use it - you don;t need any try-catch for it
 
Awarded 2011
Awarded 2011

Commented:
I mean you'll have try-catch anyway - but you need to match JDK to driver
without any try -catch
Mike EghtebasDatabase and Application Developer

Author

Commented:
re:>  why would you need to download another java?

I was following your instruction from 37086821. I guess you were just showing me the screen. Do harm is done, I have not downloaded java yet.

I was under impression I could just download the latest and get rid of old version(s). This way I could have the latest.

Now, shall I download 64 version although I am not sure if I may need to have 32?
Mike EghtebasDatabase and Application Developer

Author

Commented:
sorry I didn't see your posts...

I will do:

Anyway, I gues let's go to this page
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255

and install the one with _x64 in the name
Awarded 2011
Awarded 2011

Commented:
37086821 was not an uinstruction - Iwas just explaining how the page looks like.
But on taht page I wanted you not to click donwload java but to click test currently installed version

But it didn;t work for you for some reason
Awarded 2011
Awarded 2011

Commented:
Yes, lt's do it
If it does not match your JDK then well think again
Awarded 2011
Awarded 2011

Commented:
But driver is indeed waht you need
Mike EghtebasDatabase and Application Developer

Author

Commented:
I can not install 64 because:

Although I have Office 2010 purchased and installed on my computer but, it originally came with Office2007 trial version (I never used which is 32 I guess).

So, I need to uninstall 2007 first. I hope it will not mess up my computer. With Microsoft products you never know. Also, I am afraid to ask me to re-install office 2010 again.

Shall I go ahead to uninstall office 2007?
Awarded 2011
Awarded 2011

Commented:
No you should not uninstall office.
This is only about drivers.
And I think the drivers for 64-bit they still should recognize previous version of office
Mike EghtebasDatabase and Application Developer

Author

Commented:
The link you gave me to download 64 refused to do this (saying you have... see the image)
64Or32.png
Awarded 2011
Awarded 2011

Commented:
Then try to install the one without _x64 - would that install?  
Mike EghtebasDatabase and Application Developer

Author

Commented:
The other one is

AccessDatabaseEngine.exe

I install this now. I will be fairly easy to remove this one later I guess.
Awarded 2011
Awarded 2011

Commented:
Yes, let's see.
In the worrst case you can then install 32-bit java also.

I hope that mdb file which you have is compatible.
If this .mdb requires only 64-bit driver then it will be a problem.
Mike EghtebasDatabase and Application Developer

Author

Commented:
No the database engine shows in the control panel but not under System DSN
DatabaseEngine.png
Mike EghtebasDatabase and Application Developer

Author

Commented:
Now the database engine shows in the control panel but not under System DSN
Awarded 2011
Awarded 2011

Commented:
it shoudl not be under System DSN.

You should go to this System DSN tab and then clickk Add
Then it should show list of drivers - in this list you should see MicorsosoftAccessDriver(*mdb)
Mike EghtebasDatabase and Application Developer

Author

Commented:
sorry, after clicking on add (under System DSN) I get just those two previous SQL drives. Nothing about what we just installed...

FYI, I did restart the computer.
ODBC-Access.png
Mike EghtebasDatabase and Application Developer

Author

Commented:
I can write T-SQL to generate the database in SQL-Server.
Awarded 2011
Awarded 2011

Commented:
well it is probably better than Access

 you can still try to run the code in this variant:

try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+fileName;
      con = DriverManager.getConnection(url,"","");
} catch (Exception e) {


another question - so you have now the control in control panel - what
happens when you click on it ?

Awarded 2011
Awarded 2011
Commented:

That is waht they actually write in the instructions on that pge from eher you downloaded:

3.If you are application developer using ODBC to connect to Microsoft Office Access data, set the Connection String to “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file”

so try this variant:

try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=full_path_to_tour_mdb_file";
      con = DriverManager.getConnection(url,"","");
} catch (Exception e) {



Mike EghtebasDatabase and Application Developer

Author

Commented:
re:> you can still try to run the code in this variant:

Do you mean in attempt to read from access? What will be complete code to use in netbeans?


re:> so you have now the control in control panel - what

A click does nothing but dbl-click tries to uninstall it.
Awarded 2011
Awarded 2011

Commented:
the code you posted in your question, just  replace

static final String DATABASE_URL = "jdbc:odbc:books";

with

String DATABASE_URL  = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=full_path_to_tour_mdb_file";

Mike EghtebasDatabase and Application Developer

Author

Commented:
didn't work.
msgUntitled.png
Mike EghtebasDatabase and Application Developer

Author

Commented:
Thank you
Awarded 2011
Awarded 2011

Commented:
Strange, why does not it work?
Awarded 2011
Awarded 2011

Commented:

go to
Start - Run - type
C:\Windows\SysWOW64\odbcad32.exe

and check if in this control you'll see Microsoft Accaess Driver (*mdb)

I guess the control you have in Control panel is 64-bit which is started by C:\Windows\SysWOW64\odbcad64.exe
and we installed 32-bit driver.

Check if it appears in that control
Awarded 2011
Awarded 2011

Commented:

Aslo try this format Microsoft Access Driver (*.mdb)}; instaed of "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};
it just worked for me (didn't work with *accdb).

  String DATABASE_URL  = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\temp\\test\\db2.mdb";

Explore More ContentExplore courses, solutions, and other research materials related to this topic.