Solved

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

Posted on 2009-05-20
24
791 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now