Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Location
    Whittier, California, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query in Large DB Hangs (2003)

    Hello All,

    I have an Access database that is 117 MB in size. It contains tables that I imported from text files. My computer has 1 GB memory, which apparently is borderline for working with this DB. For example, I've had trouble convering some fields from text to date because I get "Your computer is out of memory" error messages. I finally had to cut these tables in half, convert the fields, and then reassemble them.

    Now I need to create a one-to-many relationship, and in order to do so I have to make sure that all the common ID values in table 1 are in table 2. I created a simple query SELECT ID FROM tbl1 WHERE ID Not In (SELECT ID FROM table2). Access just hangs when I try running this query. There are 36,000 records in table 1 and 17,000 in table 2.

    Any advice for me on this? Thanks, Kevin

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

    Re: Query in Large DB Hangs (2003)

    The Not In (...) construct is powerful but inefficient. Try this:

    SELECT tbl1.ID FROM tbl1 LEFT JOIN tbl2 ON tbl1.ID = tbl2.ID WHERE tbl2.ID Is Null

  3. #3
    New Lounger
    Join Date
    Mar 2006
    Location
    Whittier, California, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query in Large DB Hangs (2003)

    Yes, that did the trick. Thanks again, Hans [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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