Opening two tables

[denaex]

I am updating a phone list. First I open table1 and get a name then I close table1 and Open table2 to see if there is a match where I will update the phone number and routing symbol. If there is no match, I add the record to the end of the table.

My problem is that after several (675) iterations of the table2 search it bombs out as Object not set. Is there a limit on opening and closing or am I not closing the first table correctly? After extracting the name from the first table1 I say :

object.Close and Set object = Nothing.

 

[JamesLean]

As far as I'm aware there's no "limit" that would cause this problem. However, it sounds like you could perform this update much more efficiently using set-based SQL commands rather than looping row-by-row. An example:

Table1 has a list of phone numbers/routing symbols. Table2 has the same. You want to update the phone numbers in Table2 with the correct routing symbols from Table1, then insert any rows from Table1 which don't match a number in Table2.

--Update existing rows
UPDATE t2
SET routing = t1.routing
FROM table2 t2 JOIN table1 t1 ON t2.phone = t1.phone
WHERE t2.routing <> t1.routing

--Insert new rows
INSERT table2 (phone, routing)
SELECT t1.phone, t1.routing
FROM table1 t1 LEFT JOIN table2 t2 ON t1.phone = t2.phone
WHERE t2.phone IS NULL

--James