title: Named variables in SQL using ADODB in vba
body: <p>I have lots of SQL scripts, many of which use various different variables throughout, and I'd like to be able to drop the results directly into Excel. The hope is to do this as 'smoothly' as possible, so that when someone gives me a new SQL script (which may be relatively complicated), it is relatively clean to set up the spreadsheet that gathers its results.</p>
  
  <p>Currently trying to get this working using ADODB Command objects parameters, but I can't even manage to get a very basic example to work. I have the following VBA:</p>
  
  <pre><code>Dim oConnection As ADODB.Connection
  Set oConnection = New ADODB.Connection
  oConnection.ConnectionString = "MyConnectionString"
  oConnection.Open 
  Dim cmd As ADODB.Command
  Set cmd = New ADODB.Command    
  cmd.ActiveConnection = oConnection
  </code></pre>
  
  <p>Up to here is just setting up the connection, which seems to work fine.</p>
  
  <pre><code>cmd.CommandText = "DECLARE @DateID integer;" &amp; _
  "SELECT TOP 10 * FROM dbo.SomeRecords " &amp; _
  "WHERE DateID = @DateID" 
  cmd.CommandType = adCmdText
  
  Dim DateID As ADODB.Parameter
  Set DateID = cmd.CreateParameter("@DateID", adInteger, adParamInput)
  cmd.Parameters.Append DateID
  DateID.Value = 20120831
  
  Dim rst AS ADODB.RecordSet
  Set rst = cmd.Execute()
  
  ActiveSheet.Range("A1").CopyFromRecordset rst
  </code></pre>
  
  <p>Unfortunately, this doesn't return anything. However, if I replace the line:</p>
  
  <pre><code>"WHERE DateID = @DateID"
  </code></pre>
  
  <p>with this: </p>
  
  <pre><code>"WHERE DateID = 20120831"
  </code></pre>
  
  <p>Then the query returns exactly what you'd expect (the top 10 records from August 31), so obviously I'm not passing the value of the variable from VBA into SQL properly, but I have to admit that I'm pretty much stuck.</p>
  
  <p>Certainly <em>something</em> is being passed into SQL (if I change the type of the variable @DateID to datetime in the SQL, then I get a SQL Server arithmetic overflow error, from trying to convert something to datetime), but it isn't doing what I was expecting.</p>
  
  <p>I guess there are two questions: Is there a way to fix this code? Is there a better way of achieving the general goal described at the start?   </p>