[AccessD] Speed enhancement

On 30 Aug 2003 at 9:09, Stuart McLachlan wrote:

Oops, I didn't throw away the Where when I was editting it down. All 
it needs is:

>  
> 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 & ";"
> 
> 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.
> 
> 
> 
> _______________________________________________
> 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.