TransferText - Export

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My problem is that the same basic query needs to be run from different
tables. How can I create a query at run time and code it to the
docmd.transfertext command. I have only been able to run a stored query.

dan
 
Here's some generic code to create a new query from an existing query
(alternatively, you can write your own SQL statement and use it to make the
new query), append it to QueryDefs collection, export it, and then delete
it.

Dim qdf As DAO.QueryDef, qds As DAO.QueryDef
Dim dbs As DAO.Database
Dim intLoop As Integer
Dim strSQL As String, strQ As String

Set dbs = CurrentDb

' Use next four steps if the new query is being copied from
' an existing query
'Set qds = dbs.QueryDefs("QueryBeingUsedAsTemplate")
'strSQL = qds.SQL
'qds.Close
'Set qds = Nothing

' Use next step if you want to build the SQL string for the new query
'strSQL = "Your SQL statement goes here"

strQ = "NameOfNewQuery"
Set qdf = dbs.CreateQueryDef(strQ, strSQL)
qdf.Close
DoCmd.TransferText acExportDelim, "SpecificationName", qdf.Name, _
"ExportToFile.txt", True
dbs.QueryDefs.Delete strQ
Set qdf = Nothing
dbs.Close
Set dbs = Nothing
 
Ken,
thanks for the quick response. I am using ado not dao.
Is the best way to solve the problem create a temp query? If so, how do I do
that?

Dan
 
Use DAO instead of ADO for this particular action.

The "second" option in the code that I posted shows how to create a
temporary query (see the line with CreateQueryDef in it).
 
Back
Top