docmd.transfer text using stored procedure

[GShen]

Tried posting in other forums, maybe this is the correct one to post in.
I can't believe this will not work.
Using an ADP. backend stored procedures. have done this million times using a table. Can you substitute a stored procedure returning the data you want in place of the table? 100 people on the net say yes, 100 say no. The syntax says you can use a query. Does a query mean a sp as well? 1 person said create a query in vb. I could not do that because there was no DIM for querydef. Do I need another ref. as an add-in ?
They gave me the following but I could DIM the field qry, hence the rest is useles..

Dim qry As querydef
Dim sqlStmt As String

sqlStmt = "SELECT tblepoftp_Data " & _
"FROM tblEDIPOFTP"

Set qry = CurrentDb().QueryDefs("qryTemp")
qry.sql = sqlStmt

DoCmd.TransferText acExportFixed, , qry.sql, FTPAddress

....thanks.

Remember when... everything worked and there was a reason for it?

 

[PHV]

maybe this is the correct one to post in
I'd post here:
Forum958

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GShen]

Thanks. I also found out that I needed an AD IN. That helped let me Dim a field as a querydef but the SET statement still gives me an error. Somebody in the world has to be doing this.
My other option is to call a stored procedure which fires off a job which fires off a SSIS package with parameters,etc. etc. Can't wait till that goes down and you have to try and fix or modifty it..... Hence, back to the transfertext.

Remember when... everything worked and there was a reason for it?