600 MB text files to Access - how to do it quickly?

[Jaymond Flurrie]

So the idea is that I have now split up my 6GB flatfile into ten smaller ones, they're just text files with one ID number, four 1-10 numbers and 2400 0/1s separated by tabs there. As an example data (using only four of those 2400):

ID One Two Three A B C D
1 4 5 6 0 1 0 1
12 5 6 7 1 1 0 1

I would need to get these to Access as quickly as possible and I already have VBA code to create tables with Integer-Integer-Integer-Integer-BIT-BIT-BIT-BIT fields, but what's the smartest way to get the data there?

Let's assume one of my 600MB files is in C:\fromhere1.txt

Also, would it be faster/better if I would use ACCDB instead of MDB as my databases for this kind of data?

 

[xenou]

I've no idea really. But there's got to be less overhead since there's no execution of other commands intervening (FOR, IF, &, =).

 

[Jaymond Flurrie]

I've no idea really. But there's got to be less overhead since there's no execution of other commands intervening (FOR, IF, &, =).

Well, yours takes 15.3 seconds mine takes 36.1 seconds with 10 000 rows, so yours seems to be better - at least with 2500 columns.

 

[Jaymond Flurrie]

Here's the check-function I quickly built to check whether the row should be added to the sum or not.

Function bCheckCriteria(vTxtRow As Variant, vCriteria As Variant) As Boolean
    Dim i As Integer, j As Integer
    Dim iOne As Integer
    Dim iTwo As Integer
    Dim iThree As Integer
    Dim iCriteria(0 To 2) As Integer
    Dim bCheck(0 To 2) As Boolean
    
    Dim bOne As Boolean
    Dim bTwo As Boolean
    Dim bThree As Boolean
    
    iCriteria(0) = vTxtRow(1)
    iCriteria(1) = vTxtRow(2)
    iCriteria(2) = vTxtRow(3)
    
    bCheck(0) = False
    bCheck(1) = False
    bCheck(2) = False
    
    For j = LBound(bCheck) To UBound(bCheck)
        For i = LBound(vCriteria, 2) To UBound(vCriteria, 2)
            If vCriteria(j, i) = iCriteria(j) Then
                bCheck(j) = True
                Exit For
            End If
        Next i
        
        If Not bCheck(j) Then
            bCheckCriteria = False
            Exit Function
        End If
    Next j

    bCheckCriteria = True

End Function

Slightly simplified version:

Function bCheckCriteria(vTxtRow As Variant, vCriteria As Variant) As Boolean
    Dim i As Integer
        
    For i = 1 To 3
        If vCriteria(i, vTxtRow(i)) = 0 Then
            bCheckCriteria = False
            Exit Function
        End If
    Next i
    
    bCheckCriteria = True
End Function