Accessing MS Access / MS SQL from NetBeans

by Gustavo Soto

In this tutotial, you use NetBeans IDE with JDBC - ODBC Bridge to connect to MS Access / MS SQL Server database.

For more information about working with NetBeans IDE, see the Support and Docs page on the NetBeans web site.

Tutorial Requirements

Prerequisites

Before you use this tutorial, you must have the NetBeans 6.0 with the Visual Web Package, MS Access / MS SQL Server, also you need the basic knowledge of MS Access / MS SQL Server and ODBC, in order to create the database.

Be aware, that SUN's JDBC - ODBC Bridge has a lot of bugs, ant that it would be not included in Java 7.0.Stay tuned: jdbc-odbc-enhanced

This tutorial assumes that you have some basic knowledge of, or programming experience with, the following technologies.

• MS Access
• MS SQL
• JDBC
• ODBC

In this tutorial, you build a basic application that connects to a MS Access database.

1. Create database.

2. Create the NetBeans Project.

3. Connect the application to the databse.

Creating the Database

You being creating the database in MS Access or MS SQL.

1. Create a new Database named DB_AMS, then create a new Table named T_AMS with the following structure:

2. Add some data to the table.

Now you have to create the ODBC data source as follows:

1. Create a System DSN with the name ODBC_AMS, and configure it, with the information for your MS Access or MS SQL data provider.

2. Test the connection with the ODBC data source.

Creating the NetBeans project

1. Create a new Web Application Project named AccessingMSDB that uses the Apache Tomcat Application Server and the Visual Web JavaServer Faces Framework.

2. Add new Java Class named DbAccess, and add the next code, below to the class declaration:

Figure 1: Creating the connection.

Note (Ulf Zibis):

If you use JDK 6.0 "Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");" is superfluous. The JdbcOdbcDriver is registered automatically!

3. Now add the next code right below the last code:

Figure 2: Getting the data.

Note (Ulf Zibis):

"statement.execute(query)" + "statement.getResultSet()" is superfluous. Just write:

resultSet = statement.executeQuery(query);

... But to get the number of rows you don't need to browse to the last row by resultSet.last(), just write:

options = new Option[((JdbcOdbcResultSet)resultSet).getRowCount()];

4. Press Ctrl+Shift+I in order to Fix Imports.

5. In the Page1 Desing view add Listbox component from the Palette.

6. Now go to the Java View and add the next code in the init class method:

Figure 3: Show the data.

7. Press Ctrl+Shift+I in order to Fix Imports.

8. Test the project by pressing F6.

Summary

• You use the JDBC-ODBC Bridge Driver to connect to a MS Access / MS SQL database.
• You can bind most properties to data.

Next Steps

• Create this tutorial for a Java Desktop Application.
• Create this tutorial for the other Operating Systems spported by the NetBeans IDE.

To send comments and suggestions, obtain support, and stay informed of the latest changes to the NetBeans IDE J2EE development features, join the nbusers@netbeans.org mailing list.

Attachments

addConnection.png		62427 bytes
db.png		8750 bytes
getName.png		75442 bytes
init.png		50531 bytes