transfer text query parameter

[Guest]

Using the TransferText command I want to specify the query from VBA. Reason
is I want to pass a variable to the where cause from the myArr array object.

I have tried this in my attempt of myStr2 but I get run-time error 3011 i.e.
make sure the object exists.

Am I heading in the right direction? Or should I do this another way?

Bruce

Option Compare Database
Private Function destdir()
destdir = CurrentProject.path & "\Export Sec\"
End Function

Function export_Sec_List()

Dim mySpec As String, myQuery As String

mySpec = "spec_sec_List"
' myQuery = "q_XEJ_List"
myArr = Array("XEJ")

myStr2 = "SELECT ASXCode, CompanyName FROM tbl_Company GROUP BY ASXCode,
CompanyName ORDER BY ASXCode;"

For Each a In myArr
DoCmd.TransferText acExportDelim, mySpec, myStr2, destdir &
"Security_List" & a & ".txt", False, ""
MsgBox ("Finished")
Next a


End Function

 

[Guest]

This mystr2 should be stored in a temporary query by using querydefs and then
you use this temp query to be exported.

e.g.

QueryDefs("mytempquery").sql=mystr2
querydefs.refresh
DoCmd.TransferText acExportDelim, mySpec, "mytempquery", destdir &
"Security_List" & a & ".txt", False, ""

- Raoul