Using VBA to query a SQL Server table in Excel

Question

I am trying to query a table in Microsoft Excel using VBA. I wrote code to try to complete this task, but I get an error all the time:

'1004' runtime error: expression of a common ODBC error.

I'm not sure what I need to make this code work correctly, so I can query this table.

I am using SQL Server Express, the server I am connecting to:. .\SQLEXPRESS

Database:

Databaselink

Product table query VBA 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;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " 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 

Answer 1

the solution for this is the following

 Dim lo As ListObject Set lo = ActiveSheet.ListObjects.Add(xlSrcExternal, _ "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SERVER_NAME;Data Source=CONNECTION_ADRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False", _ True, xlYes, Range("A2")) lo.QueryTable.CommandType = xlCmdSql lo.QueryTable.CommandText = "SELECT * FROM TABLE_NAME" With lo.QueryTable.Parameters.Add("Currency code", xlParamTypeVarChar) .SetParam xlRange, ActiveSheet.Range("A1") .RefreshOnChange = True End With lo.QueryTable.Refresh BackgroundQuery:=False 

This can create a listObject from SQL Query.

Answer 2

Use the OLEDB provider for Excel, and it should work fine.