Update queries not working in Access 2010

breauxlg
breauxlg used Ask the Experts™
on
I converted an access 2003 database to 2010. The database worked under access 2003 as well as 2007, but I was having so many problems with it as an mdb under 2010 that I converted it. I am having a world of trouble. I have some simple update queries that just don't work. They run without error messages, but don't update the object table. Here is the SQL from one of them. Is there some property somewhere that will keep an update query from udpating? Trust center stuff? I can go into the table and manually key numbers into the quantity field I'm trying to update, and it keeps them, so the table isn't read-only, but the update query does not update. Thanks in advance for any help.

UPDATE FinInvProductItemBalance INNER JOIN POFinInvProdItemNewBal ON (FinInvProductItemBalance.[ColorID] = POFinInvProdItemNewBal.[ColorID]) AND (FinInvProductItemBalance.[TypeID] = POFinInvProdItemNewBal.[TypeID]) AND (FinInvProductItemBalance.[SizeID] = POFinInvProdItemNewBal.[SizeID]) AND (FinInvProductItemBalance.[BrandID] = POFinInvProdItemNewBal.[BrandID]) AND (FinInvProductItemBalance.[LotID] = POFinInvProdItemNewBal.[LotID]) AND (FinInvProductItemBalance.[OwnerID] = POFinInvProdItemNewBal.[OwnerID]) SET FinInvProductItemBalance.Quantity = [POFinInvProdItemNewBal]![NewQuantity], FinInvProductItemBalance.TotalValue = [POFinInvProdItemNewBal]![NewValue], FinInvProductItemBalance.AveragePrice = [POFinInvProdItemNewBal]![NewAverage];
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
So what happens when you run the query?
Do you get the usual messages saying how many records will be updated?  Is it correct as far as you know?

Author

Commented:
Actually, I have the confirm messages turned off.  I'll turn them on and see what it says. Good point.

Author

Commented:
It says I am about to update 22 rows, which is what it should say. I tell it to run and it completes, but the data is not updated.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

I am aware of some update query bugs in A2010, but this doesn't match anything I've heard of.
Are you able to post a file containing the problem?

Author

Commented:
It's a pretty big file. The front-end is around 50 meg and the back end is around 100 meg.  I've created a new pair of tables with a single relationship field and a single field update query and that works, but I have a slew of update queries that work under prior office installs, but not 2010. I don't know if it's a problem with 2010 converting from an mdb database or what. I'd really hate to have to downgrade to 2007 or 2003 to get this working, but I'm starting to get into a time bind. I'll try to create a simple database in 2003 and convert it to 2010 to see if the same behavior arises.

Commented:
I had a similar problem.  First apply the latest and greatest SP to MS Access and Office 2010. Then try again.

Author

Commented:
When I go into windows update, I have no updates for Office. When I go to Office, I don't see a service pack.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
I don't think are are SP's for O2010 yet.

mx

Author

Commented:
I'll bet they are needed, though.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Probably so ... :-)
Commented:
I paid the $99 to Microsoft to open a case. They found it to be a previously undocumented bug in Access 2010. Uploaded it to their system. It failed under Office 2010, worked under Office 2007. I wonder if I'll get a refund since it's their problem.
So what is the key point that is creating the problem (what bug has been identified)?
The query you posted here doesn't look unusual apart from perhaps the number of fields in the join.

Author

Commented:
They have not determined what it is about this query. I wish they would and would give me a fix. I've got a conversion on hold until they do. This system is designed for Shrimp Processors who don't want to look at their product by item number,  but by the different identifying criteria. Size, type, brand, color, lot. I've got these joins throughout the system. I've not tried to make update queries with one, two, three, etc joins to see if it's the number of joins. I've never seen a documented limit to the number of joins in an update, but I'm sure it can't be in the single digits. I had gotten a request to close this question because it was dormant for 3 days, but if the moderators will leave it open, I'll post the Microsoft response when I get it.

Author

Commented:
Let me know if the question should remain open until I get the Microsoft response.
I would like it to stay open for week or so to see if you get an update.

The limit on joins is a mixed limit on there being no more than 32 ( joins plus indexes) present.

Author

Commented:
Still haven't gotten a solution from Microsoft.

Author

Commented:
Here's a way to re-create the problem.

Table 1
TableID                  1
CurrentBalance    50

Table 2
TableID                  1
AddBalance           5

Query1 (select query)
SELECT Table1.TableID, Table1.CurrentBalance, Table2.AddBalance, [CurrentBalance]+[AddBalance] AS NewBalance
FROM Table1 INNER JOIN Table2 ON Table1.TableID = Table2.TableID;

Result: TableID      CurrentBalance      AddBalance              NewBalance
                    1      50                                5                   55


Query2 (update query)
UPDATE Table1 INNER JOIN Query1 ON Table1.TableID = Query1.TableID SET Table1.CurrentBalance = Query1!NewBalance;

In Access 2003 and 2007, after running Query2,Table1 has a CurrentBalance of 55
In Access 2010, after running Query2, Table1 has a CurrentBalance of 50. It says it's going to update 1 row, it runs without errors, but does not update the CurrentBalance field.

If anyone else has 2010, please try this and let me know if you get different results.
nathanielIT Consultant

Commented:
did you solve this already?

alternatively (while waiting for MS Solution), you may want to apply this:

From Query1, convert your Select query to "Make-Table" Query. Table name is "Table3" for example. Save Query1 as Query11
Then on your Query2, instead of using Query1, use Table3 as your record reference. The SQL script should now be:
UPDATE Table1 INNER JOIN Table3 ON Table1.TableID = Table3.TableID SET Table1.CurrentBalance = Table3!NewBalance;
Save this Query as Query12

Now try running Query11 (to create Table3) and Query12 (to update CurrentBalance from Table1)

This alternative method avoids some sort of circular referencing and maintains data integrity.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial