Hi all,

I have developed the undermentioned code to insert values into an Access table. The function RtnQryStr just creates the insert string using the values in an array called UniqueArray(i) after splitting them as shown.

My issue is I have a file with about 200000+ records. And it takes ages for it to execute every value one by one in the QryString.

Is there any way to insert all these values in one go?
If there is a way, would QryString be able to hold that large chunk of data strings to insert?

        
For i = LBound(UniqueArray) To UBound(UniqueArray)
            SplitterVal = Split(UniqueArray(i), DelimitChar)
            QryString = RtnQryStr(dbName, "IN", dbTableName, TableDataTypes, SplitterVal, MustField, MustVal, 
            MustValPos)
            AppAccess.CurrentDb.Execute QryString
            Set SplitterVal = Nothing
            UploadData = UploadData + 1
        Next i
Public Function RtnQryStr(dbName As String, RunCommand As String, dbTableName, _
    TableDataTypes As Variant, SplitValues As Variant, MustField1 As Variant, MustVal1 As Variant, MustVal1Pos
    As Integer, _
    Optional MustVal2 As Variant, Optional MustVal2Pos As Integer) As String

Dim i As Long, j As Long, k As Long

Select Case RunCommand
    Case "IN"
        j = 1
        k = 0
        RtnQryStr = "INSERT INTO " & dbTableName & " VALUES ("
        For i = 1 To UBound(SplitValues)
            If MustVal1Pos <> 0 Then
            If j = MustVal1Pos Then
                RtnQryStr = RtnQryStr & "#" & MustVal1 & "#,"
                k = k + 1
            End If
            End If
            Select Case TableDataTypes(k)
                Case 4
                    RtnQryStr = RtnQryStr & "" & SplitValues(i) & ","
                Case 10
                    RtnQryStr = RtnQryStr & "'" & Replace(SplitValues(i), "'", "") & "',"
                Case 8
                    RtnQryStr = RtnQryStr & "#" & Replace(SplitValues(i), ".", "/") & "#,"
                Case 7
                    RtnQryStr = RtnQryStr & "" & SplitValues(i) & ","
            End Select
            j = j + 1
            k = k + 1
        Next i
        If MustVal1Pos <> 0 Then
        If j = MustVal1Pos Then
            RtnQryStr = RtnQryStr & "#" & MustVal1 & "#,"
        End If
        End If
        RtnQryStr = Left(RtnQryStr, Len(RtnQryStr) - 1)
        RtnQryStr = RtnQryStr & ")"
    Case "DE"
        RtnQryStr = "DELETE * FROM " & dbTableName _
                & " WHERE " & MustField1 & " = #" & MustVal1 & "#"
End Select
End Function