Importing Text Files Agony HELP!!!!

[Guest]

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 ([email protected])
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

 

[John Nurick]

Hi Jez,

It's a bit much to expect people to analyse your code when you don't
even say where it's going wrong. The first thing to do is to step
through the code and check that each statement produces the results you
expect. If it doesn't, check the values of all the relevant variables
(or object properties).

 

[Guest]

Hi,

Apologies, I forgot to point out where my problem is.

This section below works when I just delete the data from my table, but says
that has imported new data, when infact no data has been run. It doesnt seem
to get the data and import.

While Not rs.EOF
'sqlRemoveCrap = "DELETE * FROM " & rs("TempTable") & _
" WHERE ISNUMERIC(" & rs("TempTable") & "." & rs("KeyField")
& ") = False"
sqlDelete = "DELETE * FROM " & rs("Table")
'sqlDelete = "DELETE * FROM " & rs("Table") & _
" WHERE " & rs("Table")
sqlInsert = "INSERT INTO " & 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("Table"),
rs("Spec")
With cnn
'.Execute sqlRemoveCrap
.Execute sqlDelete
.Execute sqlInsert
'.Execute "DROP TABLE " & rs("TempTable")
End With
rs.MoveNext
Wend

Its very strange as unsure where its going wrong.

 

[John Nurick]

When you step through the code, what is the value of sqlInsert at the
point where you call
cnn.Execute sqlInsert
?

At this point, create a new query and switch it to SQL view. Copy the
"SELECT ..." clause of sqlInsert and paste it into the query window.
When you preview the query, does it return the records you expect?