Data Doesn't Import with VBA

[Moxioron]

Hello again.

I am stumped. My code is below and it is just importing a text file. When I select 'Run Sub', the screen blinks, but nothing happens. No error messages, no data import...nada.

I made sure that my table column names matched and I made sure that the data width and location was accurate.

Where am I going wrong? Thanks for your help.\

Option Compare Database
Option Explicit
Public Const cDailyActivityReport As String = "T:\Visa\Visa Month End\Daily Activity Data\CD021Data.txt"
Public Const cTITLE2 As String = "Daily Activity Report"
Public Function ImportEFile()
Dim myCheck
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strInFile As String, intInFile As Integer, strFileData As String
Dim datReport As Date
Dim strDate As Date
Dim strCardnumber As String
Dim strTranCode As String
Dim strAmount As String
Dim strReferenceNumber As String
Dim strTransactionDate As String
Dim strFT As String
On Error GoTo ImportFile_Err
strInFile = cDailyActivityReport
DoEvents
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_CD 021 Data", dbOpenDynaset)
intInFile = FreeFile
Open strInFile For Input As intInFile
SysCmd acSysCmdInitMeter, "Importing Data File", LOF(intInFile)
Do Until EOF(intInFile)
SysCmd acSysCmdUpdateMeter, (Loc(intInFile) * 128)
Line Input #intInFile, strFileData

If StrComp(Mid(strFileData, 55, 8), "MONETARY", vbBinaryCompare) = 0 Then
datReport = Mid(strFileData, 100, 8)
strDate = datReport
End If
If StrComp(Mid(strFileData, 8, 4), "XXXX") = 0 Then 'Or StrComp(Mid(strFileData, 16, 3), " B ") = 0 Then
strCardnumber = Mid(strFileData, 8, 16)
strTranCode = Mid(strFileData, 29, 3)
strAmount = Mid(strFileData, 35, 15)
strReferenceNumber = Mid(strFileData, 52, 17)
strTransactionDate = Mid(strFileData, 71, 6)
strFT = Mid(strFileData, 79, 2)
End If
With rst
.AddNew
!Date = strDate
!CardNumber = strCardnumber
!TranCode = strTranCode
!Amount = strAmount
!ReferenceNumber = strReferenceNumber
!TransactionDate = strTransactionDate
!FT = strFT
.Update
End With
' End If
rst.Close
dbs.Close
ImportFile_Exit:
SysCmd acSysCmdClearStatus
Close intInFile
Set dbs = Nothing
Set rst = Nothing
'Set cd = Nothing
Exit Function
ImportFile_Err:
'MsgBox Err & " " & Err.Description & vbLf & "Job Name: ImportFile" & vbLf & cSUPPORT, vbCritical
Resume ImportFile_Exit
Loop
End Function

 

[boblarson]

You do have the message box commented out for the error handler, so I'm guessing that something happened and since there was no message box code to display the problem it just bypasses it to the ImportFile_Exit part.

Also, why do you have Loop in the bottom of it near the End Function? That shouldn't be there. If it goes with the Do Until EOF(intInFile) line then it should be above the ImportFile_Exit label and above the rst.Close part.

 

[Moxioron]

Okay, I updated it and I am getting a 13 Type Mismatch Job Name:ImportFile 16 message. I googled that error message and I think it has something to do with the resources.

In the past I have selected Microsoft DAO 3.6 Object Library as a reference, because it is DAO, but when I when I select it, I get a 'Name conflicts with existing module, project, or object library'.

Any idea? Thanks for your help.