VBA Problem with Transfer text, Help anyone? (1 Viewer)

[Robert88]

I have been trying to import a txt file into a table.

Doing it manually with import, using the Door Fob import specification and importing it into tblDoorFob table it is successful.

I have written VBA for a button on a form frmReadDoorFob.

Private Sub CmdReadFile_Click()

DoCmd.TransferText acImportDelim, "Door Fob", "tblDoorFob", "C:\Door_Fob\AutoArch(20040103-20040107).txt", False

' A message box indicating the process has completed.
MsgBox ("The file has completed its transfer")

End Sub

But when I use this VBA there is an error, it seems there is something wrong with the first field "fldDate". It reports in a table "AutoArch(20040103-20040107)_ImportErrors". In addition the tblDoorFob is filled with blank records.

I have attached in a zip the mdb and a sample of the txt file, which resided in a folder c:\Door_Fob.

I am unsure why this is not importing using VBA as it works when I manually import the data as seen in the tblDoorFob attached in the mdb file.

If anyone could help, I would appreciate it.

It is probably something simple I have over looked? I hope, as I have been banging my head against the wall with this one.

Robert88

 

[modest]

change acImportDelim to acImportFixed

Private Sub CmdReadFile_Click()
    Dim strPath As String
    Dim strSpec As String
    Dim strTbl As String
    
    strTbl = "tblDoorFob"
    strSpec = "Door Fob"
    strPath = CurrentProject.Path & "\AutoArch(20040103-20040107).txt"

    DoCmd.TransferText acImportFixed, strSpec, strTbl, strPath, False
    
    ' A message box indicating the process has completed.
    'MsgBox ("The file has completed its transfer")
    
End Sub

Instead of creating a new directory, I kept the file with the database (hence the CurrentProject.Path) so that it would work. As you can see, I also made variables for all the parameters.

 

[Robert88]

Thanks for your help.

Hi modest,

Thanks for that.

That worked a treat.....

Just added more code for each file, I am sure there is an easier way rather than write down each file as I have approx 200 per year?

Just read in 213 files which in total contained 20,277 records. Considering it took me all day yeaterday to do all of 2003 data, manually, today with code it only took, 3 hours, cool.

Will have to work out how to do it with multiple files as I am reading approx 200 files for each year but when I get all the data in for 2003, 2004 & 2005 it will be a matter in the future to read about 5 files per week. But I will have to investigate this further down the line as currently just want to get some reports out of all the data.

I appreciate the support.

Robert88