VBA + TransferText + SpecificationName

Question

The following is what my DoCmd.TransferText looks like

oCmd.TransferText TransferType:=acExportDelim, _
            SpecificationName:="Schema.ini", _
            TableName:="BASIC_DATA_Query", _
            FileName:="BASIC_DATA_Query_Result.txt", _
            HasFieldNames:=False

When I give the "schema.ini" as my specification name, I get an error

"Run-time error '3625': The text file specification 'Schema.ini' does not exist. You cannot import, export, or link using the specification."

Even after referring to this article: http://support.microsoft.com/kb/241477

I have not been able to resolve the problem. My 'Schema.ini' is in the same folder as the DB.

BASIC_DATA_Query - is a query which has all my results and I need that to be exported to the file BASIC_DATA_Query_Result.txt with header and fields separated by tabs.

What is the possible solution?

score 1 · Accepted Answer · answered Apr 06 '11 at 16:12

There seems to be a problem with VBA, a know issue since 2004 :(.

Anyway I wrote code to export and that resolved my problem. Am posting the code here for anyone else

Sub ExportTextFileDelimited(FileName As String, _
    DataSet As String, _
    Delimiter As String, _
    TextQualifier As String, _
    WithFieldNames As Boolean)
    On Error GoTo ExportTextFile_Err

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    Dim Directory As String
    Dim MyString As String, strSQL As String
    Dim strDS As String
    Dim I As Integer

    Open FileName For Output As #1
    Set cnn = CurrentProject.Connection

    rst.Open DataSet, cnn, adOpenForwardOnly, adLockReadOnly
    If WithFieldNames Then
        For I = 0 To rst.Fields.Count - 1
            MyString = MyString & TextQualifier & rst(I).Name & TextQualifier & Delimiter
        Next I
        MyString = Left(MyString, Len(MyString) - 1)
        Print #1, MyString
    End If
    rst.MoveFirst
    Do While Not rst.EOF
        MyString = ""
        For I = 0 To rst.Fields.Count - 1
            'check for text datatype (202)
            If rst(I).Type = 202 Then
                MyString = MyString & TextQualifier & _
                rst(I) & TextQualifier & Delimiter
            Else
                MyString = MyString & rst(I) & Delimiter '<----
            End If
        Next I
            MyString = Left(MyString, Len(MyString) - 2) '<---
        Print #1, MyString & TextQualifier
        rst.MoveNext
    Loop

ExportTextFile_Exit:
    ' Close text file.
    Close #1
    rst.Close
    Set cnn = Nothing
    Exit Sub
ExportTextFile_Err:
    MsgBox Err.Description
    Resume ExportTextFile_Exit
End Sub

Usage:

Call ExportTextFileDelimited("C:\Query.txt", "Query", vbTab, """", True)

VBA + TransferText + SpecificationName

1 Answers1

Linked