This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes JDBC and the fly likes How to get list of tables in MS access database into my java program Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to get list of tables in MS access database into my java program" Watch "How to get list of tables in MS access database into my java program" New topic
Author

How to get list of tables in MS access database into my java program

SRK Reddy
Greenhorn

Joined: Jul 05, 2007
Posts: 2
Hi,

I want to get the list of tables in an MS access DB into my Java program. I wrote the below code but it giving me "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MsysObjects'." Eventhough I used the admin login.

....
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbcdbc:sampledb";
Connection con = DriverManager.getConnection(url,"admin","");
PreparedStatement getTablesList = con.prepareStatement("SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;");
ResultSet rs1 = getTablesList.executeQuery();
.......

Is there any other way to access the list of tables in the DB?
Gaurav Chikara
Ranch Hand

Joined: Jun 09, 2000
Posts: 410
Some more information is required from you for this problem before it can be answered completely
Like what are the available users in MS-Acess and have you checked the rights on the tables.
I believe there must be some sysuser who must be defined as owner of this objects


SCJP,SCWCD,SCBCD<br />If Opportunity doesn't knock then build the door
SRK Reddy
Greenhorn

Joined: Jul 05, 2007
Posts: 2
Thanks for the reply!

There is only one user in this database which is very small and it is "admin" who has all the rights. There is no sysadmin or sysuser.
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

Thats the MS "JET" database engine and it looks like you are not using an SQl query. If you are using JDBC I believe you need to use SQL here.
Jürgen F. Kilian
Greenhorn

Joined: Jun 05, 2009
Posts: 1
In cases where you don't have access to the MSysObjects Table you can use
the DatabaseMetaData object like:

Connection con = getConnection();
DatabaseMetaData d = con.getMetaData();
ResultSet rs = d.getTables(null, null, "%", null);

The object rs contains now the metadata (including table name) of all tables in the db.

For more infos please see the javadocs on DatabaseMetaData.getTables

Cheers,

Jürgen
rk palleru
Greenhorn

Joined: Feb 26, 2009
Posts: 22
i think use "select * from tab" . then you can get the table names of database.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Mr. C Lamont Gilbert wrote:Thats the MS "JET" database engine and it looks like you are not using an SQl query. If you are using JDBC I believe you need to use SQL here.

You can use whatever varient of SQL your database supports. If Access understands that Left$ stuff (no idea if it does) JDBC wont complain.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
 
jQuery in Action, 2nd edition
 
subject: How to get list of tables in MS access database into my java program
 
Similar Threads
JBoss 4.0 and MSSQL Server 2000
stuck up in the problem
Having serious trouble configuring Authorization
problem with j_security_check
Netbeans and MS Access