Solved

how to query MSysObjects (using Java)

Posted on 2003-12-08
4
1,393 Views
Last Modified: 2007-12-19
I have a Java app which reads .mdb files like so:

       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        // we are using the direct connection method rather than the DNS method
        String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
        database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end
        Connection con = DriverManager.getConnection( database ,"","");
        ResultSet rs;
        DatabaseMetaData metaData = con.getMetaData();
        // find out what tables and queries are in the database, and display their names
        rs = metaData.getTables(null,null,"%",null);

... which works great, however

I want to query against the system tables in the .mdb, specifically MSysObjects and MSysQueries to get som more structural info, but I get error:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Records can not be read; no read permission on 'MSysObjects'.

from the line:
Statement s = con.createStatement();
ResultSet rs_test = s.executeQuery("SELECT * FROM MSysObjects");

I am sure I solved this problem befor by tweaking a setting in Access, but I can't  remember how I did it. I have enabled viewing of the hidden and system tables, but this did not solve it. any help would be great!
0
Comment
Question by:kengoudsward
  • 3
4 Comments
 
LVL 10

Expert Comment

by:jobrienct
ID: 9902312
this thread appears to describe your problem quite well, albeit from a coldfusion perspective, the issue is securty settings i believe, read down past the original answer.

hth

John
0
 
LVL 10

Accepted Solution

by:
jobrienct earned 400 total points
ID: 9902314
0
 

Author Comment

by:kengoudsward
ID: 9909451
John, Thanks for the help
the thread you pointed me to said to...

Open up the database in Access, go to Tools, Options, View and select Hidden
and System objects.  

... which I had already done and wasn't the problem, but it was close. After a bit more playing around (and some sleep, and a new day)

I went to Tools, Security, User and Group Permissions
where I gave read permissions to all users and groups on the tables of interest; now it works!
0
 
LVL 10

Expert Comment

by:jobrienct
ID: 9909790
sorry, you read me wrong - i told you to scroll down passed the accepted answer a bit to where they talked about the security settings.

ahh well, next time I'll be more specific :)

thanks for the question,

John
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now