Error: Increase MaxLocksPerFile registry entry via Python

Question

I am running a rather complec update MS Access query from Python:

 qry = '''
        UPDATE H500_ODFlows INNER JOIN H500_UPDATE ON 
        (H500_ODFlows.Product = H500_UPDATE.Product) 
        AND (H500_ODFlows.Dest = H500_UPDATE.DestCode) 
        AND (H500_ODFlows.Orig = H500_UPDATE.OrigCode) 
        SET H500_ODFlows.Pieces = [H500_UPDATE].[Pieces],
        H500_ODFlows.Weight = [H500_UPDATE].[Weight], 
        H500_ODFlows.Cons = [H500_UPDATE].[Pieces], 
        H500_ODFlows.DeadWeight = [H500_UPDATE].[DeadWeight], 
        H500_ODFlows.DoNotRead = [H500_UPDATE].DoNotRead,
        H500_ODFlows.[_OrigCountryCode] = [H500_UPDATE].[_OrigCountryCode],
        H500_ODFlows.[_DestCountryCode] = [H500_UPDATE].[_DestCountryCode]
    '''

try:
    crsr.execute(lb.cleanqry(qry))
    cnxn.commit()
    print('Updating was successful.')
except Exception as err:
    print('Updating failed. See the error.' + str(err))

but get the following error:

('HY000', '[HY000] [Microsoft][ODBC Microsoft Access Driver] File sharing lock count exceeded. Increase MaxLocksPerFile registry entry. (-1033) (SQLExecDirectW)')

I followed the instructions to increase "MaxLocksPerFile" but it is not helping. Moreover, the query runs in MS Access quite OK but not through Python. Any advice?

Answer 1

Try running the query with autocommit on. That way, the database won't need to keep all those locks open, but can just commit everything as the query runs.

 qry = '''
        UPDATE H500_ODFlows INNER JOIN H500_UPDATE ON 
        (H500_ODFlows.Product = H500_UPDATE.Product) 
        AND (H500_ODFlows.Dest = H500_UPDATE.DestCode) 
        AND (H500_ODFlows.Orig = H500_UPDATE.OrigCode) 
        SET H500_ODFlows.Pieces = [H500_UPDATE].[Pieces],
        H500_ODFlows.Weight = [H500_UPDATE].[Weight], 
        H500_ODFlows.Cons = [H500_UPDATE].[Pieces], 
        H500_ODFlows.DeadWeight = [H500_UPDATE].[DeadWeight], 
        H500_ODFlows.DoNotRead = [H500_UPDATE].DoNotRead,
        H500_ODFlows.[_OrigCountryCode] = [H500_UPDATE].[_OrigCountryCode],
        H500_ODFlows.[_DestCountryCode] = [H500_UPDATE].[_DestCountryCode]
    '''

try:
    cnxn.autocommit = True
    crsr.execute(lb.cleanqry(qry))
    print('Updating was successful.')
except Exception as err:
    print('Updating failed. See the error.' + str(err))