Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004

    Unanswered: create and export query to txt from macros

    I need to programatically create and export sql query to text. I dont mean a query that allready exists, but rather one sql for which will also be set within my program.


    sql="select * from table1 where fieldname=" & variable
    then I need to export query created with that sql into a text file.

  2. #2
    Join Date
    Apr 2004
    Kingsland, Georgia
    Use DAO and a blank query you've created and saved:
    Dim qdf As DAO.QueryDef
    Dim strSQL as String
    strSQL = "SELECT * FROM tblWhatever ORDER BY fldYourMomma"
    'sets the query 
    Set qdf = CurrentDb.QueryDefs("qryYouMade")
    qdf.SQL = strSQL
    DoCmd.OutputTo acOutputQuery, "qryYouMade", acFormatText, "C:\text.txt", True
    'clean up & release resources
    Set qdf = Nothing
    give that a try. hope it helps. have a great day

  3. #3
    Join Date
    Sep 2004
    Thanks alot, it worked. As note to future readers I would add that you need to have the microsoft DAO library pluged in and I also changed the export method to DoCmd.TransferText so they both work.

    TransferText works in a VERY odd way i.e. if the results returned by query are relatively small then everything is ok - it creates a text file and puts the data inside. BUT. if the results are large then it gives me an error '3011' ( object fname not found by JET) and the intriguing part is that the file indeed disappears even if it existed before - without warning or anything. Any advise on how to fix this?

    Regarding OutputTo - it warks regardless of the size of queries resultset, but it only outputs file in a certain format i.e. delimited by | and _ , how can I change this to tab delim?
    Last edited by NaugGc; 11-02-04 at 06:45.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts