Large updates become much easier to perform when you understand some of the basic principles of database design.
An Access database is not a file in the same sense as a Microsoft Office Word 2007 document or a Microsoft Office Power Point 2007 slide deck.
However in many (probably most) cases, the performance advantage with instead of an empty string, i.e., "", because the memory for the named constant is already allocated.
This doesn't matter in most cases, but in a loop it might, so getting in the habit insures you'll get it right when it does matter.
On some Microsoft Access queries, I get the following message: Operation must use an updatable query. I work around it by using temporary tables, but I'm wondering if there's a better way. Here's the code: Since Jet 4, all queries that have a join to a SQL statement that summarizes data will be non-updatable. I've also had to use temp tables to get around it (pull the aggregated value into a temp table with an insert statement, then join to this table with your update, then drop the temp table).
You aren't using a JOIN, but the WHERE clause is exactly equivalent to a join, and thus, the Jet query optimizer treats it the same way it treats a join. :-))I don't believe your assertion that the SQL you quote could ever produce the "query too complex" error in Jet. Glenn suppose you are stored the database (MS-Access file) in read only folder, while running your application the connection is not force-fully opened.
In turn, those design principles affect how you enter data.
Remember these facts about database objects and design as you proceed.
In addition, those objects must adhere to a set of design principles or the database will either work poorly or fail altogether. LGAName = [tbl_Prop_LGA].[LGA_NAME] where tbl_LOT_VALUATION_DETAILS. LGAName is null; The problem defintely relates to the use of (in this case) the max() function. to retrieve the max or min or avg value from a joined table) will cause the error.Normally I'd just make the UPDATE query a passthrough to use the non-JET syntax, but one of the tables I was joining with was a local Access table.Note: If you want to remove a small number of records — any quantity that you feel comfortable deleting by hand — you can open the table in Datasheet view, select the fields or rows that you want to delete, and press DELETE.Use a delete query to remove entire records (rows) from a table or from two related tables, in one operation.