473,320 Members | 2,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query too Complex

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
11 1936
Stewart Ross
2,545 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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
sloney
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
8,834 Expert 8TB
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
Stewart Ross
2,545 Expert Mod 2GB
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
2,653 Expert 2GB
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
32,556 Expert Mod 16PB
...
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
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
Good thinking Batman :)

That's almost certainly something you should implement.
Sep 3 '08 #10
sloney
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
32,556 Expert Mod 16PB
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

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

Similar topics

3
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going...
4
by: Starbuck | last post by:
OK, first let me say that I am no DB person. But I have a user here who keeps getting this error whenever she does, whatever it is she does, with databases... A google search takes me to...
3
by: Jonathan | last post by:
Hi all! For a match schedule I would like to find all possible combinations of teams playing home and away (without teams playing to themselves of course). I now the simple version works...
8
by: Matt | last post by:
Hi all, Thank you for taking the time. I have a database with 45 tables on it. 44 tables are linked to a main table through a one to one relationship. My question is, is there no way i can...
2
by: Ben de Vette | last post by:
Hi, I'm using the querybuilder when updating a record in a table (Access). However, I get a "Query is too complex" message. The Primary key is autonumbered. Why is it making such a complex...
9
by: Jimbo | last post by:
Hello, I have a user request to build a form in an Access database where the user can check off specific fields to pull in a query. For example, let's say I have 10 fields in a table. The user...
10
by: Robert | last post by:
I am an attorney in a non-profit organization and a self-taught programmer. I'm trying to create a client db that will allow me to search for potential conflicts of interest based either on Social...
1
by: arun | last post by:
Query is too complex -------------------------------------------------------------------------------- Hi, I was trying to solve this problem since last two days but couldn't find any solution. ...
8
by: babyangel43 | last post by:
Hello, I have a query set up in Access. I run it monthly, changing "date of test". I would like this query to be merged with a Word document so that the cover letter is created in Word, the fields...
0
crystal2005
by: crystal2005 | last post by:
Hi, I am having trouble with some complex SQL queries. I’ve got winestore database, taken from Web Database Application with PHP and MySQL book. And some question about queries as the following ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.