Working with a databas... Access in this example

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();
          
          
        
         

Select allOpen in new window

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();
          
          
        
         

Select allOpen in new window

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

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

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();

Select allOpen in new window

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
 

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

FYI, I am working with Windows 7 professional.

I will go bed now and check back tomorrow, thx

Do you have this ODBC sources in controlpanel?

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();
            }
        

    }

}

Select allOpen in new window

accessDB.PNG

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

I am still trying to find out about ODBC in control panel. Also see: https://www.experts-exchange.com/questions/27317445/ODBC-sources-in-controlpanel.html

Mike

Also have in mind that I am using Access 2010 not 2003 or earlier version.

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();
            }
        

    }
}

Select allOpen in new window

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?

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

Show me your System DSN tab

Here you are. Team is the sub-folder where the database is.
DSN-Tabs.png

I guess I need to use User DSN tab to create one for Access.

I am not exactly sure how to do this though.

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

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

The problem will be if when you click Add you'll not see
MicrosofT Access Driver (*.mdb) driver in the list

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.

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

Is your Windows 32 bit ?

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

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.

it is 64 bit system.

Well, that's even worse

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

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

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();
            }
    }
}

Select allOpen in new window

But does it work ?

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

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

It is programming environment. Most likely it is installed. We will see.