CODEWITHSUNDEEP
ms-accessimportms-access-2007vba
valki
valki

Reputation: 63

VBA Access import Field doesn't exist in destination - Fieldname is garbled

I'm importing a *.csv file into an Access table with the following code:

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

    If .Show = -1 Then

        DoCmd.TransferText TransferType:=acImportDelim, _
            TableName:="New References", _
            FileName:=.SelectedItems.Item(1), _
            HasFieldNames:=True

        TextReferenceImport = .SelectedItems.Item(1)
    Else
        TextReferenceImport = ""
    End If
End With

Set fd = Nothing

However, I get the following error:

Microsoft Visual Basic
Field 'Name' doesn't exist in destination table 'New references.'

Obviously, the field name in both Access and the *.csv file is "Name". This is also the first column of the *.csv file. I have also set HasFieldNames to False, and changed the first columns name to F1 in access, and that seemed to work so I don't think there is anything wrong with the import.

Why does access read these strange characters that are not present in the *.csv or table, that then disrupt the import?

Upvotes: 2

Views: 4350

Answers (1)

HansUp
HansUp

Reputation: 97100

Those  characters are the UTF-8 BOM (byte order mark).

Unless it's practical to strip them out of your CSV before doing the import, I think you will need to create an Import Specification.

Begin an import manually from the Access user interface. After selecting your CSV file, select "First Row Contains Field Names" from the second page of the "Import Text Wizard". Then click the "Advanced" button and select "Unicode (UTF-8)" for the "Code Page" property on the import specification dialog. And then click "Save As" to give your import specification a name.

Import Specification with Unicode (UTF-8) as Code Page

Then you can use that saved import specification when you call DoCmd.TransferText from VBA. Here is an example I tested in Access 2007 ...

DoCmd.TransferText TransferType:=acImportDelim, _
    SpecificationName:="YourTable Import Specification", _
    TableName:="YourTable", _
    FileName:="C:\Users\hans\Documents\YourTable.csv", _
    HasFieldNames:=True

Upvotes: 3

Related Questions