PDA

View Full Version : Solved: ADO Parameter query.. from text file!



mantooth29
10-11-2012, 02:17 PM
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 portableI 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 am fairly new to using ADO (a recent convert from customizing MS Query in VBA)


I know I can run paramaterized queries with ADO, but is it possible to pass variables 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

Any help or advice is appreciated!

mohanvijay
10-12-2012, 12:09 AM
you can modify your query (which gets from text file) for example

sSql=Replace(sSQL, "WHERE", "WHERE yourfieldname=fieldcriteria")

mantooth29
10-12-2012, 07:42 AM
Thank you for the suggestion, that definitely worked.

I could see that getting messy with several parameters though, and my goal is to keep SQL out of the Module completely.

However, if I cannot find a way to append the parameter on the incoming string command I can always use your idea.

Thanks!

mantooth29
10-12-2012, 11:34 AM
Thread now cross posted at VBForums

http://www.vbforums.com/showthread.php?694737-ADO-Append-Parameter-to-query-stored-as-text-file&p=4256843&posted=1#post4256843

stanl
10-18-2012, 07:59 AM
and my goal is to keep SQL out of the Module completely.


your module uses Cmd Execute and that is SQL. You could accomplish the same end with Connection Execute and replace strings in your text.

I have generally used a fabricated recordset with rather large queries (SELECTS,UPDATES,PIVOTS) in a memo data type, reading in replace parameters from additional fields in the recordset - the output going into Excel, .csv files, an access table. The neat thing is that the recordset uses the MSPersist provider (included with MDAC) and persists as an xml file which can be modified with code, or a text editor [even lowly Notepad].

Just .02

Stan

mantooth29
10-18-2012, 12:54 PM
Interesting that it persists as an XML file. What uses have you found for that feature? I may be working with XML data sources in the near future.

I misspoke regarding SQL. I just wanted to keep the SQL command statements out of my VBA module, not avoid SQL altogether. It's messy with line continuations (I have Iif statements that take up to 4 lines if I want to keep it visible without scrolling), less portable, and makes the module bigger than I would like.

BTW original question was resolved at cross posted forum.

http://www.vbforums.com/showthread.php?694737-ADO-Append-Parameter-to-query-stored-as-text-file&p=4256843&posted=1#post4256843 (http://www.vbforums.com/showthread.php?694737-ADO-Append-Parameter-to-query-stored-as-text-file&p=4256843&posted=1#post4256843)

addie
11-27-2012, 04:27 AM
The neat thing is that the recordset uses the MSPersist provider (included with MDAC) and persists as an xml file which can be modified with code, or a text editor [even lowly Notepad].