Accessing MS Access database catalog using SQL

[AaronMason]

Hi,

Is it possible to use SQL to get a database's structure, like

SHOW TABLES

in MySQL? I'd like to create a database access interface for MS Access databases in JSP/Java Servlets and I need to be able to access the database structure. Of course, I would port this to other languages as well, but since I'm learning JSP/Servlets I'd like to start with that for the time being.

Thanks in advance

[Andrew Thackray]

Tables and everything else are stored in the hidden table msysObjects.

If in Tools>Options you ckeck the show system objects you will be able to see the table in the tables tab.

The SQL to list user tables is

SELECT Name as TableName from msysobjects where type = -32768

[AaronMason]

Hey,

Thanks for the quick reply, when I tried that query, this was the response I got:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'msysobjects'.

Any idea as to how I can acquire read permission on that table?

Thanks in advance

[Andrew Thackray]

Try making the table visible in ms access. In the access database do the following

ools>Options check the show system objects you should be able to see the table in the tables tab.

If this does not work try putting the sql

SELECT Name as TableName from msysobjects where type = -32768

into an access query. If the query runs (it does on my version) then you call the query in your Java code.