FYI this thread is cross posted here
So far I have gotten a suggestion to use the Replace function in the sSQL string. This approach does work, but I would still like to use the append method to define parameters.


I am toying with the idea of putting all my SQL statements used by Excel into text files.

My Reasoning

  • Cleaner
  • No line continuation concerns
  • Easier to trouble shoot
  • More portable


I can get queries to run using a text file with no parameters, or when a parameter is hard coded into the SQL text file (WHERE myParam=10/1/2012, for instance). But if I attempt to use WHERE=? or WHERE=[@myParameter] I get the following error:


"No value given for one or more required parameters."


on this line..
Code:
With objRS 
    .Open sSQL, objConn 
End With

I know I can run paramaterized queries with ADO, but is it possible to append parameters when a text file provides the SQL? Here is my full procedure (Please note it uses a custom function, 'LoadTextFile', which is not provided but reads the text files contents)


Code:
Sub ADOQuery() 
     
     
    Dim objConn As ADODB.Connection, objCmd As ADODB.Command, objRS As ADODB.Recordset, objParam As ADODB.Parameter 
    Dim a As String, stConn, sSQL, sPath As String 
    Dim myParameter As Range 
     
    Set myParameter = Sheets(1).Range("G1") 
    Set objConn = New ADODB.Connection 
    Set objCmd = New ADODB.Command 
    Set objRS = New ADODB.Recordset 
     
    sPath = "C:\Users\Desktop\SQL.txt" 
    sSQL = LoadTextFile(sPath) 
    a = "C:\Users\Desktop\Reporting Database.mdb" 
     
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
    & "Data Source=" & a & ";" 
     
    objConn.Open (stConn) 
    Set objCmd.ActiveConnection = objConn 
    objCmd.CommandText = sSQL 
    objCmd.CommandType = adCmdText 
    objRS.CursorType = adOpenForwardOnly 
    objRS.LockType = adLockOptimistic 
     
    Set objParam = objCmd.CreateParameter("@aParam", adDate, adParamInput, 0, 0) 
     
    objCmd.Parameters.Append objParam 
    objCmd.Parameters("@aParam") = myParameter 
    objCmd.Execute Rows 
     
    With objRS 
        .Open sSQL, objConn 
    End With 
     
    With Sheets(3) 
        .Cells(3, 1).CopyFromRecordset objRS 
    End With 
     
    objConn.Close 
     
     
End Sub
I hope this is possible..