Text import works manually but not with VBA script

[HelloWorldGuy]

I am beyond confused. When I do a manual import of this data into a new table I have no serious issues. Only when I use transfer text I get numerous erros and a mostly blank table. I am using the saved import specification of the manual import. Also, I am clearing out the table with VBA to do a clean import.

Private Sub cmdImportData_Click()

Dim MyFile, MyDir, MoveCmd
Dim FileCount As Integer

CurrentProject.Connection.Execute "delete * from tblSN137"

MyFile = "C:\Users\Import.txt"
FileCount = 0
If MyFile = "" Then
MsgBox ("No Files Found")
Else

FileCount = FileCount + 1
MsgBox ("Your file is being imported...")
DoCmd.TransferText acImportFixed, "MyImportSpecification", "tblMyTable", MyFile, False, ""
DoCmd.SetWarnings False
End If
MsgBox ("The SN137 Table has been updated")

DoCmd.Close acTable, "tblSN137"
DoCmd.SetWarnings True
End Sub

 

[HelloWorldGuy]

and to be clear, my table IS called tblSN137 and my tDoCmd.TransferText acImportFixed does reflect the correct names of tables/import specifications.I was just going to post general code and neglected to change those "tblSN137"s

 

[MazeWorX]

couple of things ...

MyFile = "C:\Users\Import.txt"

If MyFile = "" Then
MsgBox ("No Files Found")
Else

will always be false as you have assigned the value in the lines above. also myfile has no data type declaration so by default it will be a variant and you are checking for the existence of a empty string

Having said this lets get to the import Spec file... when you created it, was it importing to a "New table"? Import it again manually and you reach the last option before importing re save the import spec. I figured in you code you where checking to see if the file existed but as stated above your code would always evaluate to false so i have provided a simple function to check the files existence

I have rewritten the code below it should as long as your import spec file is resaved work fine.

Private Sub cmdImportData_Click()

    Dim strMyFile As String
    Dim intFileCount As Integer
    Dim intFile As Integer
    
    DoCmd.SetWarnings False
    CurrentProject.Connection.Execute "delete * from tblSN137"
    MyFile = "C:\Users\Import.txt"
    
    intFile = fIsFileDIR(strMyFile)
    
    If intFile = 0 Then
        MsgBox ("No Files Found")
    Else
        MsgBox ("Your file is being imported...")
        DoCmd.TransferText acImportFixed, "MyImportSpecification", "tblSN137", MyFile
        MsgBox ("The SN137 Table has been updated")
    End If
    DoCmd.SetWarnings True
End Sub

'Fully qualify stPath
'To check for a file
'   ?fIsFileDIR("c:\winnt\win.ini")
'To check for a Dir
'   ?fIsFileDir("c:\msoffice",vbdirectory)
'returns -1 true 0 for false

Function fIsFileDIR(stPath As String, Optional lngType As Long) As Integer
    On Error Resume Next
  fIsFileDIR = Len(Dir(stPath, lngType)) > 0
End Function

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>