f



MS Access databases

Hello,

I have been trying to read a simple MSAccess database and show
the records on screen but no success so far.
I  have created a DSN (Data Source Name) in my Win 2k box
and the connection goes ok.
But when I try to return a ResultSet using a simple SELECT statement,
("SELECT * FROM MyTable")
no records appear. Database is Ok. It is a very simple table.

I don't get *any* error message but also no records from it.

Is there anything special I need to do for accessing it?
Does anybody have a simple code for accessing MSAccess databases?
(From DSN creation till sample code)


Thanks in Advance.

Java Newbie.


0
H3li0
11/5/2003 11:07:17 PM
comp.lang.java.databases 3049 articles. 0 followers. samyaksulabh (16) is leader. Post Follow

7 Replies
1446 Views

Similar Articles

[PageSpeed] 57

import java.sql.*;

public class DataBase {
    public static void main(String[] args) {
        String data = "jdbc:odbc:YahooAddrBook";  // data base friendly nam
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection conn = DriverManager.getConnection(
                data, "", "");
            Statement st = conn.createStatement();
            String q =
                "SELECT * " +
                "FROM yahoo_ab " +              // table name
                "WHERE (\"Last Name\" LIKE '"+ args[0]+"') "+
                "ORDER BY (\"First Name\")";
                System.out.println(q);
            ResultSet rec = st.executeQuery(q);
            while(rec.next()) {
                System.out.println(
                          rec.getString("First Name")     + ", "
                        + rec.getString("Last Name")      + ", "
                        + rec.getString("E-Mail Address") + ".");
            }
            st.close();
        } catch (Exception e) {
            System.out.println("Error -- " + e.toString());
            e.printStackTrace();
        }
    }
}
"H3li0" <junior_helio@hotmail.com> wrote in message
news:bobvqp$1cqqpv$1@ID-147895.news.uni-berlin.de...
> Hello,
>
> I have been trying to read a simple MSAccess database and show
> the records on screen but no success so far.
> I  have created a DSN (Data Source Name) in my Win 2k box
> and the connection goes ok.
> But when I try to return a ResultSet using a simple SELECT statement,
> ("SELECT * FROM MyTable")
> no records appear. Database is Ok. It is a very simple table.
>
> I don't get *any* error message but also no records from it.
>
> Is there anything special I need to do for accessing it?
> Does anybody have a simple code for accessing MSAccess databases?
> (From DSN creation till sample code)
>
>
> Thanks in Advance.
>
> Java Newbie.
>
>


0
nos
11/5/2003 11:14:35 PM
It works now.
Thanks a LOT !

I wonder if the process to access a SQL Server 2000 database would be the
same.

Is tehere any difference in the process? Or is it just about the DSN change?

Cheers,

Java Newbie


"nos" <nos@nospam.com> wrote in message
news:vvfqb.115975$e01.421770@attbi_s02...
> import java.sql.*;
>
> public class DataBase {
>     public static void main(String[] args) {
>         String data = "jdbc:odbc:YahooAddrBook";  // data base friendly
nam
>         try {
>             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
>             Connection conn = DriverManager.getConnection(
>                 data, "", "");
>             Statement st = conn.createStatement();
>             String q =
>                 "SELECT * " +
>                 "FROM yahoo_ab " +              // table name
>                 "WHERE (\"Last Name\" LIKE '"+ args[0]+"') "+
>                 "ORDER BY (\"First Name\")";
>                 System.out.println(q);
>             ResultSet rec = st.executeQuery(q);
>             while(rec.next()) {
>                 System.out.println(
>                           rec.getString("First Name")     + ", "
>                         + rec.getString("Last Name")      + ", "
>                         + rec.getString("E-Mail Address") + ".");
>             }
>             st.close();
>         } catch (Exception e) {
>             System.out.println("Error -- " + e.toString());
>             e.printStackTrace();
>         }
>     }
> }
> "H3li0" <junior_helio@hotmail.com> wrote in message
> news:bobvqp$1cqqpv$1@ID-147895.news.uni-berlin.de...
> > Hello,
> >
> > I have been trying to read a simple MSAccess database and show
> > the records on screen but no success so far.
> > I  have created a DSN (Data Source Name) in my Win 2k box
> > and the connection goes ok.
> > But when I try to return a ResultSet using a simple SELECT statement,
> > ("SELECT * FROM MyTable")
> > no records appear. Database is Ok. It is a very simple table.
> >
> > I don't get *any* error message but also no records from it.
> >
> > Is there anything special I need to do for accessing it?
> > Does anybody have a simple code for accessing MSAccess databases?
> > (From DSN creation till sample code)
> >
> >
> > Thanks in Advance.
> >
> > Java Newbie.
> >
> >
>
>


0
H3li0
11/6/2003 9:23:36 AM
"H3li0" ...
> It works now.
> Thanks a LOT !
>
> I wonder if the process to access a SQL Server 2000
> database would be the same.
>
> Is tehere any difference in the process?
> Or is it just about the DSN change?

