Guys,

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

Is this an ADP talking to SQL Server? This isn't real clear:

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

It looks like you're attempting to open a connection other than the ADO connection already in use by Access. Havn't seen CurrentProject.Connection assigned as a connection string value before. Its already the open ADO connection that Access is using.

Maybe to start:

Set com.ActiveConnection = CurrentProject.Connection

instead of

Set com.ActiveConnection = con

Then at least you're comparing apples to apples.

Bob

Bob, I tried it. I took out every reference to the con object and instead made the connection through the command Set com.ActiveConnection = CurrentProject.Connection.

It worked but there was no difference in speed. The prepared scrips still runs one minute slower than executing the non-parsed query script. Oh well. I think this one will just remain a mistery.


nikotromus