Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Big Query (Access 2000)

    Hello:
    I am trying to run an update query from a table that contains 49599 records and 100 fields to update a table of the same dimensions. When I try to run the query I get the error message, "Not a valid bookmark". Am I running out of memory? The machine has 256 mb of RAM.
    If so, how can I work with this table?

    Thank you,
    Doug Lowry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Big Query (Access 2000)

    Do you use custom functions in the update query?
    Does the query finish without errors if you put in criteria to limit the number of records affected?

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Big Query (Access 2000)

    Hans:
    No custom functions, just want to update data in one table with data from another.
    I can update records by limiting them in criteria. I just tried and it works fine.
    Is there a method to easily combine all records into one table and work with them? I'll need to work with this data extensively by building reports, and queries.

    Thank you,
    Doug

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Big Query (Access 2000)

    > I can update records by limiting them in criteria. I just tried and it works fine.

    That implies that it's probably a memory problem. You could write code to loop through the records and update them, that would avoid the memory problem, but it would be slow.

    > Is there a method to easily combine all records into one table and work with them?

    Do you mean append the records of one of the two tables to the other one? Since you wanted to update one table from the other, there is probably a unique key that would prevent this. Or did you mean something else.

  5. #5
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Big Query (Access 2000)

    Hans:
    What I was trying to say was, "Is there a way to work with the one large table?"
    If it's a memory problem, the only way to work with the large table would be to increase RAM. I can do this, but not today.
    I could break the table up into smaller components, then build queries and reports and I very well may do this for today.
    How can I build a report using all the records when I need to do this?

    Thank you,
    Doug

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Big Query (Access 2000)

    I'm not sure that you will have problems when running a select query or a report based on the table. The problem with an update query is that Access performs the update, then asks you for confirmation. If you reply No, the entire update must be undone. A straightforward select query is less resource intensive (but a query with lots of calculations is another story.) So see how far you get without installing extra memory.

  7. #7
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Big Query (Access 2000)

    Hans:
    OK, I'll keep trying and see what I can accomplish with regular select queries and reports.

    Your advice was helpful and I appreciate it.

    Thank you,
    Doug

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Big Query (Access 2000)

    A major part of your problem may also be the number of fields involved. More often than not, have 100 fields in a table indicates the data has not been normalized. I realize that may not be feasible in your case, but another strategy is to only update selected fields in a given pass.
    Wendell

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Big Query (Access 2000)

    If running Action query, you can try changing the query's UseTransaction property. From Access Help:

    "You may get substantial performance benefits by setting the UseTransaction property to No in the right circumstances. When the UseTransaction property is set to No, the user will never encounter problems associated with too many lock requests. In addition, the Jet database engine doesn't store query results in a temporary database, thereby achieving a substantial performance increase. ... If there is a large number of records that must be written to the temporary database, performance may suffer as a result of running an action query as a single transaction."

    See UseTransaction Property in Access Help for more info. If setting property to No, however, be advised of possible consequences. See MSKB 208184:

    ACC2000: Action Query Commits Changes to Data When Cancelled

    Article notes: "When the UseTransaction property is set to No, the results are written immediately to the current database; the query runs much faster because it is not wrapped in a single transaction. However, you cannot cancel the changes that the query has made, even if you click No when prompted to commit the changes." See article for more info.

    You may want to experiment (with backup of database) by changing UseTransaction property to see if it makes any difference. If not a factor, do not have any other ideas - have never seen "Not a valid bookmark" error msg when running action query.

    HTH

  10. #10
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Big Query (Access 2000)

    Wendell:
    Thank you for this suggestion. I ended up pulling many of the fields into separate tables where the data the tables contained was more similar. The tables are necessarily more normalized, but they do contain fewer fields. This in itself makes them easier and quicker to work with.

    Doug

  11. #11
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Big Query (Access 2000)

    Mark:
    I will look into this option tomorrow when I have more time to investigate my options. I appreciate you putting the link in the post and making it easy to find the article. I'll copy the large table first!

    Thank you,
    Doug

Posting Permissions

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