using docmd.transfertext cant seem to use sql for input

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

Guest

I want to create a file. I found DoCmd.TransferText which does what I want.
Problem is I need to only take one row at a time from table, so I created sql
to do that but the input area of the TransferText throws error saying Jet
cannot find the object.
 
TransferText can export a stored query or a table; cannot use an SQL
statement as the source. You'll need to save the SQL statement as a query
and then do the export. If you don't want to keep the query permanently
after you do the export, use code similar to this to create and then destroy
the query:

EXAMPLE FOR HOW TO CREATE A NEW QUERY IN ORDER TO CONTROL THE QUERY'S
CONTENTS AND NAME, AND TO EXPORT IT TO A TEXT FILE USING
"TRANSFERTEXT" COMMAND.
-------------------------

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
 
Thank you very much. I am studying hard, but had avoided the qdf to this
point.
I am very appreciative of the time you took to explain this.
Thank you Ken Baker
 
Back
Top