Using TransterText

[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.

 

[John Viescas]

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)

 

[Dale]

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.

 

[John Viescas]

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)

 

[Dale]

Thanks a lot John, I'll give that a shot!