Error while trying to reconnect same DB after closing its connection

[mariasabba]

Environment

• Python: 3.11.6
• pyodbc: 5.0.0
• OS: Windows 64-bit
• DB: Microsoft Access
• driver: Microsoft Access Driver (.mdb, .accdb)

Issue

I have a function in my code that inserts data into my database. It happens that I have some files that needs to be attached in my DB, but as pyodbc in Access usage doesn't permits it, I came around connecting and closing connection on each insert statement, so I can insert the attachments using another lib. The issue is that the first insert works perfectly, but when I get in the second one, I tgives me this error:

pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine could not find the object {table_name}. Make sure the object exists and that you spell its name and the path name correctly.

I checked with cursor.tables(), and my table is there. It seems like the first connection is not really closed, so the new one cannot find it. I tried to close it explicitly, with context manager, but nothing worked.

Error Reproduction

Here is some sample that can reproduce my situation:

import pyodbc

connection_string = "DRIVER={DriverName};DBQ={DBPath}"

def consult(sql):
    with pyodbc.connect(connection_string) as conn:
        cursor = conn.cursor()
        cursor.execute(sql)
        result= cursor.fetchall()
    return result

r1 = consult("SELECT * FROM Table1")
r2 = consult("SELECT * FROM Table1")`

The error raises when It's running r2 statement.

[gordthompson]

I tried to close [the connection] explicitly, with context manager

Using a context manager does not automatically close the connection. See #43 for details. You could try

def consult(sql):
    conn = pyodbc.connect(connection_string)
    cursor = conn.cursor()
    cursor.execute(sql)
    result= cursor.fetchall()
    cursor.close()
    conn.close()
    return result

[mariasabba]

@gordthompson thanks for the answer!

I actually had already tryied this code you sent me, and still got the same error.

[v-chojas]

Could you post an ODBC trace?

[mariasabba]

@v-chojas sure! Here it is. SQL.LOG

[v-chojas]

Looking at the trace, it does appear to close the first connection before opening the second one, so I'd consider this possibly a driver bug.

[gordthompson]

I am unable to reproduce the issue using the repro code provided.

import pyodbc

DriverName = "Microsoft Access Driver (*.mdb, *.accdb)"
DBPath = r"C:\Users\Public\Database1.accdb"
connection_string = f"DRIVER={DriverName};DBQ={DBPath}"

def consult(sql):
    with pyodbc.connect(connection_string) as conn:
        cursor = conn.cursor()
        cursor.execute(sql)
        result= cursor.fetchall()
    return result

r1 = consult("SELECT * FROM Table1")
print(r1)  # [(1, 'foo'), (2, 'bar')]
r2 = consult("SELECT * FROM Table1")
print(r2)  # [(1, 'foo'), (2, 'bar')]

No errors reported.

Windows 8.1 Python 3.10.9 ACE (Access) driver 15.00.4695.1000

[gordthompson]

Can you make a backup copy of the .accdb file (just in case), then open it in Access and perform a "Compact and Repair Database" operation on it to see if that makes any difference?

[mariasabba]

@gordthompson i did that and made no difference. But as you reproduced a generic example, I tryied it too, and guess what, It worked! So maybe the problem is indeed with my database. It is a linked DB with an online source, so maybe it is affecting the connection.

As it is no longer a pyodbc issue, I'll close this thread, but if you have any idea on how to overcome this issue, feel free to contact me. Thank you all so much for the support!

[gordthompson]

One other thing you could try would be to set pyodbc.pooling = False right at the beginning of your script (before the first call to pyodbc.connect()).