Running a dynamic query from a string variable 1

[zimmer9]

I execute the following sub procedure to generate a report in Excel based on a query I created via the Objects list Query using QBE.

Is there a way to run a similar function to take a string I created which I will call strDynamicSQL. This string contains a SQL statement based on listbox values that the user selected via a form. Now I want to run this dynamic SQL statement which I created in a string format and export the results of this dynamic query to an Excel spreadsheet.

Private Sub FallQueryLNToExcel()

Dim rstQueryFS As ADODB.Recordset
Dim objXL As Excel.Application
Dim objWS As Excel.Worksheet
Dim fld As ADODB.Field
Dim intCol As Integer
Dim intRow As Integer


Set rstQueryFS = New ADODB.Recordset

Set rstQueryFS = CurrentProject.Connection.Execute("qryLNFall", , adCmdStoredProc)
Set objXL = New Excel.Application
objXL.Workbooks.Add
strNextFile = GetNextFileName("C:\LEXFALL1.XLS")
objXL.ActiveWorkbook.SaveAs strNextFile
Set objWS = objXL.ActiveSheet

For intCol = 0 To rstQueryFS.Fields.Count - 1
Set fld = rstQueryFS.Fields(intCol)
objWS.Cells(1, intCol + 1) = fld.Name
Next intCol

intRow = 2
Do Until rstQueryFS.EOF
For intCol = 0 To rstQueryFS.Fields.Count - 1

objWS.Cells(intRow, intCol + 1) = _
rstQueryFS.Fields(intCol).Value
objWS.Cells.EntireColumn.AutoFit

Next intCol
rstQueryFS.MoveNext
intRow = intRow + 1
Loop


objXL.Visible = True

End Sub

BTW: My congrats to PHV for being acknowledged this week for all the great work in assisting others. I know PHV has helped me immensely.

 

[PHV]

You may try to simply replace "qryLNFall" by strDynamicSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[zimmer9]

I placed my string named strSQLPrefix containing Dynamic SQL into the following statement:

Set rstQueryFS = CurrentProject.Connection.Execute("strSQLPrefix", , adCmdStoredProc)

By using the debugger I discovered that this line caused an error as follows:

Runtime error '-2147217865(80040e37)';
The Microsoft Jet database engine cannot find the input table or query 'strSQLPrefix'. Make sure it exists and that it's name is spelled correctly.

I defined this string strSQLPrefix in the General Declaration Section as follows:

Option Compare Database
Public strSQLPrefix As String

Using the Immediate Window, when I get to the offending line, I can print the value of strSQLPrefix as follows so I know the string is available:

?strSQLPrefix
SELECT * FROM tblCustomers As C, tblProducts As P, tblStatesAll As S WHERE S.FallCycle = Yes AND C.DateLost <=DateSerial(2005-S.StocksFS,6,30) OR C.DateLost <=DateSerial(2005-S.CashFS,6,30) OR C.DateLost <=DateSerial(2005-S.IRAFS,6,30) AND C.DateOfBirth <=DateSerial(Year(Now())-70,1,1) OR (C.DateLost <=DateSerial(2005-S.BondsFS,6,30)) AND P.PropertyType IN('CASH','STOCKS','BONDS','IRAS')

However, the string strSQLPrefix is not recognized by the Microsoft Jet database engine.

 

[PHV]

And this ?
Set rstQueryFS = CurrentProject.Connection.Execute(strSQLPrefix, , adCmdStoredProc)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[zimmer9]

I tried the string name without the double quotes but I get the follwoing system response:

Same runtime error, namely:

Runtime error '-2147217865(80040e37)';
Expected query name after Execute.

but the accompanying message about the query name is different.

I can still print in the Immediate Window the string just before the offending line as follows:

?strSQLPrefix
SELECT * FROM tblCustomers As C, tblProducts As P, tblStatesAll As S WHERE S.FallCycle = Yes AND C.DateLost <=DateSerial(2005-S.StocksFS,6,30) OR C.DateLost <=DateSerial(2005-S.CashFS,6,30) OR C.DateLost <=DateSerial(2005-S.IRAFS,6,30) AND C.DateOfBirth <=DateSerial(Year(Now())-70,1,1) OR (C.DateLost <=DateSerial(2005-S.BondsFS,6,30)) AND P.PropertyType IN('CASH','STOCKS','BONDS','IRAS')

 

[zimmer9]

Correction: the Runtime error is as follows:

-2147217900(80040e14)

 

[zimmer9]

It seems to work fine when I write the statement as follows:

Set rstQueryFS = CurrentProject.Connection.Execute(strSQLPrefix)

thanks PHV

 

[PHV]

Replace adCmdStoredProc by adCmdText

Anyway you're in trouble with your generated WHERE clause as you mix AND & OR operators without proper parentheses ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244