Can Access/VBA codes be reading things wrongly?

[shawnnnnnnn]

Hi all,

I managed to write a code that will go through a table and remove any faulty data from it.

My system generates out a monthly text file containing all the events that have happened over the month. Each event is detailed within 6 lines of text, any more or any less is considered faulty. The start of each event is determined by "0~0~ES~1~".

Here's the weird part, I tested my program with a test file which consists of about a thousand lines of text, and it runs perfectly. However, when I used it on the actual file which consists of about half a million lines of text, it's always giving me back different data.

E.g. my first test on the actual file generated 92123 records, whereas my second test generated 91800.

I did not change my codes at all, so could this be a bug?

I really have no idea how to rectify this as I see nothing wrong with my codes or data.

Sub Del_Invalid(strSearch As String)
 
    Dim db As Database, rst As Recordset, lngcnt As Long, k As Long
 
    On Error GoTo Del_Invalid_Err
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Oct", dbOpenTable)
 
    lngcnt = 0
    With rst
 
        Do While Not .EOF
            Debug.Print !Field1
 
            If InStr(1, !Field1, "0~0~ES~1~") > 0 Then
                If lngcnt > 6 Then
                    'Delete offending records
                    .MovePrevious
                    For k = 1 To lngcnt
                        .Delete
                        .MovePrevious
                    Next
                    lngcnt = 0
                Else
                    lngcnt = 1
                End If
            Else
                lngcnt = lngcnt + 1
            End If
            .MoveNext
        Loop
        ' In case last one was offending
        If lngcnt > 6 Then
            'Delete offending records
            .MovePrevious
            For k = 1 To lngcnt
                .Delete
                .MovePrevious
            Next
        End If
 
    End With
 
Del_Invalid_Exit:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
 
Del_Invalid_Err:
    MsgBox "Error " & Str$(Err.Number) & " = " & Err.Description
    Resume Del_Invalid_Exit
 
End Sub

Private Sub Command0_Click()
    Call Del_Invalid("0~0~ES~1~")
End Sub

[jforbes]

I doubt you are running into a bug. I assume you have exclusive access to the table that you are modifying, if not maybe there is a record lock issue.

One thing that concerns me is the .MovePrevious method. Moving through a Recordset and deleting records is tricky enough without moving in both directions. You could be running into a timing issue with your reads and writes. Given this, you might want to try to lockdown your Recordset when opening it. I haven't tested this out, but you might want to do something like:

Set rst = db.OpenRecordset("Oct", dbOpenTable,dbDenyRead,dbPessimistic)

A way you might be able to sidestep this is to create a query to perform your deletes and execute it. What you are doing is complex, but it doesn't seem too complex for a query.

[shawnnnnnnn]

Hi jforbes,

I just did a thorough check. My codes are working fine, but I'll try your suggestion to be safe :)

Anyway, I think the problem lies with Access's import function. I imported my data using the external data tab and with the same settings, but when I search for, example record number "523111", both tables will show a different value in the field. I feel that my 500000+ line text file may be too much for access to handle properly. So I'm gonna try splitting it and importing again.

[Rabbit]

When you import data, there's no way to guarantee the order in the tables unless you write a script to import it line by line.

[shawnnnnnnn]

Hi rabbit,

Would you by any chance know how to or can provide me an example of the code to import line by line?

[Rabbit]

An example wouldn't be much help because the bulk of it would change depending on the file layout. But the basic steps are:

1) Read a file line by line
2) Break out the line into the fields you need (this is where the bulk of the code would be and also where the changes are depending on the layout)
3) Insert that row of data into the database table with an autonumber field.

Steps 1 and 3 are fairly standard. Number 2 is where there are lots of changes depending on your file layout.

[shawnnnnnnn]

Hey rabbit,

I've been reading on this recordset method in http://bytes.com/topic/access/answer...into-ms-access

However, I'm still unable to figure out how to modify the code to work with my data due to my poor VBA knowledge. I'm really bad with this "Mid$" stuff.

Basically, I just want each line in my text file to occupy one field in my table first. I want the entire line (each line has a different number of characters) to be placed into the field, so I don't think I really need to do the Mid$ thingy right?

E.g.
Line 1 is placed in Row 1 Column 1
Line 2 is placed in Row 2 Column 1
Line 3 is placed in Row 3 Column 1
.
.
.
Line X is placed in Row X Column 1

[Rabbit]

Yes, you're right. If you only have one field, then you don't need to mess with the Mid stuff.