MS Access next line does not move after specific record (1 Viewer)

[DevAccess]

Hi

I have below code where

Sub compareCSV(filename As String, tablename As String, deletetablename As String)

Dim rs As DAO.Recordset
Dim file_number As Integer
    Dim raw_line As String
    Dim csv_line As Variant
    Set DB = CurrentDb()
    file_number = FreeFile
  
    Open filename For Input As #file_number

Set rs = CurrentDb.OpenRecordset("SELECT * FROM " + tablename + "")

'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True
            RecordFound = False
            field_value = rs![Unique ID]
            Do While Not EOF(file_number)
                    Line Input #file_number, raw_line
                    csv_line = ProcessCsvData(raw_line)
                    field_index = 0
                    If UBound(csv_line) < field_index Then
                        ' Guard against index out of bounds
                    ElseIf csv_line(field_index) = field_value Then
                       RecordFound = True
                    End If
                Loop
        rs.MoveNext
        If RecordFound = False Then
   
        DB.Execute "INSERT INTO " + deletetablename + " SELECT * FROM " + tablename + " where [UNIQUE ID] ='" + field_value + "'"
        End If
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

after some records are being read in CSV file it goes to Do While Not EOF(file_number) but then jumps to rs.movenext instead of reading line of CSV.

What is missing here.

 

[MarkK]

There are two loops here right? One is the recordset, and the other is thru the file that you open. It looks like what may happen here (impossible to tell without debugging the code directly) is that your inner loop runs to the end of the file. Then, your next loop in the recordset arrives at...

            Do While Not EOF(file_number)

...and EOF is True, and it skips it. You've already run thru all the rows in the opened file.
Maybe you need to re-open the file or move the file pointer back to BOF???
hth
Mark

 

[DevAccess]

There are two loops here right? One is the recordset, and the other is thru the file that you open. It looks like what may happen here (impossible to tell without debugging the code directly) is that your inner loop runs to the end of the file. Then, your next loop in the recordset arrives at...

            Do While Not EOF(file_number)

...and EOF is True, and it skips it. You've already run thru all the rows in the opened file.
Maybe you need to re-open the file or move the file pointer back to BOF???
hth
Mark

You seems to be right, how do I achieve that problematically.

 

[MarkK]

Well, file access is cumbersome. I would most likely just read that file into a table first. Or maybe you can link to it, that would be best actually, if it is a structured file. So create a link to it, and then it shows up in your database like a table, and then you can query it directly without having to access it sequentially in code, line by line, as you are doing. Create the link consume the data, then delete the link. That would be easiest.
hth
Mark

 

[The_Doc_Man]

Let's analyze the code flow here.

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True    <<----------------------------------<      'top of loop 1
            RecordFound = False                                                 
            field_value = rs![Unique ID]                                         
            Do While Not EOF(file_number)   <<------------------<           'top of loop 2
                    Line Input #file_number, raw_line                    
                    csv_line = ProcessCsvData(raw_line)               
                    field_index = 0                                             
                    If UBound(csv_line) < field_index Then             
                        ' Guard against index out of bounds             
                    ElseIf csv_line(field_index) = field_value Then    
                       RecordFound = True                                   
                    End If                                                        
                Loop   >>---------------------------------------^            'end of loop 2                               
        rs.MoveNext                                                             
        If RecordFound = False Then                                       
   
        DB.Execute "INSERT INTO " + deletetablename + " SELECT * FROM " + tablename + " where [UNIQUE ID] ='" + field_value + "'"                                |
        End If                                                                     
    Loop       >>------------------------------------------------^     'end of Loop 1
Else
    MsgBox "There are no records in the recordset."
End If

IF I read this correctly, your logic says to read through the recordset looking for the next ID number to process. Then it says to read through the file and scan the records to determine whether that particular ID number exists in the file.

The implication is that if you ever find a record for which there is no such ID in the file, you will insert that record into the 'deletetable'.

Comments based on a closer look:

1. Field_Index will ALWAYS be 0. I see no purpose for it. You set it to zero but nothing in that code EVER sets it to anything else. Therefore, unless you omitted something, that IF statement inside the inner loop will never check anything other than element zero of whatever that variant holds.

2. If you follow the flow of loop 2, it would end when you scan through the whole file without finding that ID number. You would add the ID number to your 'deletetable' (I guess as a candidate for some kind of cleanup? Just guessing...) BUT if that ever happens, you are still inside of loop 1, which will step to the next record and continue the search. BUT at this point, you have hit <EOF> on 'filenumber' and loop 2 will ALWAYS see <EOF> - because you don't reset the file back to its beginning.

I'm thinking that somewhere in the same area that you perform that INSERT, perhaps right afterit, you have to also close and re-open the file because otherwise you will stay at <EOF> for the duration of loop 1. You can NEVER find another match (i.e. will always decide that the desired record is absent) after the first time you drop out of loop 2 with a "not found" condition.