Schema.ini and Text Qualifier for Linked text file in Access RRS feed

  • Question

  • I had some issues lately with linking client text files that do not use a Text Qualifier into an Access database. If you link the text file manually, in Access, you can set the Text Qualifier to 'none'. If you use VBA with a Schema.ini to link the file (Excel to Access), it seems that the default Text Qualifier = " is used and you get different results.

    Is there any way to set the Text Qualifier for a file during the linking process or afterward for a text file linked in Access?

    Wednesday, January 20, 2016 6:16 PM

All replies

  • This is how I solved the problem...

    I manually linked to the text file and saved the Link Specification as "Amazon Data Link Specification". Then, from Excel I use this code in place of code that uses ADODB to reconnect the linked file:
    Sub RunSubInAccessDatabase()
        Dim sPathFileName As String
        Dim oApplication As Object

        sPathFileName = "C:\Amazon Program\Database\Text File\Amazon Data.txt"
        Set oApplication = CreateObject("Access.Application")
        With oApplication
            .OpenCurrentDatabase "C:\Amazon Program\" & "Test Import.accdb", False
            .DoCmd.DeleteObject 0, "Amazon Data"
            .DoCmd.TransferText 0, "Amazon Data Link Specification", "Amazon Data", sPathFileName, False

        End With

        Set oApplication = Nothing

    End Sub

    This seems like it will do the trick. And the Link Specification that was created manually retains the Text Qualifier value.

    Friday, January 22, 2016 1:43 AM