Updateable Query problem in Windows 7

  • Thread starter Thread starter DESeibold
  • Start date Start date
D

DESeibold

I'm using Office 2003 in a new windows 7 computer. I copied a database to the
new computer from the server. When I try to run the following query, I get a
"must use updateable query" error.

UPDATE LowStockListQuery1B INNER JOIN NewList3 ON LowStockListQuery1B.number
= NewList3.item SET NewList3.LowStk = Yes;


LowStockListQuery1B :
SELECT stock.distrib, supplier.name, stock.number, stock.desc, stock.low,
stock.reordquant
FROM stock LEFT JOIN supplier ON stock.distrib = supplier.code
WHERE (((stock.distrib)=[Forms]![LLPage]![Combo8]) AND ((stock.low)<2) AND
((stock.reordquant)<2) AND ((stock.cantsell)=0) AND ((stock.construct)=0) AND
((stock.break_out)=0) AND ((stock.nonproduct)=0) AND ((stock.discont)=0))
ORDER BY stock.number;

This works well on all of my XP and 2000 computers, so I figure it has
something to do with Windows 7. Any ideas?
 
I'm using Office 2003 in a new windows 7 computer. I copied a database to the
new computer from the server. When I try to run the following query, I get a
"must use updateable query" error.

UPDATE LowStockListQuery1B INNER JOIN NewList3 ON LowStockListQuery1B.number
= NewList3.item SET NewList3.LowStk = Yes;


LowStockListQuery1B :
SELECT stock.distrib, supplier.name, stock.number, stock.desc, stock.low,
stock.reordquant
FROM stock LEFT JOIN supplier ON stock.distrib = supplier.code
WHERE (((stock.distrib)=[Forms]![LLPage]![Combo8]) AND ((stock.low)<2) AND
((stock.reordquant)<2) AND ((stock.cantsell)=0) AND ((stock.construct)=0) AND
((stock.break_out)=0) AND ((stock.nonproduct)=0) AND ((stock.discont)=0))
ORDER BY stock.number;

This works well on all of my XP and 2000 computers, so I figure it has
something to do with Windows 7. Any ideas?
Click to expand...

I doubt that it's Windows 7 (I certainly hope not, it would be a dreadful bug
if so!); I suspect instead that in transferring to or upgrading your computer
you've somehow lost or corrupted an index. This will be updateable if
Code: 
is the Primary Key of the table Supplier and there is a relationship defined
between [distrib] and [code]; you might want to check the table definitions
and the relationship window to see if that has somehow gotten lost.

What version of Access are you using? If you changed versions that's a more
likely cause than changing OS - A2007 is much starchier about some things than
earlier versions. In particular, it's possible that "code" is a reserved word,
and "name" and "number" certainly are; consider changing these fieldnames, or
at least get in the habit of always putting them in [brackets].
 
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

PERHAPS you can rewrite your UPDATE query. As far as I can tell there is
nothing in the query LowStockListQuery1B that uses the table supplier to
return a field or restrict the records returned.

UPDATE NewList3 INNER JOIN Stock
ON NewList3.Item=Stock.Number
SET NewList3.LowStk=True
WHERE stock.distrib=[Forms]![LLPage]![Combo8]
AND stock.low<2
AND stock.reordquant<2
AND stock.cantsell=0
AND stock.construct=0
AND stock.break_out=0
AND stock.nonproduct=0
AND stock.discont=0

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Nothing seemed to help so I finally just deleted the database and made a new
copy from the server. Everything is working fine now.

I have no idea what caused the problem other than that the stock table was
suddenly non-updateable. That is what caused me to try a fresh copy of the
database.

Thanks for all your help!
 
This is certainly a cleaner version of my query but it didn't solve the
problem.

I finally just deleted the database and made a new copy from the server.
Everything is working fine now.

I have no idea what caused the problem other than that the stock table was
suddenly non-updateable. That is what caused me to try a fresh copy of the
database.

Thanks for all your help!

John Spencer said:
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

PERHAPS you can rewrite your UPDATE query. As far as I can tell there is
nothing in the query LowStockListQuery1B that uses the table supplier to
return a field or restrict the records returned.

UPDATE NewList3 INNER JOIN Stock
ON NewList3.Item=Stock.Number
SET NewList3.LowStk=True
WHERE stock.distrib=[Forms]![LLPage]![Combo8]
AND stock.low<2
AND stock.reordquant<2
AND stock.cantsell=0
AND stock.construct=0
AND stock.break_out=0
AND stock.nonproduct=0
AND stock.discont=0

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I'm using Office 2003 in a new windows 7 computer. I copied a database to the
new computer from the server. When I try to run the following query, I get a
"must use updateable query" error.

UPDATE LowStockListQuery1B INNER JOIN NewList3 ON LowStockListQuery1B.number
= NewList3.item SET NewList3.LowStk = Yes;


LowStockListQuery1B :
SELECT stock.distrib, supplier.name, stock.number, stock.desc, stock.low,
stock.reordquant
FROM stock LEFT JOIN supplier ON stock.distrib = supplier.code
WHERE (((stock.distrib)=[Forms]![LLPage]![Combo8]) AND ((stock.low)<2) AND
((stock.reordquant)<2) AND ((stock.cantsell)=0) AND ((stock.construct)=0) AND
((stock.break_out)=0) AND ((stock.nonproduct)=0) AND ((stock.discont)=0))
ORDER BY stock.number;

This works well on all of my XP and 2000 computers, so I figure it has
something to do with Windows 7. Any ideas?
Click to expand...
.
Click to expand...
 
Back
Top