Share via

MS Access application Write Conflict behavior change using MSODBCSQL17.DLL (2017.172.00.01) driver when connecting to SQL2017; Bug?

Anonymous
2019-01-08T23:26:43+00:00

This situation is consistently reproducible on MS Access 2010 (x32) and MS Access 2016 (x32) on Windows 7 or Windows 10 environments.  I have an existing MS Access application (with SQL Server backend, linked tables) that works as desired using SQLSRV32.DLL to connect to SQL.  I am trying to change my application to a dll that will support TLS 1.2. I have successfully tried SQLNCLI11.dll or MSODBCSQL13 and 17 when connecting to SQL 2010,2012,2014 and 2016 but the application generates an error when working with a SQL 2017 database.

I have a SQL table:

CREATE TABLE [dbo].[AppUser](

 [InternalID] [int] IDENTITY(1,1) NOT NULL,

 [UserName] varchar NULL,

 [PW] varchar NULL,

 [SecLevel] [smallint] NULL,

 [LastLogin] [datetime] NULL,

 [CheckRelationships] [bit] NULL,

 CONSTRAINT [PK_AppUser] PRIMARY KEY CLUSTERED

(

 [InternalID] ASC

) ON [PRIMARY]

) ON [PRIMARY]

My application has a linked table (DSN using MSODBCSQL17.dll) to the SQL AppUser table and a bound form (AppUser2017) to this table.  The main screen has a button which opens the bound form "hidden" specifying the ID (primarykey) as a filter.  The LastLogin field is then updated to the current date and then the bound form is closed.  When the bound form is closed, I get the popup "Write Conflict" This record has been changed by another user since you started editing it.  My only options are Copy to Clipboard or Drop Changes.

The only code on my main form is:

Private Sub Command0_Click()

    DoCmd.OpenForm "AppUser2017", acNormal, "", "[InternalID] = 268380106", , acHidden

    Forms!AppUser2017!LastLogin = Now()

    DoCmd.Close acForm, "AppUser2017"

End Sub

Can anyone explain this?  I could probably hack away to get rid of the error but it seems like a bug since all other versions of SQL Server do not have this issue.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
2019-01-09T01:14:32+00:00

I see the same problem with SQL Server Native Client 11 driver. Hmmm, that was unexpected.

But then I remembered that in some cases Access needs a timestamp column to be able to make sense of which record is being edited. I added that column and both drivers worked fine.

Was this answer helpful?

4 people found this answer helpful.
0 comments No comments

13 additional answers

Sort by: Most helpful
  1. Anonymous
    2019-01-11T02:12:15+00:00

    I rent a sqlserver database on cloud and have never found the problem you meet.

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2019-01-09T14:02:04+00:00

    I tried all the drivers I could find and there were no issues with my application until I tried them with SQL2017.  Our application is very large and we support SQL 2008 and up.  Do you think this is a bug that could possibly be fixed in the future with a newer driver?  I don't need to switch off sqlsrv32.dll yet and have concerns about what else might not work.

    I was also going to try a x64 bit version of Access with the x64 bit drivers to see if it behaved differently but right now I don't have an environment I can install that.  If anyone happens to test that, please comment. 

    Thank you.

    Was this answer helpful?

    0 comments No comments
  3. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2019-01-09T00:40:24+00:00

    I just installed SQL2017 and I am seeing the same problem. Digging in a bit more...

    Was this answer helpful?

    0 comments No comments
  4. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2019-01-09T00:02:28+00:00

    To test a bit more what's going on, I would create new db, and link this table.

    In Access table design view, is the PK symbol on InternalID column visible? If not, there is your problem.

    Assuming yes, create an auto-form based on this table. Open it. Can you navigate to a new record? Can you edit an existing record?

    BTW, you did not really want all these fields be nullable, right?

    Was this answer helpful?

    0 comments No comments