Ask a new question

Error 7787 on second attempt to change record via form bound to DSN data source

Access 2010

 

My application reads and updates data in a a MySQL DB via a MySQL 5.1 DSN. I can browse to a record directly via the linked table, make changes directly in the table, save the changes by moving to another record, move back, make more changes, and save the new changes. All continues to work fine when I make a change to a bound field on a bound form, close my form, and the record is saved. The new data is there when I look it up via my linked table and even if I look at the data directly in the MySQL DB via PHPMyAdmin (online management tool for MySQL databases).

However, when I then re-open the form, make another change, and again close the form, I get error 7787--data has been updated by another user. This error persists for the affected record even after I entirely close and re-open my Access app. In fact, I now get the same 7787 error when attempting to change any field in this record directly in the linked table.

I was able to "unlock" this by going directly to the SQL processor in PHPMyAdmin and running an update statement affecting the record.

 

As a test, I resorted to forcing a save of the record immediately after making the change on the form by constructing the SQL statement and running it through like this:

 

Dim strSQL as String

strsql = " UPDATE address SET latitude = 47.845123372972, longitude = -122.282912051305 WHERE address_id = 15"

CurrentDb.Execute strSQL, dbFailOnError

 

This works; however, I then have to cancel my changes on the bound form, or it generates the same 7787 error because the record has, indeed, been changed since I navigated to the current record.

 

I really want the simplicity of using a bound form here. Any ideas on what is happening here?

Hi Brian,

 

I’m not sure if the KB article below shows the same error you are receiving?  If it is, you could try adding a timestamp field to your underlying tables and then relink the tables from within Access to see if this helps.

 

280730            You may receive write conflict errors when you update records of a linked SQL Server table in Access 2000

http://support.microsoft.com/default.aspx?scid=kb;EN-US;280730

 

If that doesn’t help, you could take a look at the this thread on the MySQL site to see if that helps.  There are several responses on this thread with what appear to be a couple different resolutions (most seem similar to adding a timestamp field in the MySQL table).

 

Best Regards,

Nathan O.

Microsoft Online Community Support

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi, Nathan.

 

There is more than a timestamp issue here.

In short, if I populate a control programmatically with a Double value, then close the form, the next attempt to update the record the same way fails. If, however (as I just did in testing), after the values are set in VBA, I simply higlight the value in each the control, then Copy & Paste to place the exact same value back into the control before closing the form, there is no 7787 on the next update attempt.

How can the content copied TO the clipboard vary from the content pasted FROM it? Unless, of course, there is more to the content of the text box than is available visually... Are there perhaps new issues in Access 2010 surrounding the actual value of double-precision numbers?

More application-specific info below, but it may be superfluous:

This is a mapping application in which the user opens my form and selects customer record, whereupon I send the address to MapPoint in VBA, where it is located on the map. The user then clicks OK to copy the coordinates (latitude & longitude) derived from the point on the map to two text boxes on my form, bound to two fields in the MySQL database. I set the value of the two controls thus in VBA:

 

Latitude = PushPinCurrent.Location.Latitude
Longitude = PushPinCurrent.Location.Longitude

If I then close my form, re-open it, change anything, then then attempt to close the form, I get the 7787 error (and forever thereafter until I run an update statement on the record directly in PHPMyAdmin on the MySQL server).

Both fields in the MySQL database are double, as is the data type of the Latitude property being derived from MapPoint.

I will post my questio on MSDN instead.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated May 1, 2023 Views 1,856 Applies to: