none
Troubles inserting new records in linked SQL-server table in Access

    Question

  • This question was also asked by me at stackexchange. But as I got no aswer, Microsoft would be where to ask.

    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?

    Monday, February 11, 2019 9:45 AM

All replies

  • That behavior us normally a problem on the Access side, start by relinking the table. Maybe the primary key information is incorrect.

    Monday, February 11, 2019 12:08 PM
  • Thank you for the answer. I did relink. Even used another ODBC driver. I did this in two different versions of Microsoft Access. Only time I saw some effekt was by recreating the tables on the SQL-server.
    Monday, February 11, 2019 12:18 PM
  • hmm, run DBCC CHECKIDENT on the table. Also check whether there is a trigger active on that table.

    Monday, February 11, 2019 12:23 PM
  • So DBCC CHECKIDENT ('T_Proeve_ImpMill');

    Gives me:

    Checking identity information: current identity value '5112', current column value '5112'.

    That is also according to what i see in the table. When a new record is added as shown in the screen capture here: http://idea2action.dk/images/Access_trouble.mp4 the new record is in fact added on SQL server with the correct key value. If I requery the table the new record appears.


    Monday, February 11, 2019 12:45 PM
  • So DBCC CHECKIDENT ('T_Proeve_ImpMill');

    Gives me:

    Checking identity information: current identity value '5112', current column value '5112'.

    That is also according to what i see in the table. When a new record is added as shown in the screen capture here: http://idea2action.dk/images/Access_trouble.mp4 the new record is in fact added on SQL server with the correct key value. If I requery the table the new record appears.


    Hi Allan,

    Thanks for your reply.

    Per your recent replies, it seems that the issue is more likely related to access configuration, not from SQL Server.

    Based on my research, you need to read the two articles which is covered with retrieving identity or auto number values, maybe you would  find out how to figure out that issue.

    Retrieving Identity or Autonumber Values

    Getting an AutoNumber Value from Microsoft Access

    Also, you could submit your issue to Microsoft Access forum for professional support:https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=accessdev

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 12, 2019 6:23 AM
    Moderator
  • Hi Will
    Thank you for your reply. That was a long reading. Fortunately, it is all well-known stuff to me.
    Consider these facts from my original post:
    1) This solution has been working for years. The tables were upsized from Access to SQL Server in 2012. This error just began to appear a couple of week ago.
    2) In the create statement for the table both primary key constraint as well as identity property for the primary key is set.
    3) Both the Access version and the ODBC driver has been swapped to isolate the error.
    4) If the table are recreated, the problem goes away. But apparently not for good.
    Nothing but best practice has been used in this solution.
    The solution I have implemented now is to add a record, not by MS Access standard method, but by executing an insert sql-statement via the connection object. Requery the form in Access, and scroll to the newest record. The users now have to click a button to add a new record instead of just entering it in the bottom of the table as they are used to. Not the best user experience.
    Best regards
    Allan

    Wednesday, February 13, 2019 10:13 AM
  • Did you test on different Access versions and Windows versions? Cause the we had some troubles with Access/Office updates as well with Win 10 updates.

    To correctly isolate the error: If it happens again, run an ODBC trace and analyze it.

    If it is an ODBC error, you need to open a support ticket with MS.

    If it is an Access error, then you test first in a new database to exclude corruption. Then test under different Access/Windows configurations.

    Wednesday, February 13, 2019 11:50 AM