By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,691 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Query too Complex

P: 8
Hello All! I have an update query that is rather large and I keep getting a "Query is too complex" error. Does anyone know the limitations on update queries for MS Access 2000? I am running on Windows XP. The query has about 95 actions (updates) for approximately 35 tables. Thank you for the info!

Sloney
Sep 2 '08 #1
Share this Question
Share on Google+
11 Replies


Expert Mod 2.5K+
P: 2,545
I think you have no choice but to break your query down into more manageable components; as you will see from the MS Knowledge Base article linked here, earlier versions of the JET database engine had a limit of 40 fields (now 99 fields) before a 'Query Too Complex' error would occur. The article explains why.

Anyhow, if you need 95 attributes updated it would help maintainability if nothing else to break it down into multiple updates that are run sequentially.

-Stewart
Sep 2 '08 #2

NeoPa
Expert Mod 15k+
P: 31,347
I'm afraid I have no idea what the limitations are, but I would consider breaking this query into multiple queries if I had that many tables to update - or even to include when fewer tables are actually updated.

PS. Stewart nicked in there with a better answer anyway, although it's not too far what I was thinking (phew!)
Sep 2 '08 #3

P: 8
Based on what I read this evening I should be able to run this update query as a Docmd RunSQL. Please straighten me out where I may misspeak or not clearly understand as I am a newbie.

Anyways, based on the MSDN website (http://msdn.microsoft.com/en-us/library/aa141546(office.10).aspx) it appears that "The maximum length of the sqlstatement argument is 32,768 characters (unlike the SQL Statement action argument in the Macro window, whose maximum length is 256 characters)."

My thought is that I should be able to run the desired query. When I brought it into SQL view there are just under 5000 characters. I could break it down but what fun is that! I need to use this query for 20+ databases that share the same table names and fields. If I could limit it to one or two query that would be most suitable. Sooo, it appears that I have some troubleshooting in front of me. Just recently I have tried using the Docmd RunSQL as a module in Access. I am trying to run a test on one table and build from there. Below is what I have and it keep getting a syntax error 3114.

Expand|Select|Wrap|Line Numbers
  1. Public Sub DoSQL()      
  2. Dim SQL As String          
  3.       SQL = "Update S_HYDRAMAPPING" & _     
  4.       "Set S_HYDRAMAPPING.MAPID = '0000'& MAPID"   
  5. DoCmd.RunSQL SQL  
  6. End Sub  
As I said I am a rookie so compiling is very new to me. Any advice would be helpful. Thanks folks!

Sloney
Sep 3 '08 #4

ADezii
Expert 5K+
P: 8,619
Based on what I read this evening I should be able to run this update query as a Docmd RunSQL. Please straighten me out where I may misspeak or not clearly understand as I am a newbie.

Anyways, based on the MSDN website (http://msdn.microsoft.com/en-us/library/aa141546(office.10).aspx) it appears that "The maximum length of the sqlstatement argument is 32,768 characters (unlike the SQL Statement action argument in the Macro window, whose maximum length is 256 characters)."

My thought is that I should be able to run the desired query. When I brought it into SQL view there are just under 5000 characters. I could break it down but what fun is that! I need to use this query for 20+ databases that share the same table names and fields. If I could limit it to one or two query that would be most suitable. Sooo, it appears that I have some troubleshooting in front of me. Just recently I have tried using the Docmd RunSQL as a module in Access. I am trying to run a test on one table and build from there. Below is what I have and it keep getting a syntax error 3114.

Expand|Select|Wrap|Line Numbers
  1. Public Sub DoSQL()      
  2. Dim SQL As String          
  3.       SQL = "Update S_HYDRAMAPPING" & _     
  4.       "Set S_HYDRAMAPPING.MAPID = '0000'& MAPID"   
  5. DoCmd.RunSQL SQL  
  6. End Sub  
As I said I am a rookie so compiling is very new to me. Any advice would be helpful. Thanks folks!

Sloney
These Specifications are for Access 2000
  1. Number of enforced relationships ==> 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
  2. Number of tables in a query ==> 32
  3. Number of fields in a recordset ==> 255
  4. Recordset size 1 gigabyte
  5. Sort limit 255 characters in one or more fields
  6. Number of levels of nested queries ==> 50
  7. Number of characters in a cell in the query design grid ==> 1,024
  8. Number of characters for a parameter in a parameter query ==> 255
  9. Number of ANDs in a WHERE or HAVING clause ==> 40
  10. Number of characters in an SQL statement approximately ==> 64,000
Sep 3 '08 #5

Expert Mod 2.5K+
P: 2,545
Hi. ADezii has helpfully provided the exact specs for you. Regardless of whether or not you want to break the query down you really have no choice, as it undoubtedly exceeds the limits of the number of fields Access can update at once. It is also possible that it exceeds one of the other limitations (number of ANDs involved for instance). It is not the length of the query that is the problem (though I would shudder at the thought of reading - let alone understanding - a 5000 character SQL statement - how on earth can you verify that it performs correctly in all its aspects in all circumstances???).

In any event, if you consider future maintainability by other persons you should redesign it so that it is understandable to other people. Once the update is broken down you can bring it together into a single sequential operation using either a macro to do so or VBA code, and distribute this to the other applications involved as appropriate.

-Stewart
Sep 3 '08 #6

FishVal
Expert 2.5K+
P: 2,653
Hello, all.

Jet engine could crash with the same message in a case when query contains calculations, even not very complicated from human point of view. ;)
I've never seen specifications of that type of limitation, nor I have any personal clue about it.
The only reliable way, as others already stated, is to split down complex query into sequential parts and test them separately. Or, what is the same, build query again from scratch step-by-step and localize which addition cause it to fail.

You may read also Access msg Too complex thread concerning the same.

Regards,
Fish
Sep 3 '08 #7

NeoPa
Expert Mod 15k+
P: 31,347
...
I am trying to run a test on one table and build from there. Below is what I have and it keep getting a syntax error 3114.

Expand|Select|Wrap|Line Numbers
  1. Public Sub DoSQL()      
  2. Dim SQL As String          
  3.       SQL = "Update S_HYDRAMAPPING" & _     
  4.       "Set S_HYDRAMAPPING.MAPID = '0000'& MAPID"
  5. DoCmd.RunSQL SQL  
  6. End Sub  
...
First of all I would say two things :
  1. Read Stewart's last response. It is unlikely you will manage to keep this as one item. It would be unwise even to try.
  2. When reporting errors, it's good practice to include the message as well as the number of the error. Not forgetting, of course, to include the line number in the posted code where the error occurs.
Moving on from there though, and looking specifically at your posted code, it is suffering from a very common problem when building up SQL strings in code. There is no white space before the "Set ". There is in the VBA code, but none is included in the SQL string.

A good trick here, until you find this more like second nature, is to add a Debug.Print line after the string has been formulated.
Expand|Select|Wrap|Line Numbers
  1. ...
  2. Debug.Print SQL
  3. DoCmd.RunSQL SQL
  4. ...
Other possible problems are no white space before the "&" and a possibly unrecognisable reference afterwards (MAPID).

To illustrate, the string would look like the following :
Expand|Select|Wrap|Line Numbers
  1. Update S_HYDRAMAPPINGSet S_HYDRAMAPPING.MAPID = '0000'& MAPID
Sep 3 '08 #8

ADezii
Expert 5K+
P: 8,619
One more point to consider, if you are breaking down your Query into multiple Update Querys, I would definitely think that you would want to wrap the entire process into a Transaction. I'm sure that if you ran into a situation where some of the Updates were successfully executed, while others failed, you would not be in a happy place. Either 'ALL' the Updates are a success or they 'ALL' fail.
Sep 3 '08 #9

NeoPa
Expert Mod 15k+
P: 31,347
Good thinking Batman :)

That's almost certainly something you should implement.
Sep 3 '08 #10

P: 8
Thank you all for your knowledge and information. I did break up the query into many parts. I then added each part to a macro and everything ran fine. I did have to break it down into 30 parts. It was the only way that everything would populate correctly. It appeared that if a field had the same name in different tables it could not be used in the same query even when I specified the table.field name for the specific update. Anyways, it does work now. Thanks again to all!

Sloney
Sep 3 '08 #11

NeoPa
Expert Mod 15k+
P: 31,347
I'm very pleased to hear that.

I suspect your final situation will be a lot easier to maintain into the future. Well done :)
Sep 4 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.