title: Using VBA to query a SQL Server table in Excel
body: <p>I'm trying to query a table in Microsoft Excel using VBA. I've written up some code to try and accomplish this task, but I keep getting an error:</p>
  
  <blockquote>
    <p>run-time error '1004': Saying it's a General ODBC error. </p>
  </blockquote>
  
  <p>I'm not sure what I need to do to get this code to run properly so I can query this table. </p>
  
  <p>I'm using SQL Server Express, the server I'm connecting to: <code>.\SQLEXPRESS</code></p>
  
  <p>Database: </p>
  
  <p><a href="http://examples.oreilly.com/9780735666054-files/9780735666054_files.zip" rel="nofollow">Databaselink</a></p>
  
  <p>Querying the products table
  VBA Code:</p>
  
  <pre><code>Sub ParameterQueryExample()
  '---creates a ListObject-QueryTable on Sheet1 that uses the value in 
  '        Cell Z1 as the ProductID Parameter for an SQL Query
  '        Once created, the query will refresh upon changes to Z1. 
  
  Dim sSQL As String
  Dim qt As QueryTable
  Dim rDest As Range
  
  '--build connection string-must use ODBC to allow parameters
  Const sConnect = "ODBC;" &amp; _
      "Driver={SQL Server Native Client 10.0};" &amp; _
      "Server=.\SQLEXPRESS;" &amp; _
      "Database=TSQL2012;" &amp; _
      "Trusted_Connection=yes"
  
  '--build SQL statement
  sSQL = "SELECT *" &amp; _
          " FROM TSQL2012.Production.Products Products" &amp; _
          " WHERE Products.productid = ?;"
  
  '--create ListObject and get QueryTable
  Set rDest = Sheets("Sheet1").Range("A1")
  rDest.CurrentRegion.Clear  'optional- delete existing table
  
  Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _
      Source:=Array(sConnect), Destination:=rDest).QueryTable
  
  With qt.Parameters.Add("ProductID", xlParamTypeVarChar)
      .SetParam xlRange, Sheets("Sheet1").Range("Z1")
      .RefreshOnChange = True
  End With
  
  '--populate QueryTable
  With qt
      .CommandText = sSQL
      .CommandType = xlCmdSql
      .AdjustColumnWidth = True  'add any other table properties here
      .BackgroundQuery = False
      .Refresh
  End With
  
  Set qt = Nothing
  Set rDest = Nothing
  End Sub
  </code></pre>