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:

healey21

Well-known Member
Joined
Dec 22, 2009
Messages
900
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,182
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If the text file has field names, why are you using
Code:
hasfieldnames:=False
?
 

vbvba

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,182
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

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

Forum statistics

Threads
1,078,353
Messages
5,339,720
Members
399,320
Latest member
sut3k

Some videos you may like

This Week's Hot Topics

Top