Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Unanswered: Updating 900k Records with 9k lock

    I've inherited a database at work and need to make some corrections...Here is my problem. I need to find related fields where no obvious relationship exists...Here's an example of a few rows delimeted by commas...

    Table name: Products
    Fields: ProductID (text), ProductType (text)

    2342,door
    2321,knob
    2331,window
    3212,table
    5231,door
    2121,window
    3222,door
    4321,knob

    Anytime I see "door" AND it's followed immediatly by "knob" in the same field on the very next record (assume it's sorted correctly) then I need to add a new field to the row with "knob" and fill it with the ProductID of door. IF NOT, then fill the new field with ProductID of the original row...

    The corrected version of above would be

    2342,door,2342
    2321,knob,2342
    2331,window,2331
    3212,table,3212
    5231,door,5231
    2121,window,2121
    3222,door,3222
    4321,knob,3222

    The MAJOR headache here is that it's a table with 900,000+ rows...What is the best way to go about this? My work PC has 9000 recordlocks (not changable), which makes doing a loop difficult at best...

    I have a moderate understanding of VBA

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You have two issues.

    1. 900k+ rows is WAY more than the JET engine can handle in any semi-efficient manner
    2. The order of the rows is completely and totally arbitrary. You could pull the same query twice and, in theory, get the records back in a different order. You need something else to order them with.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2006
    Posts
    5
    I completely agree on both accounts. Ideally this would be in SQL but for now it's not possible...

    The default order right now is correct, so I'm ok on that front...I just need an efficient way to correct the related data (i.e. doors+knobs are related).

    Later, I can break the table up and rebuild the database...for now, I can only with the tools in front of me

  4. #4
    Join Date
    Apr 2006
    Posts
    5
    I solved it. I decided to return to work after 2nd shift (when no one is in the DB) and open it exclusive, which means no locks. I then ran my nested loop and got the fields I needed. Hopefully I'm well on my way to getting this monstrosity of a DB into decent shape.

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You should consider MSDE. It's very solid, and it supports stored procedures, triggers, and views. And, it's free.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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