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