1

I have a MS Access (2016) database using linked tables to a MySQL database. In the access database I have a form I use for data entry. I needed certain fields to be recalculated (manually) when I click a Recalc button.

The problem I am having is that when I run the VBA code to update fields on the form, if I then try to navigate to another record I get the error "This record has been changed by another user since you started editing it...."

I am the only user accessing this database. Everything works fine if I DON'T update a bound field on the form. Once I do, then I get that error when navigating to the next record.

Here is my vba code for the Recalc button:

Private Sub Recalculate()
    vendorID = Me.product_supplier_id
    supplierID = "supplier_id=" & vendorID

    supplierHandling = Me.product_handling
    vendorFee = Me.product_vendor_fee
    supplierMarkupPercent = DLookup("supplier_markup_percent", "suppliers", supplierID)
    supplierMarkupFixed = DLookup("supplier_markup_fixed", "suppliers", supplierID)

    productCost = Me.product_cost
    productShipping = Me.product_shipping
    totalCost = productCost + productShipping + supplierHandling
    totalCost = totalCost + vendorFee
    markup = supplierMarkupFixed + (totalCost * supplierMarkupPercent)
    productPrice = (totalCost + markup) / 0.85
    amzFee = productPrice * 0.15
    totalCost = totalCost + amzFee
    profit = productPrice - totalCost

    Me.product_total_cost = totalCost
    Me.product_price = productPrice
    Me.product_profit = profit

    SetPriceColor
End Sub

The 3 statements near the end (before the SetPriceColor) are the culprits.

I am not sure how to resolve this issue. I have combed through many google searches, but nothing jumps out at me a solution for this specific case.

Andre
  • 26,751
  • 7
  • 36
  • 80
Walter Zydhek
  • 293
  • 4
  • 10
  • Usually best not to save calculated aggregate data. Calculate when needed. So Recalculate procedure does not error - error occurs when moving to another record. How do you do this navigation? Not seeing anything in this code to cause issue. But, just for grins, commit record to table and make sure it is not in edit mode. Several ways to do that. Try: `If Me.Dirty Then Me.Dirty = False` – June7 Jun 20 '20 at 03:08
  • I have tried using the Me.Dirty and still have the problem. I call the Recalculate routine from my Private Sub cmdRecalc_Click(). I added the ```If Me.Dirty Then Me.Dirty = False``` before and after calling the Recalculate routine. the one before works fine, the one after gives the same error. – Walter Zydhek Jun 20 '20 at 04:19
  • I navigate records using the access forms record navigator at the bottom of the form. – Walter Zydhek Jun 20 '20 at 04:23
  • I tried to replicate issue and can't. I have button modify a field value then I navigate to next record. No problem. Is there code behind other controls? – June7 Jun 20 '20 at 04:41
  • No. Could the issue be due to my linked tables instead of data stored in access itself? – Walter Zydhek Jun 20 '20 at 04:43

2 Answers2

2

Yes, the issue is due to linked ODBC tables. Plus floating point number columns, which can cause problems when Access checks whether your changes in the bound form (be it by VBA or manually) conflict with the previous version of the saved record.

The solution should be to add a TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP to your table.

From here:

ALTER TABLE myTable
ADD COLUMN updated_at 
  TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
  ON UPDATE CURRENT_TIMESTAMP;

See these questions:

Write Conflict messages suddenly start happening in ODBC linked tables

Does MySQL have an equivalent of SQL Server rowversion?

For tables linked from SQL Server, adding a ROWVERSION column definitely fixes the issue. For MySql (and its ODBC driver) it should work, and it did work here.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • In MySQL I am using DECIMAL(13,2) for the datatype (for the fields in question). In VBA I am not using datatypes for any of the variables which are used to assign values to the fields. – Walter Zydhek Jun 20 '20 at 17:19
  • I added a timestamp column to my table, refreshed the schema for the table in access, added the timestamp field to my form (hidden). Still the same issue. Was there something else I needed to do regarding the timestamp field, in the form or VBA? – Walter Zydhek Jun 20 '20 at 17:31
  • Ugh. Does the auto-updating of the column work? Just in case, I would delete the linked table, and re-link from scratch, instead of refreshing. @WalterZydhek – Andre Jun 20 '20 at 17:34
  • DIM'd totalCost, productPrice and profit (the 3 variables being stored back in the fields) as Currency. Still not working. – Walter Zydhek Jun 20 '20 at 17:35
  • `Currency` stores 4 decimal places. Is changing the datatype to `DECIMAL(13,4)` possible? Alternatively, you can try rounding all calculations to 2 decimal places. – Andre Jun 20 '20 at 17:38
  • I tried just manually updating 1 field (manually) and moving to the next record. no change of the timestamp field. You mentioned '''ON UPDATE CURRENT_TIMESTAMP'''. am I supposed to do that in MySQL or in VBA? If in MySQL, how do I set that. I am using MySQL Workbench and do see where I set an ON UPDATE. – Walter Zydhek Jun 20 '20 at 17:38
  • See https://www.mysqltutorial.org/basic-mysql-tutorial-aspx/understanding-mysql-timestamp/ . `ALTER TABLE categories ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;` – Andre Jun 20 '20 at 17:40
  • Ahh, that fixed it. Thank you so much! – Walter Zydhek Jun 20 '20 at 17:44
0

When you have a linked SQL database to an Access Database there are a few things that you need to make sure are in place.

On the SQL side of things, the SQL table must have a primary key and a Timestamp field where the data type is timestamp.

On the Access side of things, when referencing the tables and using recordsets include dbOpenDynaset and dbSeeChanges. Here is an example:

Dim qry As String
Dim rs As Recordset

qry = "SELECT * FROM yourtable"
Set rs = CurrentDB.OpenRecordset(qry, dbOpenDynaset, dbSeeChanges)

This should stop your error from popping up. Also if you make changes to the SQL table, the Access database will not always catch these changes so you will want to refresh your connections using the Linked Table Manager.

bigjoepro75
  • 49
  • 3