[RESOLVED] ADO - Append Parameter to query stored as text file

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..

Welcome to VBForums :wave:

The problem is basically that objCmd and objRS are not linked in any way (which is how it should be, as you can have multiple recordsets etc).

The way you are executing objCmd means that the results are ignored (unless it is an action query, such as a DELETE), and the way you are opening objRS means that it ignores objCmd (and thus the parameters).

To see how it should be done, see the article How do I use an ADO Command object? from our Database Development FAQs/Tutorials (at the top of this forum).

Ahh that makes sense. I originally put this code together for a DELETE query, and just reused the same structure.

I am looking at your thread now.

Thanks for the response!

That was an excellent write up! (Bookmarked)

Here is the working code. As you said, I hooked up the Command object to the RecordSet object, and then using your guide I was able to tweak my paramater properties. Very excited to start using ADO this way. I will use MS Query to build connections no more!

Code:

---

Sub AdoTest

Dim objConn As ADODB.Connection, objCmd As ADODB.Command, objRS As ADODB.Recordset, fld As ADODB.Field
Dim a As String, stConn, sSQL, sPath As String
Dim x As Integer
Dim ReviewDate As Range, MAnalyst

    Set ReviewDate = Sheets(1).Range("G1")
    Set MAnalyst = Sheets(1).Range("G2")
    Set objConn = New ADODB.Connection
    Set objCmd = New ADODB.Command
    Set objRS = New ADODB.Recordset
   
    sPath = "C:\Users\Desktop\SQL.txt"
    a = "C:\Users\Desktop\Reporting Database.mdb"
    sSQL = LoadTextFile(sPath)
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & a & ";"
   
'=======================================================================
With objRS
    .CursorType = adOpenForwardOnly
    .LockType = adLockOptimistic
End With

objConn.Open (stConn)

With objCmd
    .ActiveConnection = objConn
    .CommandType = adCmdText
    .CommandText = sSQL
    .Parameters.Append .CreateParameter("MA", adVarChar, adParamInput, 30, MAnalyst.Text)
    .Parameters.Append .CreateParameter("DateRev", adDate, adParamInput, , ReviewDate)
    Set objRS = objCmd.Execute
End With

x = 1
For Each fld In objRS.Fields
    Sheets(3).Cells(2, x) = fld.Name
    x = x + 1
Next fld

With Sheets(3)
    .Cells(3, 1).CopyFromRecordset objRS
End With
 
objConn.Close
   
   
End Sub

---

That looks good to me :)


As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).