TransferText, but only spec. record

[vittorio0]

I'd like to Output a certain record from a table into a Text file. GlbLastWip is the criteria

Dim strSQL As String
strSQL = "SELECT * FROM [tbl_Zeichnungen] WHERE ([F23]='" & GlbLastWip & "');"
doCmd.TransferText acExportDelim, "ExpRec_q", strSQL, "C:\temp\ExpRec_q.txt"

why does this not work?, i'm getting error 3011

Thanks for your help
vittorio0

[NeoPa]

DoCmd.TransferText() requires the name of a table or QueryDef object. It will not work with a simple SQL string as you've tried to use. If you want a QueryDef to select a single record as per your SQL then you'll probably need to filter it by a control on a form you know to be open.

NB. Please, when asking for help with code that has failed, include the error message with the question. The error number is of very limited help for those trying to assist you.

[vittorio0]

Thansk a lot for your answer, I have tried that and it works. When I try to create the Exportspecification with the filter in place, it comes up with the errormessage "1 Parameter was excepted, to few were supplied" (translated from German) any idea?

[NeoPa]

I would guess that the form wasn't open and available when you were trying to create it. Am I right?

[vittorio0]

Yes, thats right, how can I have the form open and at the same time be in edit mode at the querydef.

[vittorio0]

I managed to have the form open and tried to create the exportspecification, however the error remains the same :(

[NeoPa]

In that case I'll need some more info from you :

1. The Name & SQL of the QueryDef.
2. Details of your Export Specification.

Before we get to that though, I assume you've already checked that the QueryDef runs perfectly when opened in Datasheet view? Obviously that should be checked before trying to export it.

[vittorio0]

The QueryDef runs ok when the form is opened and Returns the proper record.

1. The Name of the QueryDef is ExpRec_q and the SQL of it:

SELECT tbl_Zeichnungen.*
FROM tbl_Zeichnungen
WHERE (((tbl_Zeichnungen.F23)=[forms]![ZeichnungSuchen_f]![dwgname]));
 

The Error comes up after Rightklick - Export - Textfile, I can set the Output file and after the error pops up. I like to specify that the first line has Labels and the file is Tab separated.

[NeoPa]

Frankly, I can't think why it would complain in those circumstances, and without direct access to your session I'm unlikely to see enough to determine the problem. However, you should be able to get around the issue.

Do a temporary update of your QueryDef to say :

SELECT tbl_Zeichnungen.*
FROM tbl_Zeichnungen
WHERE ([F23]='')

Use the correct format for the literal. I guessed string but it may be numeric. It's probably easiest and safest to use an valid value in there. Now, use this to create and save your export specification.

Once that's done you can revert to your earlier SQL and use it.

[zmbd]

:-)

How about the following? Open a simple text file using standard IO, open the recordset, step thru the fields (there is a way to pull the record; however, it can get messy depending on the contents) and either directly write the value to the text file or build a string (as I've done here) and write it all at once.
(I've not put a lot of commenting here, I think the code is fairly straight forward; however, if one wants I can go back and comment :) )

>Note that one will have to modify the zPath string to point to the correct location. I've not tested for the non-existence of the path nor read/write/modify privilege.
(( this modification comes to mind :) home > topics > microsoft access / vba > insights > select a file or folder using the filedialog object to allow the user to select at least the correct directory... ))
>One can change this to a function and/or add arguments to make this more generic.

Sub getandwrite()
    Dim zdb As DAO.Database
    Dim zrs As DAO.Recordset
    Dim zflds As DAO.Fields
    Dim zfld As DAO.Field
    Dim zSQL As String
    Dim zFNum As Long
    Dim zPath As String
    Dim zFailSafe As Long
    '
    Set zdb = CurrentDb
    '
    zSQL = "SELECT tbl_EventHistory.EventHistory_pk, tbl_EventHistory.fk_tbluser FROM tbl_EventHistory;"
    Set zrs = zdb.OpenRecordset(Name:=zSQL, Type:=dbOpenSnapshot)
    If zrs.RecordCount Then
        '
        Set zflds = zrs.Fields
        '
        'Now one can check for the existence of a file
        'and open for append, or delete using the stdIO
        'I just appended the current date-time-seconds 
        zPath = "C:\Users\[accountname]\Documents\" & _
            "\My Documents\" & _
            "MS_Database_Projects\BytesWork\" & _
            "testoutput_" & Format(Now(), "yyyymmddss") & ".txt"
        zFNum = FreeFile
        Open zPath For Output As zFNum
        '
        '
        zrs.MoveFirst
        zSQL = ""
        zFailSafe = 0
        Do
            For Each zfld In zflds
                zSQL = zSQL & zfld.Value & ","
            Next zfld
            zSQL = Left(zSQL, (Len(zSQL) - 1)) & ":"
        '    Debug.Print zSQL
            Print #zFNum, zSQL
            zSQL = ""
            zrs.MoveNext
            '
            zFailSafe = zFailSafe + 1
            If zFailSafe >= 1000 Then Err.Raise Number:=-2147220991, Source:="RecordLoop", Description:="Record loop fail safe at 1000"
        Loop Until zrs.EOF
    Else
        Err.Raise Number:=-2147220992, Source:="Record Source", Description:="No records in source for output"
    End If
    '
zcleanup:
    Close 'close any active text files
    If Not zflds Is Nothing Then Set zflds = Nothing
    If Not zrs Is Nothing Then
        zrs.Close
        Set zrs = Nothing
    End If
    If Not zdb Is Nothing Then Set zdb = Nothing
zeexit:
Exit Sub
Zerrtrap:
    MsgBox Prompt:="ErrS: " & Err.Source & vbCrLf & _
        "ErrN: " & Err.Number & vbCrLf & _
        "ErrD: " & Err.Description, _
        Title:="Oh Bother an Error"
    If zFailSafe < 0 Then Resume zeexit
    zFailSafe = -1000
    Resume zcleanup
End Sub

From test table the text file output is simply:
1,1:
2,2:

or with the strings
1,Dummy1:
2,Dummy2:

>> NOTICE << no quotes around the string values. You could add additional logic to test for the data-type of the field and add quotes as needed.

Modify the string output as needed:
home > topics > microsoft access / vba > insights > vba standard text file i/o statements