Excel to Access VBA transalation (2 Viewers)

[shawnntjr]

Hi all,

My friend was able to provide me an idea on how to do my program, however, I think he's logic is for Excel VBA instead of Access. Can someone help me translate what he's trying to say into Access VBA?

Open file
Set arrayidx = 0
Initialize Array
While not EOF Do
    Read file
    If line contains 0~0~ES~
    {
        If arrayidx = 6
        {write array 1 to 6 into table}
        End If
    Initialize array
    Set arrayidx to 1
    }
    End If
    Move line to array
    Set arrayidx = arrayidx + 1
End While

 

[Galaxiom]

It certainly isn't VBA of any kind. Looks like it is just a logical outline rather than any programming language.

What are you actually trying to do?

 

[shawnntjr]

It certainly isn't VBA of any kind. Looks like it is just a logical outline rather than any programming language.

What are you actually trying to do?

Basically, our aim was to have access parse through a text file and pull out data from it. This text files contains many events that a system experience, so it has like literally half a million lines.

Each event that the system experiences is detailed within 6 lines, any more or any less is considered a faulty data. The start of a new event is determined by the string "0~0~ES~1~".

E.g.
0~0~ES~1~ (Event 1)
....
....
....
....
....
0~0~ES~1~ (Event 2)
....
....
....
....
....

I would like to display extract and display the data such that:
Line 1 is inserted into Row 1 Column 1
Line 2 is inserted into Row 1 Column 2
Line 3 is inserted into Row 1 Column 3
Line 4 is inserted into Row 1 Column 4
Line 5 is inserted into Row 1 Column 5
Line 6 is inserted into Row 1 Column 6
Line 7 is inserted into Row 2 Column 1
Line 8 is inserted into Row 2 Column 2
Line 9 is inserted into Row 2 Column 3
Line 10 is inserted into Row 2 Column 4
Line 11 is inserted into Row 2 Column 5
Line 12 is inserted into Row 2 Column 6

I need to insert only the normal events into my access table (those with ONLY 6 lines).

 

[JHB]

Isn't it the same question you posted here?
http://www.access-programmers.co.uk/forums/showthread.php?t=271022

What is the problem with the answers you got there?

 

[shawnntjr]

Isn't it the same question you posted here?
http://www.access-programmers.co.uk/forums/showthread.php?t=271022

What is the problem with the answers you got there?

Well, I tried the importing and parsing method, and that had the best results. The problem was when I used that method on a test file, which contains maybe a thousand rows, it worked great. But when I used it on my actual file, which contains literally half a million lines, it kept giving me different results.

E.g. First try: it generated 90198 rows of data. Second try: it generated 92123 rows of data.

I did not change my codes or data, so I don't know what is happening.

So since those methods failed, I thought of trying array. But I can't write the VBA codes alone due to my lack of knowledge. I know some of you might just think I'm just too lazy to write it myself, but I really am unable to do it. I have zero VBA background and I've been trying to learn it while deciphering the codes of others these few weeks, and I managed to figure out certain things. Just not enough for me to finish this project I guess.

 

[JHB]

..
E.g. First try: it generated 90198 rows of data. Second try: it generated 92123 rows of data.

I did not change my codes or data, so I don't know what is happening.

Did you run an unmatch query to compare the two to see where it went wrong?

 

[shawnntjr]

Did you run an unmatch query to compare the two to see where it went wrong?

All my fields are set to "memo" as each field contains more than 255 characters. Is there a way to do the unmatch query for memo type data?

 

[shawnntjr]

UPDATE:

I tested my code on the same set of data multiple times and the number of generated records are all different.

Definitely not the import problem, as the number of records matches the number of lines in the text file.

So I can conclude that the problem either lies within the parsing and removing of faulty data code, or my MS Access has a mind of its own.

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, "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("ES~1~")
End Sub

 

[Christos99]

Looks like a 3rd 'duplicate' from
http://www.access-programmers.co.uk/forums/showthread.php?t=271135

You have also PMd me. Might be a good idea to keep all in one place, rather than take the scatter gun approach !

 

[shawnntjr]

Looks like a 3rd 'duplicate' from
http://www.access-programmers.co.uk/forums/showthread.php?t=271135

You have also PMd me. Might be a good idea to keep all in one place, rather than take the scatter gun approach !

Really sorry about this, but my deadline is in 2 weeks and this is the final problem of my project, so I'm pretty desperate. I guess I'll just work with you through PM then! Sorry, once again!