Using TransterText

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

Hi, I have a hybrid Access/VB6 question which I asked in a VB forum but
was suggested to try asking here as well.

if I want to use in my VB code:

DoCmd.TransferText [transfertype][, specificationname], tablename,
filename[, hasfieldnames][, HTMLtablename][, codepage]

to export the contents of a query, I can go like:

DoCmd.TransferText acExportDelim, , _
"Query1", App.Path & "\test.txt", -1

and that all works fine.

My question now is, if I change my Query1 so that it takes parameter(s),
how do I pass parameter(s) within that DoCmd statement, if it is even
possible? Thanks.
 
The answer is you can't. One solution is to modify the QueryDef object and
insert the parameter values directly before you execute the TransferText.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thanks John, I don't quite follow ... I can, in VB code, pass the
parameter values and execute the query but I don't know how I can pass
the results that are returned, which is a Recordset, to the TransferText
- the TransferText is expecting either a database or query name String.
Thanks again.
 
Sure. You can open the QueryDef object, set its parameters, and then open a
recordset on the querydef. TransferText is dumb - it accepts only a table
name or a query name. It cannot accept a recordset with resolved
parameters, and, because it executes independently of your code, it has no
way to resolve the parameters.

The only workaround is to create a query with the parameters resolved and
then pass that query name to TransferText.

For example, if your query is:

SELECT *
FROM Gorp
WHERE Gorp.SomeField = [My Parameter]

Do this:

Dim db As DAO.Database, qd As DAO.QueryDef

Set db = CurrentDB
Set qd = db.CreateQueryDef("TempForTransfer")
qd.SQL = "SELECT * FROM Gorp " & _
"WHERE Gorp.SomeField = " & intMyParameter
qd.Close
DoCmd.TransferText acExportDelim, , _
"TempForTransfer", App.Path & "\test.txt", -1
DoEvents
db.QueryDefs.Delete "TempForTransfer"

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top