DoCmd.TransferText (Run-time error '2391') (1 Viewer)

carterlw12

Registered User.
Local time
Today, 09:49
Joined
Oct 2, 2018
Messages
25
I am searched long and hard trying to find an answer to this and I have not.

- Importing .csv file via form button
- I do not want to edit the source file prior to importing
- Source file does not have headers

I have tried changing the code to include and not include headers and neither works. I get "F1 does not exist in destination table" or whatever the value of A1 in the source file does not exist in destination table.

I am self taught and about a month new to Access so just fyi when providing answers.

Here is my source code:

Code:
Private Sub cmdImport_Click()
Dim FSO As New FileSystemObject
'On Error GoTo BadFormat

If Nz(Me.txtFilepath, "") = "" Then
    MsgBox "No file has been selected. Please select a file."
    Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFilepath, "")) Then

    DoCmd.TransferText acImportDelim, , "ADP_tbl", Me.txtFilepath, False
    
    MsgBox "Import Successful!"
txtFilepath.Value = ""
    Exit Sub


'BadFormat:
'MsgBox "The file you tried to import was not a .csv file."

End If

End Sub
 

June7

AWF VIP
Local time
Today, 06:49
Joined
Mar 9, 2014
Messages
5,425
Would be helpful to provide sample file. Attach file to post.
 

carterlw12

Registered User.
Local time
Today, 09:49
Joined
Oct 2, 2018
Messages
25
CSV files can't be uploaded but here is a super basic version in excel format though.
 

Attachments

  • Sample.xlsx
    14.5 KB · Views: 488

June7

AWF VIP
Local time
Today, 06:49
Joined
Mar 9, 2014
Messages
5,425
Pretty sure can upload anything in a Windows Compression zip file.

Converted the Excel to csv. Ran your code (after modifying to not reference form and textbox for file). Import is successful. Repeat import also successful.
 

carterlw12

Registered User.
Local time
Today, 09:49
Joined
Oct 2, 2018
Messages
25
Did you set headers in the table prior to importing though? It imports fine if you don't put headers in the access table. But I need to label headers in the table first and then import.
 

June7

AWF VIP
Local time
Today, 06:49
Joined
Mar 9, 2014
Messages
5,425
I let the import create the table and therefore the field names as well. So just changed field names in table and then import fails. So I modified the csv to add header row of desired field names and set code to True. Import successful.

Apparently, if there is no header row in the file, the import insists on locating field names F1, F2, etc.

Options I see:
1. fix the csv file
2. import to 'temp' table then transfer records to primary table with SQL INSERT SELECT
 

carterlw12

Registered User.
Local time
Today, 09:49
Joined
Oct 2, 2018
Messages
25
Same results I got. Editing the CSV before hand is not an option. I mean it is, but not what I want.

After importing to a “staging table” how to I run an INSERT to the table that contains the headers I want?

Thanks for your info ps.
 

June7

AWF VIP
Local time
Today, 06:49
Joined
Mar 9, 2014
Messages
5,425
Use INSERT SELECT action syntax. In VBA like:

CurrentDb.Execute "INSERT INTO tablename(fieldname1, fieldname2, fieldname3) SELECT F1, F2, F3 FROM staging"

Then purge the staging table prior to next import:

CurrentDb.Execute "DELETE FROM staging"
 
Last edited:

carterlw12

Registered User.
Local time
Today, 09:49
Joined
Oct 2, 2018
Messages
25
I haven't been able to try this today but I will definitely give it a shot. It should work obviously, I just have to make it happen. lol thanks for the help. I'll be back if I need any more.
 

carterlw12

Registered User.
Local time
Today, 09:49
Joined
Oct 2, 2018
Messages
25
I am getting an error "Run-time error 3134:
Syntax error in INSERT INTO statement." I can't find anything wrong with it per your example.

Here is my code.

Code:
CurrentDb.Execute "INSERT INTO ADP_Person_Import_tbl(GlobalID, RecordEffDate, BirthDate, HireDate, ReHireDate, ServiceDate, SeniorityDate, TermDate, ShortName, FirstName, LastName, MI, Supervisor, EmpStatus, StatusEffDate, FT/PT, Reg/Temp, HomePhone, WorkPhone, PersonalEmail, WorkEmail, Region, Country, Division, Location, Dept, Job, OfficerCode, Union, FLSAInd, ADPPayGroup, TipInd, SpecialGroup, BaseWageRate, BaseWageEffDate, WeeklyHours, ADPFile, PTOPrem, Language, Address, City, State, ZipCode, Country2, JobDept, FileName)SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32, F33, F34, F35, F36, F37, F38, F39, F40, F41, F42, F43, F44, F45, F46 FROM ADP_staging_tbl"
 

