Querying A Sql Server In Excel With A Parameterized Query Using Vba

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 a run-time error '1004' saying it's a Genera

Solution 1:

I found this Stack Overflow question with a Google search. It does not look like anyone has tried answering it, so here's what I ended up doing. Instead of using "QueryTable", use an ADO command object as done in this MSDN article.

MSDN Example:

Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Param1 As ADODB.Parameter
Dim Rs1 As ADODB.Recordset

Dim i AsInteger' Trap any error/exception.OnErrorResumeNext' Create and Open Connection Object.Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = "DSN=Biblio;UID=admin;PWD=;"
Conn1.Open

' Create Command Object.Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?"' Create Parameter Object.Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5)
Param1.Value = 5
Cmd1.Parameters.Append Param1
Set Param1 = Nothing' Open Recordset Object.Set Rs1 = Cmd1.Execute()