• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
SRK Reddy
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 412
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
SRK Reddy
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i think use "select * from tab" . then you can get the table names of database.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic