-
access database problem
I am getting a file sharing lock count exceeded error.
I am trying to delete a lot of rows from a table, about 18000 rows.
lsSQL = "Delete from " & lsTblName & " " & lsWhereClause
CnAccess.Execute lsSQL, lnRecsAffected
Does anyone know why this would happen or if there is away for me to delete such large amounts of data?
-
Put this line of code in just before running your query:
DAO.DBEngine.SetOption dbMaxLocksPerFile, 2000000
The MaxLocksPerFile settings in the registry (in both HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0, and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 3.x) is set to 9500, which isn't enough sometimes....
I never figured out how to change this setting by code (GetSetting and SaveSetting), but the above deals with it just fine. Alternatively, go straight into regedit and change the values manually.
-