Re: import text file to access table using vb

[gracehskuo]

leinad31:
thanks,the reinterpret text to csv file code is done. below is the import csv to the access database table.have error appear regarding the path of CSV.txt location is invalid.i been check the location i save the csv file is at d:\Attend\csv.txt . please help have a look for the strsql statement not sure i done the correct way.

Private Sub CmdImport_Click()
Dim conDataConnection As Connection
Dim strsql As String
Dim Split_Data
Dim Raw_Data As String

'Create adatabase connection
  Set conDataConnection = New Connection

  conDataConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & _
                         "\Attendance.mdb;Persist Security Info=False"
  
    'Open the source text file
    Open App.Path & "\CSV.txt" For Input As #1
        While Not EOF(1)
            Line Input #1, Raw_Data
            Split_Data = Split(Raw_Data, Chr(44), -1, vbTextCompare)
            strsql = "INSERT INTO TempA (field_StaffNo, field_StaffName, field_WorkTotalHours) SELECT StaffNo, StaffName, TotalHrs FROM mysourcetable IN 'D:\Attend\CVS.txt'"
            conDataConnection.Execute strsql
            DoEvents
        Wend
    Close
conDataConnection.Close
Set conDataConnection = Nothing

End Sub

thanks for help!