Access form/table based on SQL Table Insert record returning incorrect/wrong data
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
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
ASKER
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
If i run a requery it just refreshes the "wrong" record
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.
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.
ASKER
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
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
>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'"
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'"
ASKER
I am already running SP3 v9.00.4220.00 Standard Edition. I couldnt find any updates post SP3 on the MS website
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?
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?
ASKER
Tell me about it lol
Shift F9 just moved to a new/blank record. What is this the shortcut key for?
Shift F9 just moved to a new/blank record. What is this the shortcut key for?
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.
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.
ASKER
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.
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.
>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."
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."
ASKER
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.
Did the code help?
ASKER
Sorry forgot to post up before that it still displays the wrong record
I don't have any other ideas. I'll request an alert for this question to see if we can get others to comment.
ASKER
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.
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.
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?
Does copying the db from another server resolve the problem?
ASKER
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.
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.
ASKER
the backend server exhibiting this strange behavour is the publisher
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.