I have an Access DB which I connected to a SQL Server 2012 (v11.0.7507) as a linked server using Microsoft Office 12.0 Access Database Engine OLE DB Provider.
The connection is working fine usually but there are 2 automated jobs that push/pull data and update some other data in the Access DB either of which sometimes (not quite often but sometimes) gets "stuck" while connected to the Access DB. The session in SQL Server is visible but cannot be killed - or actually when I try to kill it it gets stuck in KILLED/ROLLBACK status.
No MSACCESS.EXE is open in tasklist, however the Administrative Tools' Open Files section does show the accdb (and the laccdb) files open by the SQL Agent. Unfortunately closing them there does not solve the problem, the accdb and laccdb files are still locked.
The only solution is restarting the SQL Server and then deleting the laccdb file but that is not a very comfortable solution.
I tried searching for similar issue but did not find a solution so far that would work. The main problem is the (seemingly) random nature. Have you guys experienced something similar? Do you know what could be wrong with this setup?
Many thanks in advance.
The Access OleDb provider has very limited support in unattended applications, and should never be loaded in a long-lived application. See the note on the download site:
The Office System Drivers are only supported under certain scenarios, including:
- Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files.
- To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive).
Try using a separate short-lived process instead of Linked Server. An SSIS package is a common choice.