VBA Import Code Problem

[jhrBanker]

I've been struggling for days and can't seem to get my code to work properly. I have an Access2007 db into which I want to import monthly my Vonage activity using a form with a button. For some reason, no records are imported when the button is clicked. Below is my vba code, table layout, and sample records from the Vonage activity csv input file.
Any help will be greatly appreciated.

My Code:


Private Sub cmdVonageStmt_Click()
DoCmd.Hourglass True
CurrentDb.Execute "DELETE * FROM VonageStmt;"
lblVonageStmt.Visible = False
txtVonageStmt = ""
Dim rs1 As DAO.Recordset
Dim FileName As String
Dim FileDate As Date
Dim FileNum As Integer
Dim strFilter As String
Dim strFileDesc As String
Dim strFileExt As String
Dim strRecord As String
Dim strRecordArray() As String
Dim intCount As Integer
Dim InputString

FileName = ahtCommonFileOpenSave( _
InitialDir:="C:\Users\jhrAcer\Downloads\Vonage", _
Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:="Select File for Import... *.csv", _
Flags:=ahtOFN_HIDEREADONLY)
FileDate = FileDateTime(FileName)
FileNum = FreeFile()

Set rs1 = CurrentDb.OpenRecordset("VonageStmt")



DoCmd.RepaintObject acForm, "FrmMainMenu"
DoCmd.Hourglass True

Open FileName For Input As #FileNum
Line Input #FileNum, InputString
strRecordArray = Split(InputString, ",", 4)
On Error Resume Next
Do While Not (EOF(FileNum))
If strRecordArray(1) <> "null" Then
rs1.AddNew
rs1.Fields("DtTm") = strRecordArray(0)
rs1.Fields("Type") = strRecordArray(1)
rs1.Fields("Number") = strRecordArray(2)
rs1.Fields("Length") = strRecordArray(3)
RecCnt = RecCnt + 1
rs1.Update
End If
Loop
rs1.Close
Close #FileNum
DoCmd.Hourglass False
lblVonageStmt.Visible = True
txtVonageStmt = RecCnt
End Sub


===========================
VonageStmt Table
DtTm Text 255
Type Text 255
Number Text 255
Length Text 255
===========================
YY-MMDD.csv Downloaded File
Date/Time,null,Number,Length,Cost
12/20/2012 12:11 PM,Forwarded,BANK OF AMERICA (949) 994-7496,00:32,--
12/20/2012 09:03 AM,Forwarded,BANK OF AMERICA (949) 994-7496,00:03,--
12/14/2012 02:34 PM,Outgoing,(781) 975-7822,01:43,--
12/14/2012 11:10 AM,Incoming,TOLL FREE CALL (866) 294-1503,00:09,--
12/14/2012 10:15 AM,Outgoing,(708) 695-6619,00:14,--
12/12/2012 11:41 AM,Incoming,TOLL FREE CALL (866) 854-3732,00:04,--

[pbaldy]

I would set a breakpoint and step through the code so you can watch what it's doing. If needed:

http://www.baldyweb.com/Debugging.htm

[jhrBanker]

I have no idea how to do that. I've never used the Immediate window to run code.

[pbaldy]

I included the link in case you hadn't done it before.

[jhrBanker]

Results:
Compile error: Invalid in Immediate pane.

[pbaldy]

What are you running in the Immediate window? The code stays where it is; you set a breakpoint and then run it in the usual way (click the button in your case).

[jhrBanker]

All it's seems to be doing is stopping the code.
My problem is the code runs, but it's not stepping through the records. If I remove the line "If strRecordArray(1) <> "null" Then", only the header record is imported. With the line of code, no records are imported. What would prevent the program from stepping through and importing each record in the file?

[pbaldy]

The breakpoint stops the code, then you use F8 to step through line by line. I've never used that method to step through a text file, but the debugging method will show you what the code is doing.

[jhrBanker]

What method would you use to step through the records to import a csv file?

[jhrBanker]

When I press F8, the code skips from "Do While Not (EOF(FileNum))" to "rs1.Close". It appears that there's a problem with the EOF indicator.

Do While Not (EOF(FileNum))
If strArray(1) <> "null" Then
rs1.AddNew
rs1.Fields("DtTm") = strArray(0)
rs1.Fields("Type") = strArray(1)
rs1.Fields("Number") = strArray(2)
rs1.Fields("Length") = strArray(3)
intRecCnt = intRecCnt + 1


rs1.Update
End If
Loop
rs1.Close

[pbaldy]

I run a number of automated imports of different types. I either import it into a local table or link to it. Then I either run an append query or open a recordset on it and step through that. Depends on the specifics of the situation.

[pbaldy]

Sorry, I got distracted while typing and didn't see your last. I haven't used that method so I can't say why it's jumping over it. Does anything here help?

http://stackoverflow.com/questions/1...by-line-in-vba

[jhrBanker]

Same result . . . no records imported, but no error.

Open FileName For Input As #FileNum
Line Input #FileNum, InputString
strArray = Split(InputString, ",")
' On Error Resume Next
While Not EOF(FileNum)
If strArray(1) <> "null" Then
rs1.AddNew
rs1.Fields("DtTm") = strArray(0)
rs1.Fields("Type") = strArray(1)
rs1.Fields("Number") = strArray(2)
rs1.Fields("Length") = strArray(3)
intRecCnt = intRecCnt + 1
rs1.Update
End If
Wend
rs1.Close

[pbaldy]

Sorry, since I haven't used that method I'm not sure how to fix it. Can you provide a sample of the file and perhaps I can see another way to import it?

[June7]

Unless it is the actual literal string "null" that could be in the array element, I doubt Null should be within quote marks. Regardless, cannot compare something to Null anyway. http://allenbrowne.com/casu-12.html

Maybe:

If Not IsNull(strArray(1)) Then

Or maybe:

If strArray(1) <> "" Then