VBA Transactions

Question

I'm trying to insert some data into SQL from Excel VBA. The SQL commands are built up over the course of the VBA script, and include the use of some SQL variables.

I'm trying to understand how transactions work in VBA, and whether they will work with what I need to do, I have the code below that will test this, but it does not work. It always gives me an error about "Must define scalar variable @name" so I assume there is an issue here with the scope of the data/transaction. How can I get this simple code to work?

Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=ImportTest;" & _
"Data Source=localhost\sqlexpress"



Set cn = New ADODB.Connection

With cn
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0

End With
cn.BeginTrans
 cn.Execute "set implicit_transactions off"
cn.Execute ("declare @name varchar(100)")

cn.Execute ("set @name='name'")

cn.Execute ("Insert into test (id,name) values (55,@name)")
cn.CommitTrans


cn.Close
Set cn = Nothing

Answer 1

you need to execute all these in 1 batch

cn.Execute "set implicit_transactions off"
cn.Execute ("declare @name varchar(100)")
cn.Execute ("set @name='name'")
cn.Execute ("Insert into test (id,name) values (55,@name)")

built a string and then use 1 cn.Execute

Better yet, use parameterized queries to guard against SQL injection