SQL Server stopped working with MSAccess linked servers within last month

Question

I have been accessing 64-bit MS Office 2010 .accdb Access files from 64-bit SQL Server 2008 for six months now via linked servers:

    EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @provider=N'Microsoft.ACE.OLEDB.12.0', @srvproduct=N'OLE DB Provider for ACE', @datasrc=N'C:\MyPath\MyFile.accdb'

Within the last month, deleting or updating too many records (about 5000 or more) has been giving the error:

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "osdmini_agency" returned message "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.".

I went to modify the maxlocksperfile registry value (as I did six months ago to fix this) at HKLM\software\microsoft\office\14.0\access connectivity engine\engines\ace\ , but the error is still being thrown.

Any ideas?

Thanks,
--John

Answer 1

Hi John,

There are two workarounds for this issue.

The first one is what you have mentioned. Locate HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE

In the right pane of Registry Editor, double click MaxLocksPerFile.
On the Edit DWORD Value dialog box, click Decimal.
Modify the value of the Value data box as required, and then click OK.

This method changes the Windows registry setting for all applications that use the Microsoft Jet database engine version 4.0.

The second is use the SetOption method to temporarily change MaxLocksPerFile.

Please refer to this kb article:

http://support.microsoft.com/kb/815281/en-us

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.