Updating 900k rows with a 9k lock limit

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi.
The MAJOR headache here is that it's a table with 900,000+ rows...What is
the best way to go about this?
Click to expand...

With an UPDATE query, provided your table meets certain criteria. The query
will assume that the sort field for the Products table is numerical and
sequential, because the query checks the value of the sort field and
subtracts 1 in order to determine the previous record. Therefore, if it
isn't an AutoNumber, you need to add an AutoNumber field to this table. This
field must be unique and not allow NULL's, so if it's the table's primary
key, then it meets the criteria.
I have a moderate understanding of VBA
Click to expand...

No problem. Very little VBA is needed to accomplish this.

First, make a copy of your table, just in case something goes wrong. You
may want to create a backup copy of the file, since the table is so big.

Next, create a new query and open it in SQL View. Paste the following into
it:

SELECT ID, ProductID, ProductType,
(IIF(ProductType = "knob" AND "door" =
DLookUp("ProductType","Products","ID = " & ID - 1),
DLookUp("ProductID","Products","ID = " & ID - 1), ProductID)) AS Prod
FROM Products
ORDER BY ID;

This query assumes that the sort field is named ID, so if yours isn't, then
replace ID with the name of your field. Save this query as qryAssemblies,
then open it to verify that the column Prod is showing the correct
ProductID's. It should take a little while because IIF( ) and DLookup( )
functions are slow, but they'll do the trick. If everything looks fine, then
create a new query and open it in SQL View. Paste the following into it:

UPDATE Products INNER JOIN qryAssemblies
ON Products.ID = qryAssemblies.ID
SET ProdID = Prod;

This query assumes that the new field that needs the ProductID of the
product is named ProdID, so change this to yours, and then save the query.
Run this query to assign the proper ProductID to the new field (ProdID) in
the records, even the door-knob sequential records.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
 
Back
Top