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?

score 4 · Answer 1 · answered Jun 27 '18 at 13:07

4

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))

answered Jun 27 '18 at 13:07

Erik A

31,639
12
42
67

Thanks buddy. it worked. However, the query is creating 800 MB of empty space which can be resolved by Compact/Repair. Is there any way to prevent MS Access from creating that "empty" space? – Jun 27 '18 at 13:40
1

That must be quite the query. Access tends to cache as it sees fit, and you can do very little to limit it. You could try executing the query using DAO instead. I'm not a python guy, but you can use `Win32.Com.GetObject("DAO.DBEngine.120")` to get a [DAO DBEngine object](https://msdn.microsoft.com/en-us/library/office/ff834506.aspx), use `OpenDatabase` to open a database, and then use `.Execute` on that database to run the query. You can also use this object to compact & repair from Python (if the db is closed). – Erik A Jun 27 '18 at 13:51
Thank you Eric. I tried the following import win32com.client; con = win32com.client.GetObject("DAO.DBEngine.120") but got error: – Jun 28 '18 at 07:58
File "", line 1, in import win32com.client; con = win32com.client.GetObject("DAO.DBEngine.120") File "C:\Users\3626416\Software\Anaconda\lib\site-packages\win32com\client\__init__.py", line 72, in GetObject return Moniker(Pathname, clsctx) File "C:\Users\3626416\Software\Anaconda\lib\site-packages\win32com\client\__init__.py", line 87, in Moniker moniker, i, bindCtx = pythoncom.MkParseDisplayName(Pathname) com_error: (-2147221020, 'Invalid syntax', None, None) – Jun 28 '18 at 07:59
As said, not a python guy, and that's a python error I don't understand. I know Python can work with COM objects like DAO.DBEngine.120, but I don't know what caused that error. – Erik A Jun 28 '18 at 08:53

Parfait · Answer 2 · 2018-06-28T13:28:47.157

0

Since you note: The query runs in MS Access quite OK but not through Python. One possible reason for this is Access stored queries are more efficient than application layer called queries since the engine saves and caches best execution plan. In the application layer (Python, VBA, etc.) when processing a string SQL statement, the Jet/ACE engine does not have time to plan the best execution.

Therefore, consider the following:

Add any needed indexes to JOIN variables of respective tables.
Save your UPDATE query as a stored query inside the database. Here, the saving process checks syntax, calculates and optimizes plan, and cache stats.
Run Compact & Repair in database to refresh stats.

Then, run query in Python as a stored proc with CALL command:

# SET AUTOCOMMIT PREFERENCE IN CONNECTION
cnxn = pyodbc.connect(..., autocommit=True)
...
crsr.execute("{CALL myUpdateQuery}")

edited Jun 28 '18 at 13:28

answered Jun 27 '18 at 17:31

Parfait

104,375
17
94
125

I've never heard of the `CALL` SQL command. I believe you're looking for `EXECUTE`. – Erik A Jun 28 '18 at 08:56
@ErikvonAsmuth ... See this [solution](https://stackoverflow.com/a/34635700/1422451) – Parfait Jun 28 '18 at 13:28
Odd. Using both DAO and ADO `CALL` gives a syntax error, while `EXECUTE` is valid for update queries over ADO connections. First time I've heard of it, and I can't find it documented in the SQL reference. But hey, if it works... – Erik A Jun 28 '18 at 13:33
@ErikvonAsmuth ... Earlier, I did not wrap call in curly braces which translates to `EXECUTE`. See my edit made with last comment which works in pyodbc and ADO. For DAO (which is not an ODBC API), simply run the named, stored query without `CALL`. `EXEC` also works with pyodbc and ADO. – Parfait Jun 28 '18 at 14:13

Error: Increase MaxLocksPerFile registry entry via Python

2 Answers2