passing params to multiple querydefs and transferspreadsheet

[Doug Glancy]

Hi,

I know how to transfer params from a form to a querydef. And I know how to
use TransferSpreadsheet to put query results into spreadsheet. What I'd
like to do is combine the two for multiple queries, e.g.:

Open my form
1 Set a Querydef equal to an existing Access query
2 Pass the params from the form to the Querydef
3 Transfer the Querydef result to the Excel workbook using
TransferSpreadsheet
Repeat 1 to 3

When I try to do this TransferSpreadsheet seems to just call the Access
query - not my Querydef - and prompts for the parameters.

Is this possible?

Thanks in advance,

Doug

 

[Doug Glancy]

I've been googling some more and it seems that I have at least two choices:

1 Stick the SQL from my existing query into a new query and replace the
params with the values from the form
2 Convert my Access queries to MakeTable queries and pass them using
TransferSpreadsheet

Does that sound right?

Doug

 

[Ken Snell \(MVP\)]

Here's one example of code that exports a parameter query to EXCEL using
TransferSpreadsheet:

Export a parameter query to EXCEL via TransferSpreadsheet
---------------------------------------------------------

The query must be created with the parameter values already in the
SQL string, and then that query must be exported.


Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb
strSQL = "SELECT QDadosParaExcel.* FROM QDadosParaExcel " & _
"WHERE QDadosParaExcel.DataDados>=" & _
Format(Forms!FrmdlgExcel!DTPFromDate,"\#mm\/dd\/yyyy\#") & _
" And QDadosParaExcel.DataDados<=" & _
Format(Forms!FrmdlgExcel!DTPToDate,"\#mm\/dd\/yyyy\#") & "';"
strQDF = "PutAMeaningfulNameForTheQueryHere!!!!"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

 

[°¢·á]

Here's one example of code that exports a parameter query to EXCEL using
TransferSpreadsheet:

Export a parameter query to EXCEL via TransferSpreadsheet
---------------------------------------------------------

The query must be created with the parameter values already in the
SQL string, and then that query must be exported.


Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb
strSQL = "SELECT QDadosParaExcel.* FROM QDadosParaExcel " & _
"WHERE QDadosParaExcel.DataDados>=" & _
Format(Forms!FrmdlgExcel!DTPFromDate,"\#mm\/dd\/yyyy\#") & _
" And QDadosParaExcel.DataDados<=" & _
Format(Forms!FrmdlgExcel!DTPToDate,"\#mm\/dd\/yyyy\#") & "';"
strQDF = "PutAMeaningfulNameForTheQueryHere!!!!"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

 

[°¢·á]

Here's one example of code that exports a parameter query to EXCEL using
TransferSpreadsheet:

Export a parameter query to EXCEL via TransferSpreadsheet
---------------------------------------------------------

The query must be created with the parameter values already in the
SQL string, and then that query must be exported.


Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb
strSQL = "SELECT QDadosParaExcel.* FROM QDadosParaExcel " & _
"WHERE QDadosParaExcel.DataDados>=" & _
Format(Forms!FrmdlgExcel!DTPFromDate,"\#mm\/dd\/yyyy\#") & _
" And QDadosParaExcel.DataDados<=" & _
Format(Forms!FrmdlgExcel!DTPToDate,"\#mm\/dd\/yyyy\#") & "';"
strQDF = "PutAMeaningfulNameForTheQueryHere!!!!"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

 

[Doug Glancy]

Ken,

Thanks. As I mentioned, I want to take a parameter query and substitute
values in a form for the parameters. I took your code and tried to do a
simple replace, like so:

Sub AnotherTest()
Dim qdfTest As DAO.QueryDef
Dim strNewQuerySQL As String
Dim qdfNew As DAO.QueryDef

Set qdfTest = CurrentDb.QueryDefs("qryTester2")
strNewQuerySQL = Replace(qdfTest.SQL, "[test]", 2)
Set qdfNew = CurrentDb.CreateQueryDef("TransferTest", strNewQuerySQL)
qdfNew.Close
Set qdfTest = Nothing
Set qdfNew = Nothing
DoCmd.TransferSpreadsheet transfertype:=acExport, tablename:=strNewQuerySQL,
filename:="testTransfer.xls"
CurrentDb.QueryDefs.Delete "TransferTest"
End Sub

On the TransferSpreadsheet line I get the error:
Microsoft jet database engine could not find the object" ... followed by my
SQL string.

What do think I'm doing wrong?

Also, I'm curious, why does the code create a new QueryDef but then just use
the SQL string?

Thanks,

Doug

 

[°¢·á]

Here's one example of code that exports a parameter query to EXCEL using
TransferSpreadsheet:

