Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87

    Unanswered: There isn't enough memory to perform this operation.

    I am trying to make a Query By Form, following the instructions on How to use the Query by Form (QBF) technique in Access 2000

    I edited the query to input my object names rather than the example ones in the code, and it worked fine until I closed my database. Once I saved, closed, and reopened my database, it gave me an error that said
    Code:
    There isn't enough memory to perform this operation. Close unneeded programs and try the operation again.
    I am running a fairly new machine with no excess programs in the background, have tried it on multiple machines too.

    I have tried compressing/repairing

    I have tried deleting and rewriting (which worked only temporarily again)

    I have called Microsoft, but they no longer support Access 2000.

    I have searched forums, and supposedly there's a patch out there somewhere that I cannot seem to find, but regardless, this database will be on a shared network drive so that multiple computers will be able to access it. If I use a patch, I will have to install the patch on all of the machines accessing the database, and I don't have access to all of those machines, because I am not the network administrator.

    I have also searched Microsoft's website, and put in a trouble ticket, however they referred me to the customer support line who told me to kick rocks.

    Has anyone else encountered this sort of problem? Is there a solution?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you post your query along with some sample data?
    Have a nice day!

  3. #3
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    unfortunately, I cannot even VIEW my query right now. However, it looks like this...

    FORM "View Reports"
    Code:
       Combo box 1
       ------------------
       Name: ViewReports_OwningOrganizationBox
    
       Text box 1
       ------------------
       Name: ViewReports_SubShopBox
    
       Text box 2
       ------------------
       Name: ViewReports_DRISBox 
    
       Combo box 2
       ------------------
       Name: ViewReports_EchelonBox
    
       Text box 3
       ------------------
       Name: ViewReports_SerialNumberBox
      
       Text box 4
       ------------------
       Name: ViewReports_DefectBox
    
       Combo box 3
       ------------------
       Name: ViewReports_PriorityBox
    
       Text box 5
       ------------------
       Name: ViewReports_IDNumberBox
    
       Combo box 4
       ------------------
       Name: ViewReports_LocationBox
    
       Combo box 5
       ------------------
       Name: ViewReports_CategoryCodeBox
    
       Text box 6
       ------------------
       Name: ViewReports_JobStatBox
    
       Text box 7
       ------------------
       Name: ViewReports_NSNBox
    
       Text box 8
       ------------------
       Name: ViewReports_TAMCNBox
    
       Command Button 1
       ------------------
       Caption: Generate List
       OnClick: RunMacro.QBF_Macro
    
       Command Button 2
       ------------------
       Caption: Back
       OnClick: RunMacro.OpenMainMenuForm
    The query looks like this:
    QUERY "QBF_Qeury"
    Code:
       Field: {FieldName}
       Sort: Ascending
       Criteria: Forms![View Reports]![{FieldName}] Or Forms![View Reports]![{FieldName}] Is Null
    The macro that runs the query looks like this:
    MACRO "QBF_Macro"
    Code:
       Action: OpenQuery
       Query Name: QBF_Query
       View: Datasheet
       Data Mode: Edit

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Do you have code firing when the mdb is first opened?

    1. Restart your computer.

    2. Open the mdb (while holding down the shift key). This bypasses any startup code you may have. See if you get the error on memory when opening (ie. before running the actual query).

    If you do get an error while just opening the mdb file (with the shift key), something else is going on (or the mdb itself is corrupt.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    Well I don't think this computer has been restarted yet, but I know that I don't have any problem opening or closing the database. The error shows up explicitely when I try to run that query. It could also be the macro, but I tried converting the macro into a module, after seeing this error. Like I said, I tried a lot of compacting/repairing, and other methods to make the database run faster/more efficiently. I'll restart the computer now and let you know.

  6. #6
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    No. I do not get any errors upon opening or closing the database. It is simply the query. It is a lot of information to query at once, however, this is the functionality that I am needing. This is a small database in terms of actual data stored. It will grow once I switch the sample data out and input the real data, but with it being as small as it is, I don't see why a function like this wouldn't work. Is there another way to do a query by form? Perhaps some code?

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for testing the suggestions. I thought perhaps your problem might be the result of something else going on in coding when the mdb opened but I think it's safe to say that it's the query itself producing the problem.

    If you need the functionality this query produces, I might suggest designing a query to FIRST filter the data with any needed criteria and then design another query based upon the first query to then do your summing/totalling/etc...

    Another technique which works well is to make your first query (which applies the criteria) a 'make-table' query which makes a 'temp' table. Then your 2nd query uses the temp table to do the summing/totalling/etc... I had over 5 million records I would query in an mdb (the backend was SQL Server linked into the mdb) and could run totalling queries on those records which returned results in less than 10 minutes. What I did was de-normalize some of the fields in the tables, adding those 'grouping' fields (which would normally be gotten by linking in the relational table), into the main table itself. Keep in mind that every time you link a table into a query, the longer it takes to process the query. A query with 5-7 linked tables takes much longer to execute versus a query which only has 1 or 2 tables linked. Also, joining 2 relational tables together on 2, 3 or more fields (versus 1 field) in a query really bogs the query down.

    MSAccess tends to get over-burdened when you try to do too much in 1 single query. The 'not enough memory' your seeing is not related to recordset size but instead deals with an expression or criteria statement MSAccess finds difficult (or time-consuming) to process within the query - hence producing the not enough memory type error. This seems most likely the case in your situation especially since you're not dealing with a large recordset (which even if you did have 100,000+ records, still wouldn't be the cause of the 'not enough memory' error).

    If you can upload the mdb (removing any confidential data from the table), or copy the SQL statement from the query (you can change the view of the query from design view to sql view), it will much easier to find where your problem lays. A query which has multiple linked tables within the query, combined with criteria and combined with 'summing' the data, often produces an error of some kind, especially if something conflicts with criteria, expressions, and grouping/summing all in the same query. Compacting/Reparing the mdb typically won't resolve these kind of issues.

    If you're interested, here's a nice example I use in every mdb which allows you to show the query results in an actual form. It won't solve your specific issue but it works nicely for having query results show in a form. - http://www.dbforums.com/6390529-post84.html
    Last edited by pkstormy; 03-23-10 at 22:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    I deeply appreciate the thought and concern, however I may not have been clear when explaining. This query does not do any calculating or summing. It simply relies on the information that a user inputs into a form to define the criteria for the query. For instance, a basic example that has nothing to do with my database, if a user wants to look up 3-digit combination locks that range values 1-3, and inputs "1" into the first digit, "2" into the last digit, it would return combinations:
    1, 1, 2
    1, 2, 2
    1, 3, 2
    Because these all match the criteria. If the user only put in the first digit, it would return:
    1, 1, 1
    1, 1, 2
    1, 1, 3
    1, 2, 1
    1, 2, 2
    1, 2, 3
    1, 3, 1
    1, 3, 2
    1, 3, 3
    Because all of these match the criteria. A user inputs the criteria for the query into the form. Therefore, there's no calculating because it simply looks up values based on that criteria. Splitting the query into two would not help me because it's essentially a "one-query" job.

    However, I have attatched my .mdb. I would scan it before you open it. Though I work on government computers that are fairly safe, they're not my machines, therefore I can't be 100% certain of the integrity of the network.
    Attached Files Attached Files

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Something is wrong with the QBF_Query. In MSAccess 2007, I couldn't get it to open in design view.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    That's what I'm saying. That's why I said that I can't even view my query. It gives me that error any time I open (in any view) or run the query. Like I said, I tried rewriting the query, however, it still gives me the same problem.

    I could tell you how to recreate the query that I am referring to, however it's pretty tedious. That would really be the only way you could see if you could reproduce the problem.

    On the website that I linked to in the first post, they give you basic instructions on how to create a very basic QBF using the sample database that comes with access. I followed these steps to ensure that it would do what I wanted, and it did, so I modified the expressions placed in the query to include MY objects rather than the sample ones. Everything else was left the same. It worked at first, but when I closed the database and reopened it to show off to someone (haha), it no longer worked and gave me the error.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Personally, I might delete that query and start from scratch.

    Unless I'm missing something, the QBF_Query/Form they are describing in the link sounds like the reporting form (or even the search form) examples in the code bank (I think there are a few of them).

    Basically you design a form with an unbound textbox or combobox field and in the criteria for your query (I think they have it as a datasheet view of a form), you put =Forms!MyFormName!MyUnboundFieldName (or: Like Forms!MyFormName!MyUnboundFieldName & *) for criteria under the appropriate column(s). Just make sure the field value in your unbound form is of the same field type in your column for the criteria (ie. you don't want "Jack" returned in the unbound box when the criteria is under the CustomerID field).

    I'd disregard the IsNull part as I'm not sure how this would work (and if this is the problem.) If it works ok, then add the IsNull part in the criteria.
    Last edited by pkstormy; 03-24-10 at 00:11.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    I didn't understand the IsNull part either, but being new to programming, I assumed that it was there so that the query would continue even if the value of the unbound object was null. Anyway, I'll try rewriting it without the null and using the expression you have or the one in the search database, because I looked at it and it does what I want to do. But then again, so did the sample on the link. We'll see. I'll let you know how it goes.

  13. #13
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    alright I took the IsNull out, leaving your expression =Forms!MyFormName!MyUnboundFieldName...

    No luck. I'll try your other expression.

  14. #14
    Join Date
    Mar 2010
    Location
    in the dark...
    Posts
    87
    It generates blank queries with either of those expressions. I'll take a look at the search example and figure out how he did it.

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try the:

    Like Forms!MyFormName!MyUnboundFieldName & *
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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