DSN-less Database Connection In JAVA...

23 April, 2009 | | 4 comments |

Share |
For connecting to MS-Access in JAVA VIA JDBC, there are two options:

1] Connect it VIA DSN(Data-Source-Name) configured from ODBC Sources in Administrative Tools in Control Panel

2] Connect it without a DSN(i.e. 'DSN-less' that's the way I've named it).

The second option is a far better option from feasibility, ease-of-use and professional approach point-of-view. Also there are a lot of advantages. I'll enumerate them as follows:

1] You don't need to manually configure the DSN settings everytime from ODBC Sources in CPL in case of DSN-less type of connection. Hence in this case you are saved from the cumbersome job of setting up the connection while deploying the project.
Add to it the pain of setting up the connection back again if you have logged onto a different user account in contrast to the one in which the connection was set-up. This happens in case if you have not checked the 'System Database' option and configured the rest of the settings from ODBC Sources. Also one thing to be remembered is that by default the 'System Database' option is not checked which means that the settings made are valid only for that current user account and not for all of the accounts existing on the OS.

2] Also in case you have not configured the DSN settings and you do not have MS Access installed then this will render you impotent. In case of DSN-less connection there is obviously no need to configure the DSN like I had explained it before.

3] And here's the best of , you do not need an MS-Access driver to connect to the DB in case of DSN-less connection since JDK has an inbuilt driver through which it operates.

Here is an example which shows DSN-less type of connection. I assume that you know the default architecture, classes, methods and syntax used for JDBC connection. So here it goes...

import java.sql.*;

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection c=DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+"DB.mdb","Uname","Pword");

// Here 'Uname' and 'Pword' are the username and password configured for the 'DB.mdb' database file. Note that the username and password is also to be configured from within the MS-Access DB.

Statement s=c.createStatement();

//Your DB operations like retrieve/insert/select operations would come here

s.close();
c.close();

//Closing the connection stream classes is extremely important
}

catch(Exception e4)
{
e4.printStackTrace();
}

------------------------------------------------------
Guru says Lolz...

4 comments:

Anonymous said...

wow!!!!! it was really helpful

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

Thanks Champ.... God bless u yaaar.

rex said...

your post was vry good and easy but it is generating an exception when i am doing it,my code an exception is given below plz provide a solution:

my code :

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

con=DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=bTicket.accdb;DriverID=22;READONLY=true","","");


EXCEPTION :

data source name not found and no default driver specified