CODEWITHSUNDEEP
pythonms-accesspyodbcsql-grant
user671111
user671111

Reputation: 41

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

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.

Upvotes: 1

Views: 1232

Answers (1)

Bryan
Bryan

Reputation: 17703

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.

Upvotes: 2

Related Questions