Updating a sql recordset
-- I don't check the Email account attached to this message. RBrandt at Hunter dot com SQL is better than Recordset Updating and Vice Versa. Suppose the SQL is demanding and takes 5 minutes to run. But is this more efficient than each User getting his/her set of records and processing them on the Client machine? It may take some thought, perhaps even testing to decide. The data is related but there is no clear rule that defines the relationship.
Suppose 20 Users call the SQL (on discrete sets of records) right now. We find we have to examine (with a function in code) much of the data in each record to find which record in Table1 relates to which record in Table2.
But for this question assume a simple creation of records and/or updating existing records.
A new acquaintance of mine has questioned this as he uses SQL statements extensively.
Changes to the data structure would require revisiting all relevant SQL statements, but then it may also require revisiting my code (except that it a lot of situations I write the data across by looping through the fields collection of the destination and don't actually code in field names except where absolutely necessary). I would only use the latter if I needed to use the value or result from one row in some other row's calculation.
[examples snipped] A scenario that requires a SQL statement in a loop is another where I agree that a Recordset can be faster.
I was referring to using a looping Recordset operation to update many rows as compared to a single SQL statement that would update all of those same rows with one statement.
His code includes constructing SQL statements that he then executes to make the changes.
I can see this would work and it has made me think about the pros and cons of this.
Private Declare Function Get Tick Count Lib "kernel32" () As Long Private Sub rh() Dim T As Long Dim I As Integer Const C As Integer = 1000 Dim rs As New ADODB. But for this question assume a simple creation of records and/or updating existing records.