Dynamic ADO recordset update

[alwayshouston]

Hi All
I would like to know how to dynamically update a field declared in ADO recordset? Let me explain what I am trying to do. I have a few tables in my database that has Date_Created field. I would like to update Date_Created Field in all tables dynamically. I have written the code to do this for only Employee table as below:

Dim cnnLocal As New ADODB.Connection
Dim rstCurr As New ADODB.Recordset
Set cnnLocal = CurrentProject.Connection
rstCurr.Open "Select * from Employee", cnnLocal, adOpenKeyset, adLockPessimistic
With rstCurr
Do Until .EOF
!Date_Created.Value = Now()
Loop
End With
rstCurr.Close


How can I do this for few other tables in database such as Customers, Products etc...? I get an error if I try to get to update rstCurr field name with a variable. Ideally, I should have create a procedure as below, but my below procedure gives me an error:

Sub Main()
Call Date_Created ("Employee","Date_Created")
Call Date_Created ("Products", "Date_Created")
Call Date_Created ("Customers", "Date_Created")
etc.....
End Sub

Sub Date_Created( tblName as String, fldName as String)
Dim cnnLocal As New ADODB.Connection
Dim rstCurr As New ADODB.Recordset
Set cnnLocal = CurrentProject.Connection
rstCurr.Open "Select * from " & tblName, cnnLocal, adOpenKeyset, adLockPessimistic
With rstCurr
Do Until .EOF
! & fldName & ".Value" = Now() '<<< Error here
Loop
End With
rstCurr.Close
End Sub

Thanks in Advance!!!

 

[Tranman]

Why not just bypass the recordset stuff and use the execute method of the connection as in:

CurrentProject.Connection.Execute "Update Employee set Date_Created = " & "#" & now() & "#"

Of course, you can put it in a sub and call it multiple times, passing the table name each time, and imbed the table name in the SQL statement.

Tranman

 

[alwayshouston]

I cannot use Execute method because my table consists of more than 100,000 records. The database locks itself if I try to run the Execute method. Any other alternatives are welcome!

Thanks!

 

[Tranman]

alwayshouston,
I have no idea--I just ran the above code on one of my tables that has 137,537 rows, and it took just under 2 seconds.

My recollection is that when you do an update query like this, only the current row is locked. Is there something I'm missing?

BTW, here's the change you need to do to make the recordset stuff work:

Do Until .EOF
.Fields(fldName) = Now() '<<< Error here
.MoveNext
Loop

This took about 9 seconds to run on my computer, vs < 2 for the execute method.

Tranman

 

[alwayshouston]

Thanks Tranman!
Your Solution Worked like a charm )