how do I execute a 'GRANT SELECT ON' statement using pyodbc

Question

I am working with hundreds of msaccess databases. I am trying to build a summary of databases, tables and other objects. To find out all the tables and objects in a given database, I use the query

select * from MSysObjects

However, I get the pyodbc error message

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

How do I programmatically change the permission of all the msaccess databases. I tried using the 'GRANT SELECT ON' statement but I got the error message

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE',     'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

The connection string I used is in this form

Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\sample.mdb;

Many thanks for you help.

MSysObjects is a system table and getting read permissions may be somewhat inconvenient. It is by no means difficult to get summaries with VBA and DAO. You may wish to read http://www.tek-tips.com/viewthread.cfm?qid=1045679 — Fionnuala, Nov 25 '11 at 12:39

score 2 · Answer 1 · answered Nov 28 '11 at 19:26

Try using the tables and columns methods of the cursor. I am unable to test against Access 2003 or 2007, but the following works with Access 2010:

import pyodbc
connection = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Username\Desktop\Database.accdb;')
cursor = connection.cursor()
for row in cursor.tables():
    print row.table_name
for row in cursor.columns():
    print row.column_name

The tables method has options to filter by table, catalog, schema, and tableType. Columns method has options to filter by table, catalog, schema, and column names.

how do I execute a 'GRANT SELECT ON' statement using pyodbc

1 Answers1