Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to connect MS Access directly

 
Will Lee
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I mean connect to Access tables withouth register .MDb as ODBC data source? I need to deal with many .mdb file without knowing their name (but only know it's *.mdb), and it's impossible to let user register each mdb file. Is there a way to read data from file, or connect to DB directly?
Thanks.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As far as I know there is no way around other then ODBC DSN.
 
Sirish Kumar Gongal Reddy
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Will Lee ,
It's very simple i will tell you,
ResuletSet rs=stmt.executeQuery("select * from [Emp$];");
while(rs.next()){
}
I hope that your are comfottable with rest of code.
Emp: is a table name.

Regards,
G Sirish Reddy.,
 
David Ulicny
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That is exactly what Will don't want to do
 
Sirish Kumar Gongal Reddy
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi David Ulicny ,
what about your comment for my above code plz let me know.
Regards,
G Sirish Reddy.,
 
David Ulicny
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your code is fine, but as I understand Will question, he wants read data from mdb file directly, not through JDBC or ODBC, as you suggested.
He wants to do something like:

openTheMdbFile();
readDataFormMdbFile();
doSomethingWithData();
 
Will Lee
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you guys. Yes, I'm working on an open microscopy environment project (OME) that need to read and parse data from many formats into XML file. The last format I left is the .mdb file. Since there could be uncertain amount of .mdb files with unknown names in many folders, it's impossible to let user register DSN for each of them. Hence I need to find an approach to bypass this step.

I searched similar message on theServerSide.com, it seems a guy tried to do same thing in 2002. There's one reply there: download file from Microsoft and that's it. This person didn't say anything about this magic file and how to deal with it. I checked the MS and didn't find anything useful.

Any more suggestion? Thanks a lot.
 
Andrew Eccleston
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Will,

It is possible to specify the database to use in your connection url. It's been a while since I've done this, but it should be something close to this:


Oddly enough, where I found useful information about the parameters like the DBQ and DRIVER (there's a number of other parameters) in the string was at Microsoft. Search for DSN-less connections or ADO connections, both using ASP.

Hope this helps.

andrew
 
David Ulicny
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Take look at this, maybe it helps you

http://mdbtools.sourceforge.net/
 
Will Lee
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot! I didn't know the terminology "DSN-less connections" Now I googled this keyword and get many links. Hopefully I will work it out soon
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There's also Jackcess if a non-JDBC solution is acceptable.
 
Will Lee
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here's the work codes I copied from intnet:


public static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
public static final String DATABASE = "jdbc dbc RIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\Edu\\Java\\Forum\\DataConnection.mdb";
public static final String USERNAME = "admin";
public static final String PASSWORD = "";

public static void main(String [] args)
{
try
{
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
if (conn != null)
{
DatabaseMetaData meta = conn.getMetaData();
String productName = meta.getDatabaseProductName();
String productVersion = meta.getDatabaseProductVersion();
conn.close();

System.out.println("Driver : " + driver);
System.out.println("Database: " + url);
System.out.println("Username: " + username);
System.out.println("Password: " + password);
System.out.println("Product : " + productName);
System.out.println("Version : " + productVersion);
}
else
{
System.out.println("Database connection was null");
}
}
catch (ClassNotFoundException e)
{
System.err.println("Couldn't load JDBC driver class");
e.printStackTrace();
}
catch (SQLException e)
{
System.err.println("SQL state: " + e.getSQLState());
System.err.println("SQL error: " + e.getErrorCode());
e.printStackTrace();
}
catch (Exception e)
{
e.printStackTrace();
}
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic