Solved

Working with a databas... Access in this example

Posted on 2011-09-19
36
518 Views
Last Modified: 2012-05-12
In a test database, I have three tables (see the image below). I want a sample code to read and write some information to tblPerson (if not much work to all three tables). This would get me started doing more work on it.

I am using Access 2010.

Thank you
AccessTables.png
0
Comment
Question by:Mike Eghtebas
  • 19
  • 17
36 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 36564970
It would be siomthing like this say for Oracle:

           Class.forName ("oracle.jdbc.driver.OracleDriver");

             Connection conn =
               DriverManager.getConnection ("jdbc:oracle:thin:@server.com:1521:sid",
      "userr", "password");

        PreparedStatement pstmt = conn.prepareStatement("insert into tblPerson(personID, FirstName, LastName, DateOfBirth) values (?,?,?,?)");
          pstmt.setInt(1,25);
          pstmt.setString(2,"Peter");
          pstmt.setString(3,"Brown");
          pstmt.setDate(4, new java.sql.Date.valueOf("1950-01-01"));
          pstmt.execute();
          conn.close();
          
          
        
         

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
ID: 36564981
I of course inseretd only som eof the fields to make it shorter

This is the JDBC tutorial - ther arre of course many details to it:
http://download.oracle.com/javase/tutorial/jdbc/

small correction valueOf is actually a static method of java.sql.Date - no "new" necessary
 
           Class.forName ("oracle.jdbc.driver.OracleDriver");

             Connection conn =
               DriverManager.getConnection ("jdbc:oracle:thin:@server.com:1521:sid",
      "userr", "password");

        PreparedStatement pstmt = conn.prepareStatement("insert into tblPerson(personID, FirstName, LastName, DateOfBirth) values (?,?,?,?)");
          pstmt.setInt(1,25);
          pstmt.setString(2,"Peter");
          pstmt.setString(3,"Brown");
          pstmt.setDate(4, java.sql.Date.valueOf("1950-01-01"));
          pstmt.execute();
          conn.close();
          
          
        
         

Open in new window


You also need to download the JDBC driver specific to your database
and have it in the classpath
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36565069
JDBC for Access is already present in java. I have been reading about it how it uses ODBC in java environment which in turn it hand shakes with ODBC in Windows environment to do the job.

The code for Oracle is not much help. I guess I need to read more to see if I can doe the same with Access.

Mike
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36565080
This is how you do it with Access (I doidn understand I tohought initially - it is access to databese in your question)

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

Connection con1 = DriverManager.getConnection("jdbc:odbc:DaatSOurceName","","");

PreparedStatment pstmt = con1.prepareStatement();

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
ID: 36565091
So you should fisrt go to control panel - AdmTools-Odbc sources and
setup DataSourceName which is used in DriverManager.getConnection("jdbc:odbc:DaatSOurceName","","")
and connect it to your .mdb file

If you have  32bit Windows it should be straightforward; in the latest systems
people had many problems as you need that driver and java all should match
and usually they struggle with mixmatch of these 32/64 bits at the point when they need to connect to Access or Excel
 

0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36565100
could you put in a class where I can compile and run it. Possible inserting some hard wired values or some values from JOption or c prompt.

Thaks
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36565110
FYI, I am working with Windows 7 professional.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36565116
I will go bed now and check back tomorrow, thx
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36565118
Do you have this ODBC sources in controlpanel?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36565183
This worked for me without problems, but I have XP

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class InsertIntoAccess {
    public static void main(String[] args) {
            try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection conn = DriverManager.getConnection("jdbc:odbc:PersonDB","","");


                PreparedStatement pstmt = conn.prepareStatement("insert into Person(personID, Name, DateOfBirth) values (?,?,?)");
                          pstmt.setInt(1,25);
                          pstmt.setString(2,"Brown");
                         pstmt.setDate(3, java.sql.Date.valueOf("1950-01-01"));
                          pstmt.execute();
                          conn.close();



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

    }

}

Open in new window

accessDB.PNG
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36566902
I tried in in netbeans. There is an error:

run:
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
      at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
      at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3073)
      at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
      at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
      at java.sql.DriverManager.getConnection(DriverManager.java:582)
      at java.sql.DriverManager.getConnection(DriverManager.java:185)
      at TestAccess.main(TestAccess.java:10)
BUILD SUCCESSFUL (total time: 0 seconds)

Now I am checking to see whether "ODBC sources in controlpanel?" you wrote about earlier.

Mike
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36566962
I am still trying to find out about ODBC in control panel. Also see: http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Windows/Windows_7/Q_27317445.html

Mike
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36566978
Also have in mind that I am using Access 2010 not 2003 or earlier version.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36567082
I notice the need to change your code a bit. After the change I got:

run:
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
      at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
      at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3073)
      at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
      at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
      at java.sql.DriverManager.getConnection(DriverManager.java:582)
      at java.sql.DriverManager.getConnection(DriverManager.java:185)
      at TestAccess.main(TestAccess.java:10)
BUILD SUCCESSFUL (total time: 0 seconds)

========
The changes included:  AddressBookAccess2010
at: DriverManager.getConnection("jdbc:odbc:AddressBookAccess2010","","");

also, some changes at: insert into tblPerson(LastName, DateOfBirth) values (?,?)");