Export a parameter query to EXCEL via TransferSpreadsheet
---------------------------------------------------------

The query must be created with the parameter values already in the
SQL string, and then that query must be exported.


Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb
strSQL = "SELECT QDadosParaExcel.* FROM QDadosParaExcel " & _
"WHERE QDadosParaExcel.DataDados>=" & _
Format(Forms!FrmdlgExcel!DTPFromDate,"\#mm\/dd\/yyyy\#") & _
" And QDadosParaExcel.DataDados<=" & _
Format(Forms!FrmdlgExcel!DTPToDate,"\#mm\/dd\/yyyy\#") & "';"
strQDF = "PutAMeaningfulNameForTheQueryHere!!!!"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

 

[Doug Glancy]

Ken,

I got it. My query was a simple one, but I had used an asterisk and then
had specified one of the fields again to compare my parameter to, like this:
SELECT tblTester2.*, tblTester2.Tester2
FROM tblTester2
WHERE (((tblTester2.Tester2)=[test]));

When I unchecked the field being compared, like this, and it works:
SELECT tblTester2.*
FROM tblTester2
WHERE (((tblTester2.Tester2)=[test]));

I also used the new query name, instead of the SQL string, which didn't make
sense to me. If I use the SQL string, I get the same error as in my last
post, but if I use the name of the new query it works fine:

Sub AnotherTest()

Dim qdfTest As DAO.QueryDef
Dim strNewQuerySQL As String
Dim qdfNew As DAO.QueryDef

Set qdfTest = CurrentDb.QueryDefs("qryTester2")
strNewQuerySQL = Replace(qdfTest.SQL, "[test]", 3)
Set qdfNew = CurrentDb.CreateQueryDef("TransferTest", strNewQuerySQL)
qdfNew.Close
Set qdfTest = Nothing
Set qdfNew = Nothing
DoCmd.TransferSpreadsheet transfertype:=acExport, tablename:=strNewQuerySQL,
filename:="testTransfer.xls"
CurrentDb.QueryDefs.Delete "TransferTest"

End Sub

Now, I'll hook it up to my form and hopefully that will work too.

Thanks,

Doug

Ken,

Thanks. As I mentioned, I want to take a parameter query and substitute
values in a form for the parameters. I took your code and tried to do a
simple replace, like so:

Sub AnotherTest()
Dim qdfTest As DAO.QueryDef
Dim strNewQuerySQL As String
Dim qdfNew As DAO.QueryDef

Set qdfTest = CurrentDb.QueryDefs("qryTester2")
strNewQuerySQL = Replace(qdfTest.SQL, "[test]", 2)
Set qdfNew = CurrentDb.CreateQueryDef("TransferTest", strNewQuerySQL)
qdfNew.Close
Set qdfTest = Nothing
Set qdfNew = Nothing
DoCmd.TransferSpreadsheet transfertype:=acExport,
tablename:=strNewQuerySQL, filename:="testTransfer.xls"
CurrentDb.QueryDefs.Delete "TransferTest"
End Sub

On the TransferSpreadsheet line I get the error:
Microsoft jet database engine could not find the object" ... followed by
my SQL string.

What do think I'm doing wrong?

Also, I'm curious, why does the code create a new QueryDef but then just
use the SQL string?

Thanks,

Doug

Here's one example of code that exports a parameter query to EXCEL using
TransferSpreadsheet:

Export a parameter query to EXCEL via TransferSpreadsheet
---------------------------------------------------------

The query must be created with the parameter values already in the
SQL string, and then that query must be exported.


Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb
strSQL = "SELECT QDadosParaExcel.* FROM QDadosParaExcel " & _
"WHERE QDadosParaExcel.DataDados>=" & _
Format(Forms!FrmdlgExcel!DTPFromDate,"\#mm\/dd\/yyyy\#") & _
" And QDadosParaExcel.DataDados<=" & _
Format(Forms!FrmdlgExcel!DTPToDate,"\#mm\/dd\/yyyy\#") & "';"
strQDF = "PutAMeaningfulNameForTheQueryHere!!!!"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

 

[Ken Snell \(MVP\)]

DoCmd.TransferSpreadsheet transfertype:=acExport,
tablename:=strNewQuerySQL, filename:="testTransfer.xls"
CurrentDb.QueryDefs.Delete "TransferTest"
End Sub

On the TransferSpreadsheet line I get the error:
Microsoft jet database engine could not find the object" ... followed by
my SQL string.

The TransferSpreadsheet action cannot accept a SQL string as the name of the
table or query being exported. You must use the actual name of the table or
query; TransferSpreadsheet then uses the SQL statement of the query (or
makes its own SQL statement for a table) to get the data for the export.