It's one way to do it, to create a DSN for the SQL-Server, and change it in
your code.

It'll work, but ODBC gives poor performance, so it would be recommended that
you instead use a JDBC Type 4 driver for SQL Server:

   http://tinyurl.com/twlb

Put the jar-files in your classpath and change the drivername to:

  com.microsoft.jdbc.sqlserver.SQLServerDriver

.... and the url (what you called "data") to:

  jdbc:microsoft:sqlserver://servername:1433

....where you change "servername" for the actual server SQL-Server runs on.
Possibly you have to change the portnumber as well, if i is configured to
listen on another port (check with your DBA).

// Bjorn A



0
Bjorn
11/6/2003 3:27:51 PM
Correct that it is only one way to do it.
I would have liked to have used JDBC, but didn't
know how at the time and only ODBC is
available on my pc.

If anyone knows of a JDBC driver for MS Access
or MS Works please let me know.
Phil....

"Bjorn Abelli" <DoNotSpam.bjorn_abelli@hotmail.com> wrote in message
news:bodp9n$1d5icj$1@ID-73292.news.uni-berlin.de...
> "H3li0" ...
> > It works now.
> > Thanks a LOT !
> >
> > I wonder if the process to access a SQL Server 2000
> > database would be the same.
> >
> > Is tehere any difference in the process?
> > Or is it just about the DSN change?
>
> It's one way to do it, to create a DSN for the SQL-Server, and change it
in
> your code.
>
> It'll work, but ODBC gives poor performance, so it would be recommended
that
> you instead use a JDBC Type 4 driver for SQL Server:
>
>    http://tinyurl.com/twlb
>
> Put the jar-files in your classpath and change the drivername to:
>
>   com.microsoft.jdbc.sqlserver.SQLServerDriver
>
> ... and the url (what you called "data") to:
>
>   jdbc:microsoft:sqlserver://servername:1433
>
> ...where you change "servername" for the actual server SQL-Server runs on.
> Possibly you have to change the portnumber as well, if i is configured to
> listen on another port (check with your DBA).
>
> // Bjorn A
>
>
>


0
nos
11/6/2003 7:04:21 PM
"nos" wrote...
> Correct that it is only one way to do it.
> I would have liked to have used JDBC,
> but didn't know how at the time and only ODBC is
> available on my pc.

Just to clarify things, you *are* using JDBC in your example, but you're
using the internal JDBC-ODBC-bridge for access to Access...

> If anyone knows of a JDBC driver for MS Access
> or MS Works please let me know.

My last comment was on the question on how to change the connection to
SQL-Server instead of MS Access, and in the case of SQL-server i pointed to
a place where the JDBC-driver can be downloaded.

   http://tinyurl.com/twlb

AFAIK there exists no JDBC-driver of type 4 (native access) for MS Access.
There are some drivers for Access of type 2 and 3 (additional software
besides the driver has to be installed) listed on Sun's site:

   http://tinyurl.com/qhu4

NB! The list says that BCS has drivers of type 4, but that's only for SQL
Server, not MS Access...

// Bjorn A



0
Bjorn
11/6/2003 11:44:33 PM
thanks i'll try it out

"Bjorn Abelli" <DoNotSpam.bjorn_abelli@hotmail.com> wrote in message
news:boemd1$1ca0dq$1@ID-73292.news.uni-berlin.de...
>
> "nos" wrote...
> > Correct that it is only one way to do it.
> > I would have liked to have used JDBC,
> > but didn't know how at the time and only ODBC is
> > available on my pc.
>
> Just to clarify things, you *are* using JDBC in your example, but you're
> using the internal JDBC-ODBC-bridge for access to Access...
>
> > If anyone knows of a JDBC driver for MS Access
> > or MS Works please let me know.
>
> My last comment was on the question on how to change the connection to
> SQL-Server instead of MS Access, and in the case of SQL-server i pointed
to
> a place where the JDBC-driver can be downloaded.
>
>    http://tinyurl.com/twlb
>
> AFAIK there exists no JDBC-driver of type 4 (native access) for MS Access.
> There are some drivers for Access of type 2 and 3 (additional software
> besides the driver has to be installed) listed on Sun's site:
>
>    http://tinyurl.com/qhu4
>
> NB! The list says that BCS has drivers of type 4, but that's only for SQL
> Server, not MS Access...
>
> // Bjorn A
>
>
>


0
nos
11/6/2003 11:47:03 PM
"nos" ...
> thanks i'll try it out

Well, don't be too hasty...

If your application functions well enough against MS Access with the
JDBC-ODBC-bridge, it could very well be a turn to the worse to try another
driver (as it AFAIK doesn't exist any type 4 drivers for MS Access).

As I said, you already *are* using JDBC, but if you change to another
database, such as SQL Server, you should change from the JDBC-ODBC-bridge to
another driver.

HTH

// Bjorn A


0
Bjorn
11/6/2003 11:55:28 PM
Reply: