Hi,
I'm a bit confused with the use of stored procedures: stored procedures can produce a dataset, but don't create access queries.
If you want to use a stored procedure, I have an example of code that uses stored procedures to produce an output to Excel:
Code:
If strReport = "rpt_StockList" Then
strReport = "exec p_exportStockList " & Me!subCriteria.Form.getWarehouse()
ElseIf strReport = "rpt_StockListDangerCodes" Then
strReport = "EXEC p_exportDangerStockList " & Me!subCriteria.Form.getWarehouse()
ElseIf strForm = "rpt_StockListRClauses" Then
intDGO = Nz(Me!subCriteria.Form!chkDangercodes, 0)
strForm = "EXEC p_exportDangerClauseStockList " & Me!subCriteria.Form.getWarehouse() & ",'R'," & intDGO
ElseIf strReport = "rpt_StockListCust" Then
strReport = "EXEC p_exportCustStockList " & Me!subCriteria.Form.getCustomer()
ElseIf strForm = "rpt_Moves_PO" Then
strForm = "p_exportMovesPO"
ElseIf strForm = "rpt_Moves_IO" Then
strDate1 = Me!subCriteria.Form.getCritDate(1)
strDate2 = Me!subCriteria.Form.getCritDate(2)
strCrit = CStr(Me!subCriteria.Form.getCustomer()) & ",'" & strDate1 & "','" & strDate2 & "'"
strReport = "EXEC p_exportMovesIO " & strCrit
ElseIf strReport = "xlsEnvironmentRpt" Then
strReport = "EXEC p_EnvironmentRpt"
End If
strFilename = gfgSaveFile("Save as", "Excel files(*.xls)|*.xls")
If Len(strFilename) = 0 Then
MsgBox "No valid file"
Exit Sub
End If
If Right(strFilename, 4) <> ".xls" Then strFilename = strFilename & ".xls"
DoCmd.OutputTo acOutputStoredProcedure, strReport , "MicrosoftExcel(*.xls)", strFilename, True
Of course to view the procedures you need an ADP project and not an mdb database.
Greetings
NG