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

[carterlw12]

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:

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]

Would be helpful to provide sample file. Attach file to post.

 

[carterlw12]

CSV files can't be uploaded but here is a super basic version in excel format though.

 

[June7]

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]

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]

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]

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]

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"

 

[carterlw12]

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]

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.

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]

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.

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]

if I post that sql into the sql window it balks at the first / ???

Try building it in the SQL GUI

 

[carterlw12]

Sorry, I don't know how to do that. lol

 

[Frothingslosh]

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.

 

[Gasman]

Sorry, I don't know how to do that. lol

How to with Words


How to with a Video

 

[carterlw12]

It was ft/pt, reg/temp and union need brackets around them

 

[June7]

Also, should not use reserved words as names for anything, as you discovered with [union].

Glad you got it working.

 

[carterlw12]

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]

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:

..., 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]

Yes. I have actually changed the field names and characters instead of leaving them as reserved names and characters! Thank you!