I am driving myself crazy with this. this code below is supposed to delete
the data in my table and then load my text file into a temp table and then
transfer into my 'Live' table. The code goes through the motions and tells me
data is imported, but when i check it, there is no data there. Help me I am
going crazy with this. How can I get it to work.

This is my code. Any problems feel free to email me (jeremylisle@onetel.net)
and I can zip up the database and send you a copy to see for yourself my
issue.

Option Compare Database
Option Explicit

Dim N As Integer
Dim varMessage As Variant
Dim sqlImport As String
Dim sqlRemoveCrap As String
Dim sqlDelete As String
Dim sqlInsert
Dim strImportFilePathlookup As Variant
Dim strImportFilePath As String

Function ImportTextFile(strImportFilePath, strFileName, strTableName,
strImportSpec As String)
   
   On Error GoTo Err:
   DoCmd.TransferText acImportDelim, strImportSpec, strTableName, _
   strImportFilePath & strFileName, False, ""
   
   Exit Function

Err:
   varMessage = MsgBox(Error$, , cMessageTitle)

End Function

Sub Import()

   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim objForm As Form
   Set objForm = Forms!frmImport
   
   'On Error GoTo Err:
   
   'determine import file path
   strImportFilePathlookup = DLookup("FilePath", "tblFilePath", _
       "Action = 'Import' AND UserName = '" & fOSUserName & "'")
       If IsNull(strImportFilePathlookup) Then
           MsgBox "No Import Path defined for this User!"
           Exit Sub
       Else
           strImportFilePath = strImportFilePathlookup
       End If
       
   'import text files- import parameters stored in tblImport
   Set cnn = CurrentProject.Connection
   Set rs = New ADODB.Recordset
   
   sqlImport = "SELECT * FROM tblImport where tblImport.Import = true " & _
                       "ORDER BY tblImport.ImportNo"
   
   rs.Open sqlImport, cnn, adOpenKeyset, adLockOptimistic
   
   rs.MoveFirst
   
       While Not rs.EOF
           sqlRemoveCrap = "DELETE * FROM " & rs("TempTable") & _
               " WHERE ISNUMERIC(" & rs("TempTable") & "." & rs("KeyField")
& ") = False"
           sqlDelete = "DELETE * FROM " & rs("Table") & _
                   " WHERE " & rs("Table")
           sqlInsert = "INSERT INTO " & rs("Table") & _
               " SELECT " & rs("TempTable") & ".* FROM " & rs("TempTable")
& ";"
                               
           'Import New data from Text file
           objForm.txtInfo = "Importing " & rs("TextFile")
           ImportTextFile strImportFilePath, rs("TextFile"),
rs("TempTable"), rs("Spec")
               With cnn
                   .Execute sqlRemoveCrap
                   .Execute sqlDelete
                   .Execute sqlInsert
                   .Execute "DROP TABLE " & rs("TempTable")
               End With
           rs.MoveNext
       Wend
   
       'Delete import errors table
       Call basGeneralFunctions.DropTableErrors
   
   cnn.Close
   
   Set cnn = Nothing
   Set rs = Nothing
   
   MsgBox "Import Complete", vbOKOnly, cMessageTitle
   
   Exit Sub
   
Err:
   varMessage = MsgBox(Error$, , cMessageTitle)
   Resume Next

End Sub

Thanks,
Jez