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
Bookmarks