Guys,

I tried posting this topic in the VBA forum, but got no response. Perhaps someone from this forum can shine some light on this problem.

I tried to write a prepared sql script instead of just executing queries inside of a loop. I timed it, and the prepared script runs over a minute slower than the regular script. I don't understand why it would take longer. I was expecting to save some time.

Here is the prepared statement script:

Dim con As New ADODB.Connection
Dim com As New ADODB.Command
Dim strqry As String

con.ConnectionString = CurrentProject.Connection
con.CursorLocation = adUseServer
con.Mode = adModeReadWrite
con.Open

Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
rsData.ActiveConnection = CurrentProject.Connection
rsData.CursorType = adOpenKeyset

strqry = " select q33_1 from test "
         rsData.Open (strqry)

Dim SQL_TO_USE As String
SQL_TO_USE = "update test set zip = 'TestThisString' where q33_1 = ?"

com.CommandType = adCmdText
com.Prepared = True
com.CommandText = SQL_TO_USE
com.Parameters.Append com.CreateParameter("value", adVarChar, adParamInput, 50)
Set com.ActiveConnection = con

While Not rsData.EOF
   com("value") = rsData("q33_1")
   com.Execute
   rsData.MoveNext
Wend

con.Close


Here is the regular way of doing it:

Dim strqry As String
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
rsData.ActiveConnection = CurrentProject.Connection
rsData.CursorType = adOpenKeyset

strqry = " select q33_1 from test "
         rsData.Open (strqry)

Dim rsAction As ADODB.Recordset
Set rsAction = New ADODB.Recordset
rsAction.ActiveConnection = CurrentProject.Connection

While Not rsData.EOF
   strqry = "update test set zip = 'RegularTest' where q33_1 = " & rsData("q33_1") & ""
   rsAction.Open (strqry)

   rsData.MoveNext
Wend

Any suggestions would be greatly appreciated.

nikotromus

nikotromus

__________________
nikotromus