Transfer Text method in VBA

• wm. david wynn

  SSC-Addicted

  Points: 404

  More actions

  August 10, 2005 at 8:17 am

  #69502

  I'm trying to create a fixed length text file as a result of a query using the 'Transfer Text' method. I have a query that prompts for two input parameters. These will select the records that I want to output. It displays the records on the screen correctly. I want these same records to be output as a fixed length text file. Using 'Transfer Text' in VBA, I put in the table name the query displays the correct records but the whole table is output to a fixed length file. If I specify the stored procedure name, the query displays the correct records and then I get a message that says that VBA can't find the stored procedure. If I don't include a table name, I get a message that says that "The action or method requires a Table Name argument". I haven't figured out how to specify that just the output of the query and not the whole file should be output as text.

  Here's my code....

  Private Sub FileOut_Click()

  On Error GoTo Err_FileOut_Click

      Dim stDocName As String

      stDocName = "File Submittal"

      DoCmd.OpenStoredProcedure stDocName, acViewNormal, acEdit

      DoCmd.TransferText acExportFixed, , "MBS Input", "C:\Documents and settings\dwynn\desktop\m1234567-999.txt"

  Exit_FileOut_Click:

      Exit Sub

  Err_FileOut_Click:

      MsgBox Err.Description

      Resume Exit_FileOut_Click

     

  End Sub

  ----------------------------------

  Any help will be greatly appreciated. Thanks.

• DKeeling

  Old Hand

  Points: 385

  More actions

  August 11, 2005 at 1:46 am

  #581297

  I normally use a temp table to store the results of the query then use the table in the transfertext method.

• jfmccabe

  SSCrazy

  Points: 2527

  More actions

  August 11, 2005 at 2:07 am

  #581305

  Use a pass through query and export that. You need to add code something like this:

      Dim db As DAO.Database
      Dim qdf As DAO.QueryDef
      
      Set db = CurrentDb
      Set qdf = db.QueryDefs("qpTest")
      qdf.SQL = "File Submittal " & param1 & "," & param2

      DoCmd.TransferText acExportFixed, , "qpTest", "C:\Documents and settings\dwynn\desktop\m1234567-999.txt"
  

  I am assuming that the pass through query is called qpTest and that you have your two variables in param1 and param2.

• Mike Jensen-169059

  SSC Enthusiast

  Points: 113

  More actions

  August 12, 2005 at 1:19 am

  #581668

  I agree with jfm on one point.  Use a pass through query but don't use DAO.  ADO is the standard and you will have a much easier time later on.

  Try this

  dim rsProc as new adodb.recordset

  dim strSQL as string

  strsql = "qpttest,(parameter1),Parameter2"

  set rsProc =  currentproject.connection.execute (strsql)

  DoCmd.TransferText acExportFixed, , rsProc, "C:\Documents and settings\dwynn\desktop\m1234567-999.txt"

  Mike