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

[mantooth29]

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

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)

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

[si_the_geek]

Welcome to VBForums

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

[mantooth29]

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!

[mantooth29]

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!

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

[si_the_geek]

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