1

I'm working in an environment that has a lot of apps that are a MS Access 2016 front-end with a SQL Server 2016 back-end.

Symptoms:

  1. When viewing data from a table where a DATETIME column is part of the unique identifier that Access is using, some rows show up with #Deleted# in every column.
  2. When updating a table with a DATETIME column, but the DATETIME is not being updated, the update fails with the error that someone else has modified the row, even though nothing has changed.

Root cause:

Temporary workaround:

  • set database compatibility level to 120 (SQL Server 2014) - problem disappears, but can't use new features, and query performance is affected.

Possible solutions:

  • Changing all DATETIME columns to DATETIME2(3)
  • Add a timestamp column to all tables.

Both possible solutions have solved the issue in testing, but they aren't real practical. That would be a lot of work. I would like to solve this without modifying every table in every database.

I have tried updating ODBC drivers. I have tried rolling back to older drivers. I have also tried older versions of Access.

Code generated by Access for symptom 1:

declare @p1 int
set @p1=11
exec sp_prepexec @p1 output,N'@P1 datetime2,@P2 varchar(50)',N'SELECT "TimeStampKey","UserID"  FROM "dbo"."TurboStaging"  WHERE "TimeStampKey" = @P1 AND "UserID" = @P2','2013-12-13 08:04:02.4200000','dasmith'
select @p1
GO
declare @p1 int
set @p1=12
exec sp_prepexec @p1 output,N'@P1 datetime2,@P2 varchar(50),@P3 datetime2,@P4 varchar(50),@P5 datetime2,@P6 varchar(50),@P7 datetime2,@P8 varchar(50),@P9 datetime2,@P10 varchar(50),@P11 datetime2,@P12 varchar(50),@P13 datetime2,@P14 varchar(50),@P15 datetime2,@P16 varchar(50),@P17 datetime2,@P18 varchar(50),@P19 datetime2,@P20 varchar(50)',N'SELECT "TimeStampKey","UserID"  FROM "dbo"."TurboStaging"  WHERE "TimeStampKey" = @P1 AND "UserID" = @P2 OR "TimeStampKey" = @P3 AND "UserID" = @P4 OR "TimeStampKey" = @P5 AND "UserID" = @P6 OR "TimeStampKey" = @P7 AND "UserID" = @P8 OR "TimeStampKey" = @P9 AND "UserID" = @P10 OR "TimeStampKey" = @P11 AND "UserID" = @P12 OR "TimeStampKey" = @P13 AND "UserID" = @P14 OR "TimeStampKey" = @P15 AND "UserID" = @P16 OR "TimeStampKey" = @P17 AND "UserID" = @P18 OR "TimeStampKey" = @P19 AND "UserID" = @P20','2013-12-13 08:04:02.4200000','dasmith','2013-12-13 08:05:54.7700000','dasmith','2013-12-13 08:20:26.8400000','dasmith2','2013-12-13 08:47:57.5830000','dasmith2','2013-12-13 08:58:41.6230000','dasmith2','2013-12-13 09:11:53.9830000','dasmith2','2013-12-13 09:15:24.4300000','dasmith','2013-12-13 09:16:58.6000000','dasmith2','2013-12-13 09:26:20.1600000','dasmith2','2013-12-13 09:48:19.9300000','dasmith'
select @p1
GO
-- Repeat the following pattern
exec sp_execute 12,'2013-12-13 09:49:48.2830000','dasmith2','2013-12-13 10:04:35.8370000','dasmith2','2013-12-13 10:19:46.5100000','dasmith2','2013-12-13 10:21:26.4570000','dasmith','2013-12-13 10:22:16.1170000','dasmith','2013-12-13 10:26:01.4470000','dasmith','2013-12-13 10:27:08.5300000','dasmith','2013-12-13 10:29:18.4730000','dasmith','2013-12-13 10:31:26.5200000','dasmith2','2013-12-13 10:31:35.9200000','dasmith'

Code generated by Access for symptom 2 (please note only the POSTED column was to be updated):

exec sp_executesql N'UPDATE "dbo"."BATCH" SET "POSTED"=@P1  WHERE "BATCH_NO" = @P2 AND "BATCH_DATE" = @P3 AND "USER_ID" = @P4 AND "DEPOSIT" = @P5 AND "POSTED" = @P6 AND "TYPE" = @P7 AND "MasterTag" IS NULL AND "AutoBanked" = @P8',N'@P1 bit,@P2 int,@P3 datetime2,@P4 varchar(25),@P5 bit,@P6 bit,@P7 varchar(4),@P8 bit',0,91657,'2010-11-10 08:50:28.6570000','jadoe',0,1,'W',0
  • What driver do you use (there are 3: ODBC Driver for SQL,Server, SQL Server and SQLServer Native Client). First Symptom can be solved by adding an interger with autoincrement as PK. Second by adding a Timestamp/RowVersion what is recommended for Ms Access using ODBC. Shouldn't be a big deal adding those fiels with a script. Have a look at Optimizing Microsoft Office Access Applications Linked to SQL Server – ComputerVersteher Jul 21 '18 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.