SQL INSERT INTO does not work my Access database

Question

I work with a somewhat old software (2000's) that interfaces with MS Access. I am using Python to write the MDB files that I need to insert into the software. I need to update the values of 7 different tables in order to get the expected value. For all the 7 tables I am using the following code:

def sql_cols(df):
    cols_aux = tuple(df.columns)
    cols_aux = str(cols_aux).replace("'","")
    cols_aux = cols_aux.replace(', DESC', ', [DESC]')
    return cols_aux


for k in range(len(df)):
    vals = tuple(list(df.iloc[k]))
    cols = sql_cols(df)
    action = 'INSERT INTO Strategy {columns} VALUES {values}'.format(columns = cols, values = str(vals).replace("'",''))
    cursor.execute(action)
    conn.commit()

In 6 tables this procedure works, however i keep getting ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)') in the last table. the action statement for that table is something like:

'INSERT INTO Strategy (STRATGY_ID, NUMBUDGETS, PDISC_RATE, INC_IMPS, OPT_METHOD, OBJ_FUNC, MININCVAL, EFFFZONE, BUDGETPD, REPORTING, NUM_GROUPS, MATRIX_DEF, NAME, [DESC], FLT_CONRTE, WRK_CONRTE, NET_CONRTE, START_YEAR, END_YEAR, CURRENCY, FLEET, NETWORK, NUM_SECSEL, NUM_VEHSEL, ANAL_MODE, NUMPRJOPTS, NUMSECOPTS, INC_NMT, DOECONANAL, DISC_RATE, BASE_OPTN, ACCCOSTS, FATALCOST, INJURYCOST, DAMAGECOST, ALLACCCOST, ENERGY, EMISSIONS, ACCLEFFECT, LOGFILE, INCSENSTVY, NUMSENSCEN, DOASSETVAL, EXCVEHDATA, EXCPERDATA) 
VALUES (0, 0,  , 0, 1, 0, 0, 95, 0, -1, 0, 0, Road_Networks_PA,  , 1, 1, 1, 2019, 2038, Reais, Vehicles 2017, Road_Networks_PA, 157, 12, 0,  , 157, -1, -1, 6.49,  , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,  , 0, -1, -1)'

I've already tried to ut all columns in square brackets, [], but it didn't work also. Can anyone help-me with this problem?

Edit: Following Gordon's advice, I altered the code, but the same error appears:

for k in range(len(df)):
    vals = tuple(list(df.iloc[k]))
    cols = sql_cols(df)
    action = 'INSERT INTO Strategy {columns} VALUES {values}'.format(columns = cols, values = str(vals))
    cursor.execute(action)
    conn.commit()

and the action statement is:

"INSERT INTO Strategy (STRATGY_ID, NUMBUDGETS, PDISC_RATE, INC_IMPS, OPT_METHOD, OBJ_FUNC, MININCVAL, EFFFZONE, BUDGETPD, REPORTING, NUM_GROUPS, MATRIX_DEF, NAME, [DESC], FLT_CONRTE, WRK_CONRTE, NET_CONRTE, START_YEAR, END_YEAR, CURRENCY, FLEET, NETWORK, NUM_SECSEL, NUM_VEHSEL, ANAL_MODE, NUMPRJOPTS, NUMSECOPTS, INC_NMT, DOECONANAL, DISC_RATE, BASE_OPTN, ACCCOSTS, FATALCOST, INJURYCOST, DAMAGECOST, ALLACCCOST, ENERGY, EMISSIONS, ACCLEFFECT, LOGFILE, INCSENSTVY, NUMSENSCEN, DOASSETVAL, EXCVEHDATA, EXCPERDATA) 
VALUES (0, 0, ' ', 0, 1, 0, 0, 95, 0, -1, 0, 0, 'Road_Networks_PA', ' ', 1, 1, 1, 2019, 2038, 'Reais', 'Vehicles 2017', 'Road_Networks_PA', 157, 12, 0, ' ', 157, -1, -1, 6.49, ' ', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, -1, -1)"

Edit 2: As asked by Kshitij Saxena, here are my data types

Field Name  Data Type
STRATGY_ID  Number
NUMBUDGETS  Number
PDISC_RATE  Number
INC_IMPS    Yes/No
OPT_METHOD  Number
OBJ_FUNC    Number
MININCVAL   Number
EFFFZONE    Number
BUDGETPD    Yes/No
REPORTING   Yes/No
NUM_GROUPS  Number
MATRIX_DEF  Yes/No
NAME        Text
DESC        Text
FLT_CONRTE  Number
WRK_CONRTE  Number
NET_CONRTE  Number
START_YEAR  Number
END_YEAR    Number
CURRENCY    Text
FLEET       Text
NETWORK     Text
NUM_SECSEL  Number
NUM_VEHSEL  Number
ANAL_MODE   Number
NUMPRJOPTS  Number
NUMSECOPTS  Number
INC_NMT     Yes/No
DOECONANAL  Yes/No
DISC_RATE   Number
BASE_OPTN   Text
ACCCOSTS    Yes/No
FATALCOST   Number
INJURYCOST  Number
DAMAGECOST  Number
ALLACCCOST  Number
ENERGY      Yes/No
EMISSIONS   Yes/No
ACCLEFFECT  Yes/No
LOGFILE     Yes/No
INCSENSTVY  Yes/No
NUMSENSCEN  Number
DOASSETVAL  Yes/No
EXCVEHDATA  Yes/No
EXCPERDATA  Yes/No

Answer 1

CURRENCY is a reserved word in Access SQL, so if you have a column with that name you must enclose it in square brackets. That is, this will fail with "Syntax error in INSERT INTO statement." ...

crsr.execute("INSERT INTO Strategy (CURRENCY) VALUES ('USD')")

... but this will work:

crsr.execute("INSERT INTO Strategy ([CURRENCY]) VALUES ('USD')")

Answer 2

As Gordon said you need quotes around strings and you should not leave empty space between commas. Try to insert 0 or Null or whatever will fit better for that field:

VALUES (0, 0, Null, 0,

Answer 3

VARCHAR columns require values in quotes. Try building your query like:

INSERT INTO Strategy (STRATGY_ID, NUMBUDGETS, PDISC_RATE, INC_IMPS, OPT_METHOD, OBJ_FUNC, MININCVAL, EFFFZONE, BUDGETPD, REPORTING, NUM_GROUPS, MATRIX_DEF, NAME, [DESC], FLT_CONRTE, WRK_CONRTE, NET_CONRTE, START_YEAR, END_YEAR, CURRENCY, FLEET, NETWORK, NUM_SECSEL, NUM_VEHSEL, ANAL_MODE, NUMPRJOPTS, NUMSECOPTS, INC_NMT, DOECONANAL, DISC_RATE, BASE_OPTN, ACCCOSTS, FATALCOST, INJURYCOST, DAMAGECOST, ALLACCCOST, ENERGY, EMISSIONS, ACCLEFFECT, LOGFILE, INCSENSTVY, NUMSENSCEN, DOASSETVAL, EXCVEHDATA, EXCPERDATA) 
VALUES (0, 0, NULL , 0, 1, 0, 0, 95, 0, -1, 0, 0, "Road_Networks_PA", NULL , 1, 1, 1, 2019, 2038, "Reais", "Vehicles 2017", "Road_Networks_PA", 157, 12, 0, NULL, 157, -1, -1, 6.49, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL , 0, -1, -1)

Add NULL or "" (empty string) for columns you want to keep null or empty.