table name and also removing PersonID (a PK from the script).
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TestAccess {
    public static void main(String[] args) {
            try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection conn = DriverManager.getConnection("jdbc:odbc:AddressBookAccess2010","","");


                PreparedStatement pstmt = conn.prepareStatement("insert into tblPerson(LastName, DateOfBirth) values (?,?)");
                          pstmt.setString(2,"Brown");
                         pstmt.setDate(3, java.sql.Date.valueOf("1950-01-01"));
                          pstmt.execute();
                          conn.close();



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

    }
}

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
ID: 36567527
Of course that's what I was talking about - no matter how you call your datasource - but you need to set ity up - so do you have this ODC control in control panel?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36570892
I verified, ODBC exist in the control panel as shown in the image below: Why then the above code is not working for me?

I am attaching the database itself just in case it could help.

Thank you,

Mike
ControlPanelODBC.png
AddressBookAccess2010.zip
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36570903
Show me your System DSN tab
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36570932
Here you are. Team is the sub-folder where the database is.
DSN-Tabs.png
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36570950
I guess I need to use User DSN tab to create one for Access.

I am not exactly sure how to do this though.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36570958
Try the following - in System DSN tab click Add,
Select MicrosofT Access Driver (*.mdb), then
type the datasource name - should be the same as in your code: AddressBookAccess2010
description - not important bbut type something
then click Select and browse to your .mdb file
In this way you'll configure ddata source
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36570964
What is this File DSN, and why you have that .zip there  i don't know - I have nothing in that tab
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36570971
The problem will be if when you click Add you'll not see
MicrosofT Access Driver (*.mdb) driver in the list
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36570974
re:> Select MicrosofT Access Driver (*.mdb), then

This option doesn't exists there. I guess I have to add it via User DSN tab to create one for Access. I am not exactly sure how to do this though.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36570983
I wrote how to do it above but in Win 7 they don't have it installed or something.
No, it is not through User DSN.
You need to install it - let me try to dig some other question - it was already a pain
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36570996
Is your Windows 32 bit ?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36571013
re:> The problem will be if when you click Add you'll not see MicrosofT Access Driver (*.mdb) driver in the list

So, then shall I click on Finish button without selecting Access from the list of data sources in "Create New Data Source"?

I tried to add it from User DSN but connectivity test failed. See the image below.


re:> What is this File DSN, and why you have that .zip there  i don't know - I have nothing in that tab

In this tab, I though I need to select the sub folder where my database is. So, I browsed and selected that sub folder. the zip file happend to be in that subfolder also.

Do I need to reverse that process? If yes, how?

TestFailed.png
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36571022
Undo whatever you did in the File DSN.
You can not do anything with create data source until you have Microsoft Acces Driver.
I think this is the place from where you need to download it:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255

And reply if you have 32 bit or 64 bit system.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36571030
it is 64 bit system.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36571033
Well, that's even worse

The JDK which you are using  - is it 64 bit or 32 bit?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36571058
And also do you have Access (MS Office) 32 bit or 64 bit ?

It is very often that they install 32 bit Office on 64 bit machines.

If so - I think you need to intall AccessDatabaseEngine.exe
from this page
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255

If you have everything - JDK and  Office - all 64bit, then you need
to install
AccessDatabaseEngine_x64.exe





0
 
LVL 47

Accepted Solution

by:
for_yan earned 500 total points
ID: 36571097



Try also another version of code:
DBQ (see below) should give the  path to your mdb file:

DBQ=C:\\temp\\test\\MyDB.mdb

With this code it should work without going to ODBC control and configuring datasource
though I didn't try this version myself.

Nevertheless, I think you don't have a Microsoft Accaess driver, and
no matter what way yyou use, you need
to install driver.
So go to that microsoft sirte and download it.

The main issue with this 64 bit is taht it is difficult to have all components - JDK, Office, Driver - compatible


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TestAccess {
    public static void main(String[] args) {
            try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb;";

Connection conn = DriverManager.getConnection(database,"","");


                PreparedStatement pstmt = conn.prepareStatement("insert into tblPerson(LastName, DateOfBirth) values (?,?)");
                          pstmt.setString(2,"Brown");
                         pstmt.setDate(3, java.sql.Date.valueOf("1950-01-01"));
                          pstmt.execute();
                          conn.close();



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

    }
}

Open in new window

0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36571370
My new code looks like:

Now, I have to deal ODBC drive issue.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class TestAccess2 {
    public static void main(String[] args) {
            try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.accdb)};"
        + "DBQ=C:\\Users\\Mike\\Documents\\Team\\AddressBookAccess2010.accdb;";
Connection conn = DriverManager.getConnection(database,"","");
PreparedStatement pstmt = conn.prepareStatement("insert into tblPerson(LastName) values(?)");
                          pstmt.setString(2,"Brown");
                          pstmt.execute();
                          conn.close();
            } catch(Exception ex){
                ex.printStackTrace();
            }
    }
}

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
ID: 36571377
But does it work ?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36572557
Tomorrow I will try on a computer that I know it has all the installation. After making sure it works, Then I will try to apply the necessary changes to my laptop.

I submitted the revised code for you to see if the path information has been applied properly.

Talk to you tomorrow.

Mike
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36572569
The path looks reasonable.
Unfortunately, I guess they now not install these drivers by default with office as they used to do it
with XP. So unless someone had the same purpose before, they may not have this driver either
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 36573127
It is programming environment. Most likely it is installed. We will see.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now