[AccessD] Speed enhancement

At least two possibilities - either all DAO or all SQL:

1. The RunSQL opens and closed MailList and on every iteration. Open 
it once and step through it:

 Dim criteria as String
 ....
 Set rstMail = db.OpenRecordset("MailList")
 Set rstTemp = db.OpenRecordset("TempMailList")
 Do Until rstTemp.EOF
 lngRecordID = rstTemp!MailListID
 criteria =  "MailListID = " & lngRecordID
 rstMail.Findfirst criteria
 rstMail.Update
 rstMail!StaffID = lngNewStaff
 rstMail!DpetTypeID = lngNewDept
 rstMial.Update  
 rstTemp.MoveNext
 lngCount = lngCount + 1
 Loop
 
2. Faster still would be to use a bulk update query with an inner 
join to select all of the required records in one hit:

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

Docmd.RunSQL StrSQL



On 29 Aug 2003 at 17:41, John Bartow wrote:

> 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

 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.