Solved

Access form/table based on SQL Table Insert record returning incorrect/wrong data

Posted on 2009-05-20
24
797 Views
Last Modified: 2013-11-28
I have a number of access databases with this fault.  When a user enters a new record onto a form or table once the insert is applied to the table different record is displayed.   The correct record is inserted into the table.  This is very confusing for the users.  If they close the form and open it again the correct data is displayed but same thing happens on next insert.

All the databases are developed in Access 2003 and could be either 2000 or 2003 format.
SQL Server 2005 hosts source database, this affects different databases only common thing is the same server and all use Merge replication.  This fault occurs at the Publisher and all effected tables have Int Identity columns managed by replication.
I am using Simple ODBC linked tables using trusted connection
This effects Different client computers ie Windows XP, Vista, Citrix and Different user accounts ie Administrators and normal users affected.

Once a database is affected the only fix is to recreate the client front end from scratch and if lucky (1 chance out of 10)  the fault goes away for a period of time.  I have resorted to recreating the replicated (back end) databases a number of times and the fault disappears for a while but soon comes back.

I have attached 2 screen shots to demonstrate this.  In this example the record was inserted into the table with an id of 136181.
beforeinsert.JPG
afterinsert.JPG
0
Comment
Question by:klilley
  • 11
  • 9
24 Comments
 
LVL 39

Expert Comment

by:thenelson
ID: 24458212
I understand your problem that the records do not display correctly in your form after a new record is inserted.

Quick solution, try:

Private Sub Form_AfterInsert()
Me.Requery
End Sub

Otherwise it might be a problem with the record source query. Try posting it.
0
 
LVL 4

Author Comment

by:klilley
ID: 24471245
Thanks for the response but this is also effecting inserts directly into an ODBC linked table.  Which is where my screenshots/example were from.

If i run a requery it just refreshes the "wrong" record
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24471761
The reason I suggested requering the form is because you said: "If they close the form and open it again the correct data is displayed but same thing happens on next insert." As for as the connection' to the data, requering the form is the same thing as closing and opening it.

Rereading your question, I see you are giving your users direct access to tables.  Is the problem with specific tables? Check the table properties for unusual values. Try running
truncate table <tableName>
(back up db first) It this fixes the problem, it is probably a concurrency problem. This is further suspected since your new record ID is 6 digits and all the others in your images is 4 or less digits. Assuming your auto number field is set for increment and you don't have about 136181 records in the table. These are problems that occur when multiple users have direct table access through an Access FE.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:klilley
ID: 24472733
looking here http://msdn.microsoft.com/en-us/library/ms177570.aspx truncate table deletes all the rows from the table. How will this help?  In this case I could understand reseeding the ID field, but in the other tables and databases that are affected this ID could be a foreign key.  

Normally the users do not have direct access to the linked table, I only tried that to eliminate the form being the potential problem.  There are not 131,000 records you are correct, this number has incremented due to identity management during merge replication and being forced to recreate the publication and subscriptions a large number of times.

I am starting to think this must be a bug in SQL, Access or ODBC in relation to Identity range management as I am also getting a number of "Cannot Insert duplicate Key" error messages across the organisation
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24474219
>I am starting to think this must be a bug in SQL<

You may be right. What SQL 2005 service pack do you have?  Take a look at:

http://support.microsoft.com/kb/936305
Cumulative update package 2 for SQL Server 2005 Service Pack 2
FIX: Error message when you run a query in SQL Server 2005: "Cannot insert duplicate key row in object <TableName> with unique index <IndexName>"

http://support.microsoft.com/kb/939537
Cumulative update package 3 for SQL Server 2005 Service Pack 2
FIX: Error message when you try to update the index key columns of a non-unique clustered index in SQL Server 2005: "Cannot insert duplicate key row in object 'ObjectName' with unique index 'IndexName'"
0
 
LVL 4

Author Comment

by:klilley
ID: 24474316
I am already running SP3 v9.00.4220.00 Standard Edition.  I couldnt find any updates post SP3 on the MS website
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24474380
As you can guess, I have been grasping at straws to try to help you.

Since you said closing and opening the form displays the correct records, does pressing SHIFT+F9 with the form having focus display the correct records?
0
 
