?
Solved

How to specify the path of Access database in connection string

Posted on 2012-03-28
17
Medium Priority
?
813 Views
Last Modified: 2013-11-24
Hi:

We're writing a Java program to connect to an Access database.
So far, to connect to Access db, we only use C# or vb with Visual Studio to make the connection. Now, we need to use Java.

We got some examples from the web to get the steps of how to connect to an Access db.
Here is one:

//------------------
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb;";
Connection conn = DriverManager.getConnection(database, "", "");
//------------------

OR

//--------------------
try {
      Connection con = DriverManager.getConnection("jdbc:odbc:Inventory", "", "");
      Statement stmt = con.createStatement();
      stmt.execute("... statement string ... ");
      stmt.close();
      con.close();
    } catch (Exception e) {
      System.out.println(e);
    }
//-------------------

We've the following questions:

1) Where should the Access db be located? Is it in the same folder of the Java project?

2) If the db is not in the same folder of the Java project, how can we specify the path of the database so that the Java code knows where it is?

Thanks a lot for any help,

Somits
0
Comment
Question by:somits
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
17 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 37779409
Use it this way:

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

                    Class.forName( JDBC_DRIVER );

                                      // establish connection to database
                                     Connection connection = DriverManager.getConnection( DATABASE_URL );
Statement statement = connection.createStatement();

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
ID: 37779414
as you sse, you can have your .mdb file located wherever you want - just put the path correctly in the connection string
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37779427
this is the simplest way:
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" +  "C:\\MDBFileName.mdb";

if you want to use it this way:
DriverManager.getConnection("jdbc:odbc:Inventory", "", "");

then you need to go through Control Panel - Admin Tools - ODBC sources and
configure DataSource there with the Inventory mapping it tio some .mdb file

I thinke some newer systems do not support this second type, and it is less convenient , so unless you have old windows machine - I'd suggest that you use the first type with DBQ=
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Expert Comment

by:for_yan
ID: 37779433
newer windows machine come without pre-installed access driver; you need to install it form here:

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

Author Comment

by:somits
ID: 37779447
Hi for_yan:

Thanks a lot for your reply.

In the above code, we see "JDBC_DRIVER". How to get this?

Thanks,

Somits
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37779453
sorry this is the

  static final String JDBC_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
0
 

Author Comment

by:somits
ID: 37780195
Hi for_yan:

First, thanks a lot for all your comments.

I tried your recommendation: using
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" +  "C:\\MDBFileName.mdb";

but when I run the code, still the same error:

Here is my new code:

//--------------
try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            jdbcOdbcDriverStr = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ="
                                        + "F:\\JAVA\\APP_DATA\\ACCESSDB_8.accdb";
           
            Connection con = DriverManager.getConnection(jdbcOdbcDriverStr, "", "");
           
            con.close();
           
        } catch (Exception e) {
            System.out.println(e);
            return;
        }
//------------------------------------

Clearly, the error
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

occurred at this line of code:
Connection con = DriverManager.getConnection(jdbcOdbcDriverStr, "", "");

Maybe, I still miss something?

Thanks a lot for all your help,

Somits
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37780206
what operating system do you have ?

I think you don't have MS Access driver installed

Did you look at control panel ODBC as I wrote in one of these questions?
0
 

Author Comment

by:somits
ID: 37783071
Hi for_yan:

I checked Admin Tools --> ODBC Data Source and found the following tabs:

1) User DSN
2) System DSN
3) File DSN
4) Drivers
...

I looked into User DSN and found: MS Access Database
I looked into System DSN and it's empty
I looked into File DSN and a lot of folders but nothing named MS Access Database

I select "MS ACcess Database" in User DSN and click Add.
A window named "Create New Data Source" pops up and ask "Select a driver for which you want to set up a data source". They list

a) SQL Native CLient
b) SQL Server
c) SQL Server Native Client 10.0

I don't know which one to choose but I selected c): SQL Seerver Native Client 10.0 because its drive is the latest. Then click Finish.
Another wizard "Create a Data Source to SQL Server" pops up. It asks to enter:

a) Name: (I guess the name of the data source)
b) Description: ...
c) Which Sql Server do you want to connect to

For b), I can guess. However, for a) and c) I don't know how enter them.

For a) How to give a name to a data source? Can we it any name we want?

For c) How we can specify that we need a data source to an Access database, not a SQL server?

Thanks a lot for all your help.

Somits
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37783130
This tells me that you do not have MS access driver installed



a) SQL Native CLient
b) SQL Server
c) SQL Server Native Client 10.0


SQL clients are quite sdifferent things - they will not help you if you
want to acces MS Access files

This is beciaue you have newer machineas and you do not have MS Access driovers installed.
I would  install driver from this page:
http://www.microsoft.com/download/en/details.aspx?id=13255

do you have 32-bit machine?
0
 

Author Comment

by:somits
ID: 37783476
Hi for_yan:

I downloaded the software from the link.

First, I install the _64 version because the operating system is x64.
However, error occurred with "Office is 32 bits".

I downloaded the 32 bit version and installed. Everything is OK. We restarted the computer to be sure.

However, I checked using Admin Tools --> ODBC Data Source, I saw the same thing in "Select the driver ..."

a) SQL Native CLient
b) SQL Server
c) SQL Server Native Client 10.0

I also tested the same code. The same error occurred.

Would we still miss something?

Thanks a lot for all your help,

Somits
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37783565
Waht JRE are you using - it is 64bit or 32bit ?

It works exactly with this string for me; I did have to install that driver at some point, but really nothing else
I;m using .mdb file and this version
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=DBNname.mdb";
all the time in my code
and as DBName  I put the fuill path to my .mdb file

but I think I tested it and it wirked with  accdb files also
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37783578
I think they all sjhould be aligned -

java + driver should be either both 64 or both 32
0
 

Author Comment

by:somits
ID: 37783661
I'm using x64

I installed Java JDK 6 Update 31 x64.

So, I should uninstall the JDK and install 32 bits.

Is it correct?

Thanks,

Somits
0
 
LVL 47

Accepted Solution

by:
for_yan earned 2000 total points
ID: 37783687
You don't need to uninstall - jdk's can coexist happily

Just install 32 bit JDK and point to 32-bit when you execute this thing

I know it is all painful, but Microsoft created such a mess with it and there were a number of such questions and we eventually found the light  I think in all cases

I'm of course using the code i sent to you myslef all the time but I have simple 32-bit XP and  therefore it was all much easier or me
0
 

Author Comment

by:somits
ID: 37784087
Hi for_yan:

IT WORKS .............................

THANKS SO MUCH FOR YOUR KINDNESS TO HELP ME ON THIS.

REALLY APPRECIATED.

You're right. It must be matched the version x64 OR 32 bits for JRE & the driver.

Somits
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37784100
Great! I'm very happy that all suffering was not in vain.
0

Featured Post

Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses
Course of the Month9 days, 14 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question