0

I am trying to insert data into ms access

I successfully insert some rows and suddenly I get an error. After the error, I can't INSERT more rows.

An example:

import pyodbc

cnxn = pyodbc.connect(your_driver_and_path_to_ms_access)
crsr = cnxn.cursor()


query1 = '''CREATE TABLE tbl01 (Id INT, name VARCHAR(25))  '''

crsr.execute(query1)
cnxn.commit()


query2 = ''' INSERT INTO tbl01 (Id,name) VALUES(?,?) '''  

for i in range(5000):
    param = [i] + ['test']
    crsr.execute(query2, param)
    cnxn.commit() 

crsr.close()

Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] Cannot open database '|'. It may not be a database that your application recognizes, or the file may be corrupt. (-1206) (SQLExecDirectW)")

Inserting up 2000 most of the time works fine.

And also reading the table after the Error works.

query3 = ''' select * from  tbl01 '''  
crsr.execute(query3)
crsr.fetchone()

Thanks in advance

Juan Chamie
  • 61
  • 1
  • 4
  • 1
    I cannot reproduce. Can you re-try after restart of Python session or machine, dropping table? What ODBC driver are you using (please show in connection sting)? And what version of pyodbc are you using (`pyodbc.version`)? – Parfait Sep 14 '20 at 19:33
  • It only works again after restarting the pc. I can't drop the table, not even inside ms access. I can't add a new row in ms access neither pyodbc version '4.0.0-unsupported' – Juan Chamie Sep 14 '20 at 20:10

0 Answers0