I believe I've encountered this sort of problem before, primarily in dealing with Excel. If it's anything like what I'm referring to, it's probable that during your connection to the Access database you are receiving some form of UI prompt or otherwise "waiting for input" type of event. The root cause of this could be a number of things, but most likely is that the Access database is being held open by a separate process and the linked server connection attempt is being "warned" about it.
When you run
KILL against the spids on the SQL Server 2008 R2 server, I expect you get something along the lines of:
Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
If that's the case, you will be able to clear these "ghost" sessions by also terminating the pid on the other side of the connection. Go to the server where the Access database exists and run a
tasklist command from the console ( or use the task manager or whatever makes you happy ). If you have a number of these "ghost" spids, you'll likely notice a number of Access processes as well (
MSACCESS.EXE, [pid], blah blah ). You can remove these processes by using
taskkill /IM "MSACCESS.EXE" /F from the console, or if you're feeling cautious,
taskkill /PID [pid] /F. The termination of these destination processes should allow the "ghost" spids on the server to resolve.
Assuming this is the problem, you'll probably find that your
SELECT commands will function as they have in the past as well.