2

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?

  • Are there triggers anywhere on the database? If so, this might be a cross-site duplicate of this question. After inserting, Access fetches the inserted PK by using SELECT @@IDENTITY and fetching the row for that identity and then displays them. If the global identity value has changed somehow, you get this behavior. You can turn this behavior off for forms, but not for tables. – Erik A Feb 15 at 9:10
  • No, there is no triggers in the database. It is upzised from MS Access by the upzising tool in Access. It consist of simple tables as the one created in the above statement. Nothing else. No triggers, stored procdures or even views. – Allan Bertelsen Feb 16 at 7:20

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.