Question

Is there a way to use Command.Prepare, and Command.ExecuteNonQuery in VBA?

The section entitled "Consider Using Command.Prepare" in the following article on ADO.NET describes objects and methods that are similar to ADODB properties and methods and I am wondering if there is an equivalent for VBA.

ADODB has .Command, .Prepared, and .Parameters.Append, but I can t seem to put them together in a way that works similar to the sample code in the article.

http://msdn.microsoft.com/en-us/library/ms998569.aspx#scalenetchapt12_topic11

Thanks!

Answer 1

The answer turned out to be fairly straightforward once I got all the syntax down. Unfortunately, it turns out there isn t much advantage to using Command.Prepared according to this article:

http://msdn.microsoft.com/en-us/library/aa260835%28VS.60%29.aspx

Read the section entitled the "so-called prepared property."

Here s the code. It works the same with and without .Prepared = True

With objCommand

    .ActiveConnection = ADO.Connection
    .Prepared = True
    .CommandText = "INSERT INTO [table1] ( col1 ) VALUES ( ?, ? )"
    .Parameters.Append .CreateParameter("intVariable", adInteger, adParamInput)
    .Parameters.Append .CreateParameter("strVariable", adVarChar, adParamInput)

    For intCounter = 0 To 10
        .Parameters("intVariable").Value = intCounter
        .Parameters("strVariable").Value = "test_value"
        .Execute
    Next intCounter

End With

I was hoping for a significant reduction in the time it took to upload my data (there is a lot of it), but all I really got was better use of parameters.

友情链接