carterlw12

Registered User.
Local time
Today, 09:49
Joined
Oct 2, 2018
Messages
25
Here is my entire section of code. Everything is working except the insert into the new table and then of course clearing out the staging table afterwards.

Code:
Private Sub cmdImport_Click()
Dim FSO As New FileSystemObject
Dim strSQL As String

[COLOR="black"][B]strSQL = "INSERT INTO ADP_Person_Import_tbl (GlobalID, RecordEffDate, BirthDate, HireDate, ReHireDate, ServiceDate, SeniorityDate, TermDate, ShortName, FirstName, LastName, MI, Supervisor, EmpStatus, StatusEffDate, FT/PT, Reg/Temp, HomePhone, WorkPhone, PersonalEmail, WorkEmail, Region, Country, Division, Location, Dept, Job, OfficerCode, Union, FLSAInd, ADPPayGroup, TipInd, SpecialGroup, BaseWageRate, BaseWageEffDate, WeeklyHours, ADPFile, PTOPrem, Language, Address, City, State, ZipCode, Country2, JobDept, FileName) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32, F33, F34, F35, F36, F37, F38, F39, F40, F41, F42, F43, F44, F45, F46 FROM ADP_staging_tbl;"[/B][/COLOR]


If Nz(Me.txtFilepath, "") = "" Then
    MsgBox "No file has been selected. Please select a file."
    Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFilepath, "")) Then

    DoCmd.TransferText acImportDelim, , "ADP_staging_tbl", Me.txtFilepath, False

    CurrentDb.Execute "UPDATE ADP_staging_tbl SET F46 = '" & _
    Right(txtFilepath, 26) & "' WHERE F46 IS NULL", dbFailOnError
    
    

   [B] CurrentDb.Execute strSQL, dbFailOnError[/B]
    
    

    CurrentDb.Execute "DELETE FROM ADP_staging_tbl"
    
    

    MsgBox "Import Successful!"
txtFilepath.Value = ""
    Exit Sub
    



End If

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:49
Joined
Sep 21, 2011
Messages
14,054
if I post that sql into the sql window it balks at the first / ???

Try building it in the SQL GUI
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 10:49
Joined
Oct 17, 2012
Messages
3,276
He just means the QBE grid - the grid-like display you get when you go to the Create tab and select Query Design from the ribbon.

Also, you'll need to place [brackets] around every field with a slash in the name.

For future reference, you should never use spaces or special characters in object names for that very reason - it makes working with them much more complicated.
 

carterlw12

Registered User.
Local time
Today, 09:49
Joined
Oct 2, 2018
Messages
25
It was ft/pt, reg/temp and union need brackets around them
 

June7

AWF VIP
Local time
Today, 06:49
Joined
Mar 9, 2014
Messages
5,425
Also, should not use reserved words as names for anything, as you discovered with [union].

Glad you got it working.
 

carterlw12

Registered User.
Local time
Today, 09:49
Joined
Oct 2, 2018
Messages
25
I did. I looked up reserved words, because I didn't know all of them, and changed all my field names in the tables and VBA code. All is good! Thanks.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:49
Joined
Feb 28, 2001
Messages
27,003
Offhand, it's not obvious, but here is what I would check. You have 46 Fnn fields in the SELECT section so verify that you have 46 fields in the INSERT INTO table (fields) list. The OTHER thing I noticed in that long query was this:

Code:
..., StatusEffDate, [COLOR="Red"]FT/PT[/COLOR], Reg/Temp, ...

You were warned about this earlier. Special characters in field names are really, REALLY, REALLY a bad idea. You must ALWAYS enclose such names in [] - but you COULD just remove the special characters instead.

In this context, ...FT/PT ... looks like FT divided by PT - and of course, Access and SQL have no clue as to the values of FT and PT.
 

carterlw12

Registered User.
Local time
Today, 09:49
Joined
Oct 2, 2018
Messages
25
Yes. I have actually changed the field names and characters instead of leaving them as reserved names and characters! Thank you!
 

Users who are viewing this thread

Top Bottom