ODBC Connections locking tables in SQL Server
Unfortunately, we've dealt with too may issues with this where I work. In cases where this has been a problem, people who don't know databases, literally open a SQL table "live" in MS Access. At first things can appear transient because they may open and close Access quickly. But then there are those moments where they leave MS Access open on the live SQL table while they go away for lunch or perhaps through the long weekend--blocking applications that try to use the database. Just to state the obvious--blocking can adversely affect any application trying to use the blocked SQL row, table, or database.

All we really had to do to prove anything was capture data from sp_who2 to find the blocking chains. 

That being said, when I'm away from the console, I still use the following code I wrote over a decade ago (I mean I'm still using DBCC InputBuffer) to find these pesky issues -- if needed (please run in a test/dev environment to see if it meets your needs). Though certainly not the Rolls Royce of routines it still works for me.

This code essentially creates a table, BlockingProcesses, in a new database DBADMIN to store the blocking information. Two stored procedures sp__Maint_BlockWatch and sp__Maint_Blockingprocesses work together to find the blockers. And a SQL job runs once a minute to check for blocks. You may need to change minor things in the code below like the user who needs to execute the job, possible scheduling, etc.

CREATE DATABASE DBADMIN
GO

USE [dbadmin]
GO

SET ANSI_NULLS ON
GO   
SET QUOTED_IDENTIFIER ON
GO    
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BlockingProcesses]
(
    [PK] [int] IDENTITY(1,1) NOT NULL,
    [last_batch] [datetime] NULL,
    [spid] [int] NULL,
    [BlockedTotal] [int] NULL,
    [LoginName] [varchar](128) NULL,
    [DBName] [varchar](128) NULL,
    [HostName] [varchar](128) NULL,
    [Program_Name] [varchar](255) NULL,
    [CPU] [bigint] NULL,
    [Physical_IO] [bigint] NULL,
    [Memusage] [bigint] NULL,
    [Open_tran] [int] NULL,
    [EventInfo] [varchar](max) NULL,
    [InsertTime] [smalldatetime] NULL,
    [Params] [varchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]    
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlockingProcesses] 
   ADD DEFAULT (getdate()) FOR [InsertTime]
GO

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp__Maint_BlockWatch]
AS
    declare @blocker smallint

    DECLARE blocker_cursor CURSOR FOR 
         select distinct blocked 
         from sysprocesses 
         where blocked != 0

    OPEN blocker_cursor
    FETCH NEXT FROM blocker_cursor INTO @blocker

    WHILE (@@fetch_status &lt;&gt; -1)
    BEGIN
      IF (@@fetch_status = -2)
      BEGIN
  FETCH NEXT FROM blocker_cursor INTO @blocker
           CONTINUE
      END

  exec sp__Maint_Blockingprocesses @blocker

 FETCH NEXT FROM blocker_cursor INTO @blocker
    END
DEALLOCATE blocker_cursor


GO

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp__Maint_BlockingProcesses]    Script Date: 09/20/2016 13:31:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE procedure [dbo].[sp__Maint_BlockingProcesses]
@spid smallint
as
declare
@blockedTotal int, 
@loginame varchar(128), 
@dbid int,
@hostname varchar(128),
@program_name varchar(128),
@cpu bigint,
@physical_io bigint,
@memusage bigint,
@open_tran int,
@dbname varchar(128),
@EventInfo varchar(255),
@Last_batch datetime
create table #temp
(
 EventType varchar(50),
 Parameters int,
 EventInfo varchar(255)
)

insert into #temp
exec ('dbcc inputbuffer('+@spid+')')

select @EventInfo=eventinfo from #temp
select @blockedTotal= count(*) from sysprocesses where blocked = @spid
select @loginame=loginame, @dbid=dbid, @hostname=hostname, @program_name=program_name, @cpu=cpu,
@physical_io = physical_io, @memusage=[memusage], @open_tran=open_tran, @Last_batch=last_batch
from sysprocesses where spid=@spid
select @dbname = name from sysdatabases where dbid=@dbid
insert into dbadmin..blockingprocesses(last_batch,spid, BlockedTotal, LoginName, DBName, HostName, Program_Name, CPU, Physical_IO, Memusage, Open_tran, EventInfo)
values(@last_batch, @spid, @BlockedTotal, @LogiName, @DBName, @HostName, @Program_Name, @CPU, @Physical_IO, @Memusage, @Open_tran, @EventInfo)
drop table #temp



GO



USE [msdb]
GO

/****** Object:  Job [_Monitor Blocks]    Script Date: 09/20/2016 13:29:34 ******/
BEGIN TRANSACTION

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/20/2016 13:29:34 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'_Monitor Blocks', 
        @enabled=1, 
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [Look For Blocking]    Script Date: 09/20/2016 13:29:34 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Look For Blocking', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=1, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec sp__Maint_BlockWatch', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Once a Minute', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=1, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20060918, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959
        --, @schedule_uid=N'bf518f11-c5ba-438a-8afd-e4e33e8dad1e'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:

GO