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

vbvba

New Member
Joined
May 25, 2012
Messages
34
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.

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.:biggrin:
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
If the text file has field names, why are you using
Code:
hasfieldnames:=False
?
 
Upvote 0
If the text file has field names, why are you using
Code:
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. :(
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top