Feeds

Escape from Access DB

The relational database that polarises programmers

Build a business case: developing custom apps

Do you like Microsoft Access? Well, thanks to an intuitive and powerful user interface and being bundled as part of Office, it has established a niche for itself in many organisations, sometimes unofficially.

The combination of recordable macros, wizard-generated reports, query-by-example and, crucially, the provision of program support using Visual Basic for Applications has made MS Access the tool of choice for legions of developers of varying levels of ability and technical proficiency.

Not only has this led to a proliferation of applications, it has also produced a proliferation of "rescue" projects as those applications have grown in scope, size and complexity. For enterprise developers using Java this data need not remain out of reach thanks to the wonders of JDBC-ODBC bridges.

ODBC (Open Database Connectivity) is an API that provides programmatic access to data using SQL. ODBC adopts a layered approach; an application written to ODBC can talk to any underlying data source that has an ODBC driver. In the case of Access, ODBC support has been there almost from the beginning. In the Java world, a similar architecture exists called JDBC (Java Database Connectivity) in which Java applications written to the API can talk to any data source that has a JDBC driver. Put these together in the form of a JDBC-ODBC bridge and you have a platform-independent mechanism for Java to talk to any DBMS that has JDBC or ODBC drivers.

In the case of Access, the first step to opening the database to ODBC is to create an ODBC Data Source Name for your database. The simplest method is to load the Data Source Administrator from within the Windows Administrator tools (via the Control Panel if you're not running a server version of Windows).

A User DSN creates and registers a DSN for a single user, a System DSN is accessible to all users and a File DSN stores the DSN information to a file which can be shared among different users. In each case the DSN associates a user-defined label with a database driver (Microsoft Access Driver *.mdb) and a specific instance of the database. Once you create the DSN, any program that uses the ODBC API to talk to the underlying database can use it.

In the case of Java, the normal method for talking to a database is to use JDBC. First, the database driver is registered and then a specific database URL is used to establish a connection which can be used to create the statement object. Once this has been done, it's possible to interact with the database using SQL, stored procedures etc.

In the following code snippet, we are going to connect to an Access database which has a DSN of MyAccessDB using Sun's JDBC-ODBC bridge driver

import java.sql.*;

public class AccessDAO {

        private Connection con;
        private Statement st;
private static final String url="jdbc:odbc:MyAccessDB"; 
        private static final String className="sun.jdbc.odbc.JdbcOdbcDriver"; 
        private static final String user=""; 
        private static final String pass=""; 

        AccessDAO()throws Exception { 


        Class.forName(className); 
                con = DriverManager.getConnection(url, user, pass); 
                st = con.createStatement();  

                //do whatever database processing is required 
        } 
} 

It's not just the user data that can be queried using the JDBC-ODBC bridge, the database metadata can also be accessed. The following program will report all of the SQL data types supported by the version of Access used by the database contained in the MyAccessDB DSN:

import java.sql.*; 

public class AccessDAO { 

        private Connection con; 
        private Statement st; 
        private static final String url="jdbc:odbc:MyAccessDB"; 
        private static final String className="sun.jdbc.odbc.JdbcOdbcDriver"; 
        private static final String user=""; 
        private static final String pass=""; 
                
        public AccessDAO()throws Exception { 

                Class.forName(className); 
                con = DriverManager.getConnection(url, user, pass); 
                DatabaseMetaData dbMetaData  = con.getMetaData(); 
                ResultSet dbTypes = dbMetaData.getTypeInfo(); 
                String typeName; 
                System.out.println("Supported Database Types:"); 
                while (dbTypes.next()){ 
                        typeName = dbTypes.getString("TYPE_NAME"); 
                        System.out.println(typeName); 
                } 
                
        } 
        
        public static void main(String[] args) throws Exception{ 
                new AccessDAO(); 
        } 
        
} 

Of course, it's not just Access that supports ODBC. There are many other databases which support the ODBC API, and these aren't limited to the Windows operating system either. Furthermore, ODBC can be used with Excel and represents an alternative to the use of Apache POI or JExcelAPI as methods for interfacing Java and Excel. ®

Bridging the IT gap between rising business demands and ageing tools

More from The Register

next story
NO MORE ALL CAPS and other pleasures of Visual Studio 14
Unpicking a packed preview that breaks down ASP.NET
Cheer up, Nokia fans. It can start making mobes again in 18 months
The real winner of the Nokia sale is *drumroll* ... Nokia
Mozilla fixes CRITICAL security holes in Firefox, urges v31 upgrade
Misc memory hazards 'could be exploited' - and guess what, one's a Javascript vuln
Put down that Oracle database patch: It could cost $23,000 per CPU
On-by-default INMEMORY tech a boon for developers ... as long as they can afford it
Google shows off new Chrome OS look
Athena springs full-grown from Chromium project's head
Apple: We'll unleash OS X Yosemite beta on the MASSES on 24 July
Starting today, regular fanbois will be guinea pigs, it tells Reg
HIDDEN packet sniffer spy tech in MILLIONS of iPhones, iPads – expert
Don't panic though – Apple's backdoor is not wide open to all, guru tells us
prev story

Whitepapers

Designing a Defense for Mobile Applications
Learn about the various considerations for defending mobile applications - from the application architecture itself to the myriad testing technologies.
Implementing global e-invoicing with guaranteed legal certainty
Explaining the role local tax compliance plays in successful supply chain management and e-business and how leading global brands are addressing this.
Top 8 considerations to enable and simplify mobility
In this whitepaper learn how to successfully add mobile capabilities simply and cost effectively.
Seven Steps to Software Security
Seven practical steps you can begin to take today to secure your applications and prevent the damages a successful cyber-attack can cause.
Boost IT visibility and business value
How building a great service catalog relieves pressure points and demonstrates the value of IT service management.