August 10, 2005 at 8:17 am
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.
August 11, 2005 at 1:46 am
I normally use a temp table to store the results of the query then use the table in the transfertext method.
August 11, 2005 at 2:07 am
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.
August 12, 2005 at 1:19 am
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy