Newbie about rs.filter

Dear all, I need to compare two db(Access2003), they have the same
ProductID, not more then 10000 records. While using rs.filter, the time take
more then 10 minutes, and take 99% cpu loading, also, after comparing, the
file size become very large. Is there any better way to do this instead of
using "rs.filter".
Also, should i need to use "rs.update", because if no "rs.update", the db
still save the value. Thanks for all .

Set rs = GetMdbRecordset( "Goods.mdb", "Goods")
Set rs2 = GetMdbRecordset( "Goods2.mdb" , "Goods")

rs.movefirst
While Not rs.EOF
A = rs("ProductID")
B = rs("Qty")
C = rs("ID")
rs2.filter = "ProductID = '" & A & "'"
D = rs2("Qty")
rs2("ID") = C
If B <> D Then
Response.write rs("ProductID")
rs.movenext
Wend

Re: Newbie about rs.filter

Alan wrote:
> Dear all, I need to compare two db(Access2003), they have the same
> ProductID, not more then 10000 records. While using rs.filter, the
> time take more then 10 minutes, and take 99% cpu loading, also, after
> comparing, the file size become very large. Is there any better way
> to do this instead of using "rs.filter".

The only better way would be to get both tables into the same database and
use sql to do this update. You could use a linked table (see Access online
help or an Access newsgroup for details).

With both tables in the same database, say in Goods2.mdb, you could use this
sql statement to do the update:

UPDATE Goods g INNER JOIN Goods_lnk l
ON g.ProductID = l.ProductID
SET g.ID = l.ID

To get the records where the quantities are different:

SELECT g.ProductID
FROM Goods g INNER JOIN Goods_lnk l
ON g.ProductID = l.ProductID
WHERE g.Qty <> l.Qty


> Also, should i need to use "rs.update", because if no "rs.update",
> the db still save the value. Thanks for all .

Earlier versions of ADO required the Update statement. Newer versions do
implicit updates (the update is performed when the movenext is executed)
which can be canceled using the CancelUpdate method before performing an
action that would trigger an update.

>
> Set rs = GetMdbRecordset( "Goods.mdb", "Goods")
> Set rs2 = GetMdbRecordset( "Goods2.mdb" , "Goods")
>
> rs.movefirst
> While Not rs.EOF
> A = rs("ProductID")
> B = rs("Qty")
> C = rs("ID")
> rs2.filter = "ProductID = '" & A & "'"
> D = rs2("Qty")
> rs2("ID") = C
> If B <> D Then
> Response.write rs("ProductID")
> rs.movenext
> Wend

If you can't use linked tables for some reason, one thing that will help is
disconnecting these databases and using batch updates (via the UpdateBatch
method), which means using client-side cursors. Your GetMdbRecordset
function will likely need to be rewritten to accomplish this.

You can find the ADO documentation at msdn.microsoft.com/library

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.