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
Improve this question
1
  • 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, 2018 at 10:58

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.