CREATE TABLE with DECIMAL column using Access ODBC

Question

I am using pypyodbc module to create a database and add a table in it.

The code I use is the following:

import pypyodbc
pypyodbc.win_create_mdb('D:\\test.mdb')
connection_string = 'Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\\test.mdb'
connection = pypyodbc.connect(connection_string)
SQL = "CREATE TABLE [test] ([FID] INT,[AREA] FLOAT,[CODE] VARCHAR(10),[X_CO] DECIMAL(10,3));"
connection.cursor().execute(SQL)
connection.commit()
connection.close()

The above code creates the database but it produces an error when it goes to create the table. The error is the following:

ProgrammingError: (u'42000', u'[42000] [Microsoft] [ODBC Microsoft Access Driver] Syntax error in defining field.

From searching the web I found out that the problem is with the DECIMAL type of the last field and when I remove it from the query, it creates the table.

I also tried to change an option in Access to make the database compatible with ANSI 92 and when I did it, the table could be created from inside Access.

Because this process must be done hundreds of databases, I would like to do this in a python loop but for this to happen, the creation of the database and the table must be done from the above code.

Am I missing something in the syntax? Does anyone know if this is a problem of the module?

score 3 · Accepted Answer · answered Jun 21 '16 at 12:49

There are a number of deficiencies with DDL handling in the Access ODBC driver and unfortunately you have encountered one of them. If you want to create a table with a DECIMAL column you will need to use adodbapi from pywin32 (release 211 or later) and the Access OLEDB provider to execute the CREATE TABLE statement:

# -*- coding: utf-8 -*-
import adodbapi  # (from pywin32)
conn_str = (
    r"Provider=Microsoft.Jet.OLEDB.4.0;"
    r"Data Source=C:\Users\Public\2003test.mdb;"
    )
conn = adodbapi.connect(conn_str)
crsr = conn.cursor()
sql = """\
CREATE TABLE [test] (
    [FID] INT,
    [AREA] FLOAT,
    [CODE] VARCHAR(10),
    [X_CO] DECIMAL(10,3))
"""
crsr.execute(sql)
conn.commit()
crsr.close()
conn.close()

CREATE TABLE with DECIMAL column using Access ODBC

1 Answers1