transfertext with a vby defined query 1

[davikokar]

Hallo,
I'm trying to use the transfertext method but the problem is that the data doesn't come from a table or a stored query but from a query defined in vba.
I don't know if the trasfertext requires a stored query.

DoCmd.TransferText ,, tablename

if I defined my query like that:

dim query1 as string
query1 = "SELECT ...."

and then I try to use the transfer text with "query1", I got an error.

Do you know why?
thanks

 

[PJStephenson]

TransferText looks for a table or query, which you don't have. You do have a couple of options.
You could create a temporary query and set it's SQL to your query1 value.
Or you could write directly from a recordset to a text file using the FileSystemObject or just the standard Open, Write, Close commands.

Peter

 

[davikokar]

Hi Stephenson,
my vba defined query is quite complicated: it's the result of few strings choosed programatically with if..then. It means that is not always the same query. I don't think I can write if..then sentences in a query, but maybe I can transfer the final string result to a temporary query and then use that query...
So my question is: how do I write a string to a temp query from VBA? Can you give me a hint? thanks

 

[PJStephenson]

This is DAO code for creating a Query

Dim objQueryDef As QueryDef
Set objQueryDef = New QueryDef
objQueryDef.Name = "TempQuery"
CurrentDb.QueryDefs.Append objQueryDef
CurrentDb.QueryDefs("TempQuery").SQL = strSQLquery

and to remove try
CurrentDb.QueryDefs.Delete "TempQuery"
HTH
Peter