1

I'm using the pandas dataframe's to_sql function to save records to MS Access 97 MDB. But I'm getting the error below:

pyodbc.Error: ('HY104', '[HY104] [Microsoft][ODBC Microsoft Access Driver]Invalid 
precision value  (98) (SQLBindParameter)').

I'm using Python 3.8.5 32-bit on a Windows 10 Workstation.

I also have the following libraries installed.

datacompy==0.7.1
et-xmlfile==1.0.1
jdcal==1.4.1
numpy==1.19.2
openpyxl==3.0.5
pandas==1.1.2
pyodbc==4.0.30
python-dateutil==2.8.1
pytz==2020.1
pywin32==228
six==1.15.0
SQLAlchemy==1.3.19
sqlalchemy-access==1.0.7

Thanks in advance!

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Jay
  • 23
  • 4
  • 1
    Please provide full code block with `import` lines not just your error message. Also, please post sample of your pandas data frame with `dtypes` posted. Both code and data helps us reproduce your issue. See [mcve]. – Parfait Oct 03 '20 at 14:45

1 Answers1

1

You have encountered a limitation of the Access ODBC Driver when working with Access_97 database files. In Python_3 all strings are Unicode, but Access did not start supporting Unicode until Access_2000.

This code fails with the error you describe

num_characters = 128
df = pd.DataFrame(
    [
        (
            1,
            "x" * num_characters,
        )
    ],
    columns=["ID", "TextField"],
)
df.to_sql("sa_a_test", engine, index=False, if_exists="append")

for values of num_characters greater than 127 when working with an Access_97 (Jet 3.5) .mdb file. The same code does not fail when working with an Access_2000 (Jet 4.0) .mdb file.

As a workaround you might be able to use something like this

cnxn = engine.raw_connection()
crsr = cnxn.cursor()
sql = "INSERT INTO [sa_a_test] ([ID],[TextField]) VALUES (?,?)"
crsr.setinputsizes([(pyodbc.SQL_INTEGER,), (pyodbc.SQL_LONGVARCHAR,)])
crsr.executemany(sql, df.itertuples(index=False))
cnxn.commit()

but a better solution would be to upgrade the database file to a newer version. The Access_97 file format has been deprecated for many years and is now completely unsupported by Microsoft.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks for great answer! However, we can't just upgrade the MS Access from 97 to newer one because of a legacy software. The above suggested code didn't work for me. However, overriding the encoding/decoding to UTF-8 worked. import pyodbc conn = branch_db_conn.raw_connection() conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8') conn.setencoding(encoding='utf-8') cursor = conn.cursor() – Jay Oct 04 '20 at 02:21