Unable to read past teh first record in ADO recordset

[vegeta456]

I have a really strange problem. I am using Microsoft Access 2003 under Windows XP Professional.

I am trying to read a CSV file using VBA in Access using the following procedure.

Public Sub GetCSV_FileData()
 
Dim cnn As ADODB.Connection
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim f As Integer
Dim NumberOfRows As Integer
Dim RowNumber As Integer
Dim strSQL As String
Dim InsertSQL As String
Dim StrFolder As String
 
' Database Columns'
 
Dim EmployeeNumberTemp As String
Dim FullNameTemp As String
Dim ElementNameTemp As String
Dim TempAmount As String
Dim Amount As Currency
Dim PayDateTemp As Date
Dim ResultType As String
Dim PeriodNameTemp As String
Dim CSVFileName As String
 
Set cn = New ADODB.Connection
Set cnn = Application.CurrentProject.Connection
 
CSVFileName = "Test_Extract.csv"
 
'On Error Resume Next
strSQL = "SELECT * FROM " & CSVFileName
StrFolder = "C:\Documents and Settings\chris\Desktop"
cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Dbq=" & StrFolder & ";" & _
    "Extensions=asc,csv,tab,txt;"
'On Error GoTo 0
If cn.State <> adStateOpen Then Exit Sub
Set rs = New ADODB.Recordset
'On Error Resume Next
'rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
rs.Open strSQL, cn, adOpenDynamic, adLockReadOnly, adCmdText
 '   On Error GoTo 0
If rs.State <> adStateOpen Then
    cn.Close
    Set cn = Nothing
    Exit Sub
 End If
    Do Until rs.EOF
       ' the field headings
       For f = 0 To rs.Fields.Count - 1
          Select Case f
             Case 0
               PayDateTemp = rs.Fields(f).Name
             Case 1
               FullNameTemp = rs.Fields(f).Name
             Case 2
               EmployeeNumberTemp = rs.Fields(f).Name
             Case 3
                 ElementNameTemp = rs.Fields(f).Name
             Case 4
                TempAmount = rs.Fields(f).Name
                Amount = Replace(TempAmount, "#", ".")
             Case 5
               ResultType = rs.Fields(f).Name
             Case 6
              PeriodNameTemp = rs.Fields(f).Name
             Case 7
           End Select
       Next f
 
'  A Lot of logic will go here to do validation
 
 
'  If the record is valid Insert it then go on to the next.
 
       InsertSQL = "INSERT INTO PAY_RUN_RESULTS (EMPLOYEE_NUMBER, EMPLOYEE_FULL_NAME,AMOUNT,RESULT_TYPE, PAY_DATE)  VALUES ('" & EmployeeNumberTemp & "', '" & FullNameTemp & "','" & Amount & "', '" & ResultType & "','" & PayDateTemp & "' );"
       cnn.Execute (InsertSQL)
       rs.MoveNext
       PayDateTemp = 0
       FullNameTemp = ""
       EmployeeNumberTemp = ""
       ElementNameTemp = ""
       TempAmount = ""
       Amount = 0
       ResultType = ""
       PeriodNameTemp = ""
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    Set cnn = Nothing
End Sub

The test datafile has 16 rows. When the procedure completes, I have in the database table 16 rows (as expected) however the data the rows contain are from the first line of the file. can anyone suggest what I'm doing wrong.

Thanks in advance.

[Stewart Ross]

Hi. Kinda tricky to read your code without code tags on, but what strikes me is that in your case selectors you are only referring to the field names

rs.Fields(f).Name

instead of the contents of the fields

rs.Fields(f).Value

-Stewart

[vegeta456]

Sorry about the delay in getting back, I was ill - pneumonia. That worked thanks very much obvious once you know. Problem for me being a newie to Access is that I asked someone how to do, did who probably did not know.