title: VBA Transactions
body: <p>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.</p>
  
  <p>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?</p>
  
  <pre><code>Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" &amp; _
  "Persist Security Info=False;" &amp; _
  "Initial Catalog=ImportTest;" &amp; _
  "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
  </code></pre>