[AccessD] Speed enhancement

Thanks Stuart, Jim, Bob, etal:

The solution I used was Stuart's SQL suggestion. It ran 10,000 times faster,
well quite a bit faster anyway :o)

strSQL = "UPDATE MailList " _
&  "INNER JOIN TempMailList " _
&  "ON MailList.MailListID = TempMailList.MailListID " _
&  "SET MailList.StaffID = " & lngNewStaff & ", " _
&  "MailList.DeptTypeID = " & lngNewDept & ";"

Docmd.RunSQL StrSQL

John B.

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Bartow
> Sent: Friday, August 29, 2003 5:41 PM
> To: AccessD
> Subject: [AccessD] Speed enhancement
>
>
> An easy question for you speed optimizing gurus out there:
> What should I replace this code (which was meant for a few record updates)
> with to make it run faster (it now needs to be used for 500+
> records). Watch
> for line wrap.
>
> Set rstTemp = db.OpenRecordset("TempMailList")
> Do Until rstTemp.EOF
> lngRecordID = rstTemp!MailListID
> DoCmd.RunSQL "UPDATE MailList SET MailList.StaffID = " & lngNewStaff & ",
> MailList.DeptTypeID = " & lngNewDept & " WHERE (((MailList.MailListID)=" &
> lngRecordID & "));"
> lngCount = lngCount + 1
> rstTemp.MoveNext
> Loop
>
>
> TIA
> John B.
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>