LVL 4

Author Comment

by:klilley
ID: 24474418
Tell me about it lol

Shift F9 just moved to a new/blank record.  What is this the shortcut key for?
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24474489
If requeries the form's record source property.  Was the form displaying the wrong records when you used it?

Another possibility: Change the form's record source property while in form view mode. You can copy it, delete it and paste it back in.
0
 
LVL 4

Author Comment

by:klilley
ID: 24474622
yes the form was displaying the wrong records.

This is what happened
1) open form in data entry mode
2) typed test info
3) clicked into subform and the main form's record changed to a different record

The only events on the form are Current (requeries a combo box) and on open which maximizes the form, but as I touched upon earlier this happens when directly entering data into the linked tables where no code could be affecting it. Out of interest if I enter info directly to SQL through the SQL Management Studio everything works fine.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24475290
>the main form's record changed to a different record<

So it is the main form that displays the wrong record when you enter a new record in the subform?

Try this in the subform's after insult event:

Private Sub Form_AfterInsert()
    Me.Parent.Requery
    Me.Requery
End Sub

You might need a second between the two requeries to allow the fist one to complete. In that case, use:

Private Sub Form_AfterInsert()
    Me.Parent.Requery
    Me.TimerInterval = 1000
End Sub

Private Sub Form_Timer()
    Me.TimerInterval = 0
    Me.Requery
End Sub

From access help for the after insert action:
"You can use an AfterInsert event procedure or macro to requery a recordset whenever a new record is added."

0
 
LVL 4

Author Comment

by:klilley
ID: 24480951
No I was trying to enter a record into the main form and once the record is saved to the table a different record is returned.  This could happen when moving to a subform, clicking Save Record or running Docmd.acmdsaverecord, requery etc basically anything that commits the changes to the underlying table.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24485872
Did the code help?
0
 
LVL 4

Author Comment

by:klilley
ID: 24491117
Sorry forgot to post up before that it still displays the wrong record
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24493491
I don't have any other ideas. I'll request an alert for this question to see if we can get others to comment.
0
 
LVL 4

Author Comment

by:klilley
ID: 24493528
Thank you for your help :)

I have been running some tests on our system and found that this only effects databases on one of our SQL Server 2005 servers.  The databases are replicated across the organisation and the other servers do not exhibit the same issue.

0
 
LVL 39

Expert Comment

by:thenelson
ID: 24495208
If the problem is in the server, it is strange that recreating the client front end from scratch would help.

Does copying the db from another server resolve the problem?
0
 
LVL 4

Author Comment

by:klilley
ID: 24497166
Sometimes recreating the FE fixes the problem which is strange and I think is more fluke than anything else.

I used exactly the same front end in the last test but just re-directed the table links to a remote server.  This way the only difference was the backend database/server.  The only other difference is that one is a subscriber and the other the SQL database publisher.
0
 
LVL 4

Author Comment

by:klilley
ID: 24497171
the backend server exhibiting this strange behavour is the publisher
0
 
LVL 4

Accepted Solution

by:
klilley earned 0 total points
ID: 24611006
It appears the fault is caused by access.  After the record is inserted into the table access requests @@identity from the server to return the inserted data.  When the table is replicated this value can return the identity from different table full details and a better explanation are
http://jagbarcelo.blogspot.com/2006/06/problems-with-identity-fields-in-ms.html

a possible solution
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/31c1dd4693ca321b/93ec304f6d8e5c4a

and I found a similar question on EE
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23977096.html?sfQueryTermInfo=1+access+insert+record+replic+sql

In the end I had to edit the merge triggers on the effected replicated tables using SQL from the google groups link above.

I added this at the top of the insert trigger
DECLARE @Identity INT
      DECLARE @strSQL VARCHAR(128)

      --Save @@IDENTITY:
      set @Identity=@@IDENTITY

and this code at the end.
      --Construct the SQL string to reset @@IDENTITY:
      SET @strsql='select identity (int, ' + cast(@identity as
VARCHAR(10)) + ',
      1) as id into #tmp'
      --Execute the SQL:
      EXEC (@strsql)

After a bit of testing and at first glance this seems to rectify the fault.  

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question