Issue 325: Failing to drop table from MS access database
Status:  New
Owner: ----
Reported by rubisc...@gmail.com, May 31, 2013
What steps will reproduce the problem?
1. Open a connection to a database which may or may not have tables:
        cxn = self.createConnection(database)
        cursor = cxn.cursor()
2. Get the list of table names and iterate through, deleting all tables:
        for table in cursor.tables():
            if table.table_type == "TABLE":
                drop = "Drop Table {0}".format(table.table_name)
                cursor.execute(drop)

What is the expected output? What do you see instead?

I expect the tables to be deleted from the database (they are not). Instead this error is given: pyodbc.ProgrammingError: No results. Previous SQL was not a query.

What version of the product are you using? On what operating system?
3.0.6 32 bit on Windows for python 2.6

Please provide any additional information below.

Jun 3, 2013
#1 Gordon.D...@gmail.com
I'm not so sure that this is necessarily a bug. You are using an object named `cursor` to iterate over the list of tables, but then you use that same object to perform the DROP, and that messes with the state of the object. Using a second cxn.cursor object to perform the DROP avoids the problem:


import pyodbc

cxn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\__tmp\\dropTest.accdb;')
cursor = cxn.cursor()
cursor2 = cxn.cursor()
for table in cursor.tables():
    if table.table_type == "TABLE":
        drop = "DROP TABLE [{0}]".format(table.table_name)
        print drop
        cursor2.execute(drop)
cxn.commit()
cxn.close()

Jun 3, 2013
#2 rubisc...@gmail.com
Thank you, you are correct, and that did the trick. Clearly my novice is showing!