CODEWITHSUNDEEP
javadatabasems-accessjdbc-odbc
yao
yao

Reputation: 1

How to list all tables' name in MSAccess database file using sql query in java?

I've getting some code here

String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + f.getPath() + ";";
                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                    Connection conn = DriverManager.getConnection(url);
                    Statement st=conn.createStatement();
                    String query="SELECT name FROM MSysObjects WHERE Type=1 AND Flags=0";
                    ResultSet rs=st.executeQuery(query);
                    ArrayList<String> tableNames=new ArrayList<String>();
                    while(rs.next()){
                        String name=rs.getString(1);
                        tableNames.add(name);
                    }

but there has some problem

[Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysObjects'."

and I searched for this problem and Just found this: social.msdn.microsoft.com/Forums/sqlserver/en-US/… " Because MSysObjects is a system table in Access, the Admin user does not have permission to read data in it". I read the answer, but in fact, I want to access permission by programming, does anyone can help me? Thank you very much

Upvotes: 0

Views: 1150

Answers (1)

wero
wero

Reputation: 32980

Seems that Access does not allow to query MSysObjects. Did you follow the steps in the article to give your user the adequate permission?

Anyway JDBC also has an own API which allows you to read database metadata:

 Connection conn = ...
 DatabaseMetaData metaData = conn.getMetaData();
 ResultSet rs = metaData.getTables(null, null, "%", null); 
 while (rs.next()) {
      String name = rs.getString(3); // see javadoc of DatabaseMetaData
      tableNames.add(name);
 }

Upvotes: 1

Related Questions