Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

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

 
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 = "jdbc:odbc: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?
 
Ranch Hand
Posts: 413
  • 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.
 
Ranch Hand
Posts: 1170
Hibernate Eclipse IDE 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.
 
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
 
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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE 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.
 
What's gotten into you? Could it be this tiny ad?
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic