Query too Complex

[sloney]

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

[Stewart Ross]

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

[NeoPa]

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!)

[sloney]

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.

Public Sub DoSQL()      
Dim SQL As String          
      SQL = "Update S_HYDRAMAPPING" & _     
      "Set S_HYDRAMAPPING.MAPID = '0000'& MAPID"   
DoCmd.RunSQL SQL  
End Sub  

As I said I am a rookie so compiling is very new to me. Any advice would be helpful. Thanks folks!

Sloney

[ADezii]

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.

Public Sub DoSQL()      
Dim SQL As String          
      SQL = "Update S_HYDRAMAPPING" & _     
      "Set S_HYDRAMAPPING.MAPID = '0000'& MAPID"   
DoCmd.RunSQL SQL  
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

[Stewart Ross]

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

[FishVal]

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

[NeoPa]

...
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.

Public Sub DoSQL()      
Dim SQL As String          
      SQL = "Update S_HYDRAMAPPING" & _     
      "Set S_HYDRAMAPPING.MAPID = '0000'& MAPID"
DoCmd.RunSQL SQL  
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.

...
Debug.Print SQL
DoCmd.RunSQL SQL
...

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 :

Update S_HYDRAMAPPINGSet S_HYDRAMAPPING.MAPID = '0000'& MAPID

[ADezii]

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.

[NeoPa]

Good thinking Batman :)

That's almost certainly something you should implement.