By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,199 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,199 IT Pros & Developers. It's quick & easy.

Unable to read past teh first record in ADO recordset

P: 5
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub GetCSV_FileData()
  2.  
  3. Dim cnn As ADODB.Connection
  4. Dim cn As ADODB.Connection
  5. Dim rs As ADODB.Recordset
  6. Dim f As Integer
  7. Dim NumberOfRows As Integer
  8. Dim RowNumber As Integer
  9. Dim strSQL As String
  10. Dim InsertSQL As String
  11. Dim StrFolder As String
  12.  
  13. ' Database Columns'
  14.  
  15. Dim EmployeeNumberTemp As String
  16. Dim FullNameTemp As String
  17. Dim ElementNameTemp As String
  18. Dim TempAmount As String
  19. Dim Amount As Currency
  20. Dim PayDateTemp As Date
  21. Dim ResultType As String
  22. Dim PeriodNameTemp As String
  23. Dim CSVFileName As String
  24.  
  25. Set cn = New ADODB.Connection
  26. Set cnn = Application.CurrentProject.Connection
  27.  
  28. CSVFileName = "Test_Extract.csv"
  29.  
  30. 'On Error Resume Next
  31. strSQL = "SELECT * FROM " & CSVFileName
  32. StrFolder = "C:\Documents and Settings\chris\Desktop"
  33. cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
  34.     "Dbq=" & StrFolder & ";" & _
  35.     "Extensions=asc,csv,tab,txt;"
  36. 'On Error GoTo 0
  37. If cn.State <> adStateOpen Then Exit Sub
  38. Set rs = New ADODB.Recordset
  39. 'On Error Resume Next
  40. 'rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
  41. rs.Open strSQL, cn, adOpenDynamic, adLockReadOnly, adCmdText
  42.  '   On Error GoTo 0
  43. If rs.State <> adStateOpen Then
  44.     cn.Close
  45.     Set cn = Nothing
  46.     Exit Sub
  47.  End If
  48.     Do Until rs.EOF
  49.        ' the field headings
  50.        For f = 0 To rs.Fields.Count - 1
  51.           Select Case f
  52.              Case 0
  53.                PayDateTemp = rs.Fields(f).Name
  54.              Case 1
  55.                FullNameTemp = rs.Fields(f).Name
  56.              Case 2
  57.                EmployeeNumberTemp = rs.Fields(f).Name
  58.              Case 3
  59.                  ElementNameTemp = rs.Fields(f).Name
  60.              Case 4
  61.                 TempAmount = rs.Fields(f).Name
  62.                 Amount = Replace(TempAmount, "#", ".")
  63.              Case 5
  64.                ResultType = rs.Fields(f).Name
  65.              Case 6
  66.               PeriodNameTemp = rs.Fields(f).Name
  67.              Case 7
  68.            End Select
  69.        Next f
  70.  
  71. '  A Lot of logic will go here to do validation
  72.  
  73.  
  74. '  If the record is valid Insert it then go on to the next.
  75.  
  76.        InsertSQL = "INSERT INTO PAY_RUN_RESULTS (EMPLOYEE_NUMBER, EMPLOYEE_FULL_NAME,AMOUNT,RESULT_TYPE, PAY_DATE)  VALUES ('" & EmployeeNumberTemp & "', '" & FullNameTemp & "','" & Amount & "', '" & ResultType & "','" & PayDateTemp & "' );"
  77.        cnn.Execute (InsertSQL)
  78.        rs.MoveNext
  79.        PayDateTemp = 0
  80.        FullNameTemp = ""
  81.        EmployeeNumberTemp = ""
  82.        ElementNameTemp = ""
  83.        TempAmount = ""
  84.        Amount = 0
  85.        ResultType = ""
  86.        PeriodNameTemp = ""
  87.     Loop
  88.     rs.Close
  89.     Set rs = Nothing
  90.     cn.Close
  91.     Set cn = Nothing
  92.     Set cnn = Nothing
  93. 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.
Mar 13 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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
Expand|Select|Wrap|Line Numbers
  1. rs.Fields(f).Name
instead of the contents of the fields
Expand|Select|Wrap|Line Numbers
  1. rs.Fields(f).Value
-Stewart
Mar 13 '08 #2

P: 5
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.
Apr 11 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.