Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query constraints (Access 97 SR2)

    I know queries have limits as to their complexity - would anybody have any ideas on any rules for this complexity i.e. number of criteria, size of criteria, does the number of criteria in 1 field change if more criteria is added to another field?

    Basically, I have a VB6 application that makes selections on a few tables and builds a query based on this criteria. VB6 returns an error of "Out of memory" if it tries create a query that is too complex. If I can find out some rules about the complexity of queries I was thinking of making some sort of counter, so when it gets to a specified amount a msgbox can be displayed warning that no more criteria can be added.

    Any help would be much appreciated.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query constraints (Access 97 SR2)

    Go to Access Help. Search the index. - Specifications, Microsoft Access.

    This will give you the limits for tables, queries, database, etc.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query constraints (Access 97 SR2)

    VB throws "out of memory" messages for a bunch of reasons, but usually because of memory leaks from not releasing resources when the application ends. Are you sure it was the query complexity that caused the message and not faulty cleanup in your code?
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query constraints (Access 97 SR2)

    Thanks taweller for the info. I read the bit about 40 Ands or Havings, and the bit about 255 characters in a query parameter. So Im going to look into that.

    As for your question Charlotte. The VB program adds in criteria to a table which is then decoded and added to the process of making a query. After the error occured I removed some of the criteria (and there was alot of it) and the query could then be run. I tried putting the longer criteria in a query manually and got the "query is too complex" error. Is it possible my theory could be wrong then?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query constraints (Access 97 SR2)

    It's possible that something else is going on. I've seen that message on a single table query in Access 2.0 or 97 because every field in the source table was 255 characters wide and all 32 of the fields had been dragged onto the grid. There are query limits on the total record length, the number of characters in the SQL statement, and a bunch of other things, and a whole bunch of them can cause that error message. In Access, at least, I've also seen queries that ran from the database window but threw errors when you tried to run a simple report based on them.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •