Field 'F1' doesn't exist in destination table "test" in ms access import text file

[vbvba]

Hi Guys,

I have a written a code for a button in a form to import text files with the following field names in the text file.

Title LastName Firstname Address City State

Please see the below code and help me in finding the issue with the code.

Private Sub Command27_Click()
Dim fdialog As FileDialog
Dim varFile As Variant
 
DoCmd.SetWarnings False
 
'Set up the File Dialog
Set fdialog = Application.FileDialog(msoFileDialogFilePicker)
With fdialog
  .AllowMultiSelect = False
  .Title = "Select a Text File to Import"
  .InitialFileName = "C:\*.txt"
 
  'Clear out any Filters, then Add you own (1)
  .Filters.Add "Text Files", "*.TXT"
 
  'Show the Dialog. If the Show Method returns True, the User picked
  'at least 1 File, otherwise the User clicked Cancel
  If .Show Then
    For Each varFile In .SelectedItems
      'There will be only 1 File selected, so place the proper Arguments into the TransferText Method
DoCmd.TransferText acImportDelim, , Tablename:="Test", FileName:="test.txt", hasfieldnames:=False
 Next
  Else
  'Nothing selected in File Dialog
  End If
End With
 
DoCmd.SetWarnings True
 
End Sub

When I click the button it throws the error "Field 'F1' doesn't exist in destination table "test"

Please advise me to proceed further.

 

[healey21]

There wont be an issue with the code I think you have to look at the database and do something like get data and then special and somewhere here you should be able to do what you need then all the automation's should work, but this needs to be set before you can test the code.

 

[RoryA]

If the text file has field names, why are you using

hasfieldnames:=False

?

 

[vbvba]

If the text file has field names, why are you using

hasfieldnames:=False

?

Hi Healey21 and RoryA thanks for your reply and suggestions on this code.

RoryA :- You have informed me to change the hasfieldnames to true then I encounter an error :-

Run time error 2391 field 'Title_lastName_Firstname_Address_City_State' doesn't exist in destination table

However, I have created table with these field names, the table name is "Test", still I experience this error.

Please give your advise to proceed further.

 

[RoryA]

What are the delimiters used in your text file? If they are underscores, then that is not standard which is probably why the code won't work. You would need to create an import specification in the database and refer to that, or use a completely different approach.

This seems to be more of an Access question so I will move this thread to that forum.

 

[vbvba]

What are the delimiters used in your text file? If they are underscores, then that is not standard which is probably why the code won't work. You would need to create an import specification in the database and refer to that, or use a completely different approach.

This seems to be more of an Access question so I will move this thread to that forum.

Thanks for your suggestion and advise.
However I'am not well versed with this forum.
Could you please tag this thread to MS access or any other database related forum.
Please help me

 

[techmichelle]

Found a work around. In Access, New Data Source, From File, Excel, Choose the file, Link to the data source by creating a linked table. From the linked table you can Copy and Paste the Table with Structure and Data.