Years ago, I upsized the back-end database from Access to SQL-server using the build in tool in Access.
It worked fine but in the recent two weeks an error began to appear. This problem occurs for several tables.
A table is opened in Access to add a new record. Then at the new empty record at the bottom of the table, a value is entered to a field. If the record above is selected the content of the row of the new record immediately change to the content of an old record.
See a screen capture here: http://idea2action.dk/images/Access_trouble.mp4 First column is an identity autonumbering field. In the screen capture you will see Access fetching an old record.
This goes for Access 2010 as well as Access 2016 as front-end application. I also switched the ODBC driver to another version without doing any difference.
The SQL server where this problem appeared is version 12. I could recreate this problem, by upsizing the original Access-backend to another version 14 SQL server. I then created a new version of the table using script table tool in SQL server management studio. I removed all extended properties from the script before creating the new version of the table. Then I inserted all records from the old table into the new table. I swapped the old and new table and the problem was gone in the Access front-end.
In the script below I treat a table named T_Proeve_ImpMill:
CREATE TABLE [dbo].[T_Proeve_ImpMill1]( [Prøvenr] [int] IDENTITY(1,1) NOT NULL, [Tidspunkt] [datetime] NULL, [Sign] [nvarchar](50) NULL, [RKV] [real] NULL, CONSTRAINT [T_Proeve_ImpMill_PK] PRIMARY KEY NONCLUSTERED ( [Prøvenr] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO set IDENTITY_INSERT [T_Proeve_ImpMill1] on; insert into [T_Proeve_ImpMill1] ( [Prøvenr] ,[Tidspunkt] ,[Sign] ,[RKV] ) SELECT [Prøvenr] ,[Tidspunkt] ,[Sign] ,[RKV] FROM [dbo].[T_Proeve_ImpMill] GO exec sp_rename [T_Proeve_ImpMill],[T_Proeve_ImpMillOld] exec sp_rename [T_Proeve_ImpMill1],[T_Proeve_ImpMill] set IDENTITY_INSERT [T_Proeve_ImpMill] off;
Happily, I implemented this solution on my customers server. The day after the customer reported, that the problem still exists.
Has anybody observed similar problem? Are there any explanations for this behavior? How should I fix it?