Problem with access acImportDelim importing csv with headers

D

Daryl

Joined
Jun 20, 2017
Messages
1
Reaction score
0
Importing a csv file and it keeps creating copy import error tables

I though by changing the HasFieldNames:=True it would ignore the error

Private Sub cmdImport_Click()
On Error Resume Next:
Dim db As DAO.Database
Set db = CurrentDb
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Const strFolder As String = "\\LT014\Users\dgriffin\Desktop\import\"
createNewDirectory (strFolder & "processed")
Const strPattern As String = "*.csv"
Dim strFile As String
strFile = Dir(strFolder & strPattern, vbNormal)
Do While Len(strFile) > 0

db.Execute "delete from tblimport"

If strFile <> "" Then
DoCmd.TransferText TransferType:=acImportDelim, TableName:="tblImport", FileName:=strFolder & strFile, HasFieldNames:=False

fso.MoveFile strFolder & strFile, strFolder & "processed\"

End If

DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_Insert_New_Items"
DoCmd.OpenQuery "qry_Insert_New_items_detail"

DoCmd.SetWarnings True
strFile = Dir
Loop

db.Close
Set db = Nothing
Me.Requery
End Sub
 
Hi, DaryL
I found that there're many issues in your program.
First and you just mentioned about is sentence "HasFieldNames:=True" seems a Pascal sentence. It should not appear in a VBA program neither a VBS program.
Second, In this sentence "Const strFolder As String = "\\LT014\Users\dgriffin\Desktop\import\"", it seems that strFolder is a constant which contains a directory on Windows. But the question is, On windows people use '\' to pattern folders. On a Unix system people use '/' to pattern folders or directories. And the root directory on a Windows is the drive letter, usually 'C:\'. On a Unix/Linux, the root directory is '\\'. If you want to point to a remote location. That remote location is under a Linux machine. I have no Idea about whether createNewDirectory can work correctly.
Third, DoCmd seems to be an object without definition. And it's really hard to guess what is DoCmd. Even that there are more objects such as DoCmd. I don't know what the parent class is, so I don't know what is Me. neither what will Me.Requery do. I don't know the form in the project, so I don't understand cmdImport.
Finally my suggestion is: could you learn VBA first?
Here's an URL that you can get more information about VBA
https://msdn.microsoft.com/en-us/library/ee691831.aspx
I used to write VisualBasic 6.0, VisualBasic.Net and VBS programs.
But I'm really not familiar with VBA. It's really hard for me to correct your program.
 
Back
Top