Solved

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

Posted on 2009-05-20
24
807 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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