Looks like I already am using it currently, just didnt know it was called Pass-through.
Here's how mine goes:
Public Sub dYomiKomi() '外データベースのデータを読み込んで、データベースに書き込む
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
'Dim tblNames(1 To 11) As String
Dim tblNames(1 To 12) As String '#02
Dim newName, InsStr, strModifier, connStr As String
Dim i, c, d As Integer
'////Fill up tables array////
tblNames(1) = "TKYOKUMD01"
tblNames(2) = "TTOKUKMD01"
tblNames(3) = "TTOKUIMD01"
tblNames(4) = "TSIHARMD01"
tblNames(5) = "TTORIKMD01"
tblNames(6) = "TSERMEMD01"
tblNames(7) = "TKANKAMD01"
tblNames(8) = "TSWKMSTD01"
tblNames(9) = "TUKKMKMD01"
tblNames(10) = "TCALENMD01"
tblNames(11) = "TSEKYUMD01"
'tblNames(12) = "TKANZAND01" '#02
On Error GoTo dame:
' For i = 1 To 11 #02
For i = 1 To 11
If doChecker(tblNames(i)) Then
Set cmd = Nothing
Set rst = Nothing
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
whoseError = ""
whoseError = "ACC"
conn.Open restorConStr
whoseError = "FOR"
DoCmd.RunSQL ("DELETE * FROM " & tblNames(i))
cmd.ActiveConnection = conn
cmd.CommandType = adCmdTable
cmd.CommandText = tblNames(i)
Set rst = cmd.Execute
If rst.RecordCount <> 0 Then
Do While Not rst.EOF
InsStr = ""
c = rst.Fields.Count
For d = 0 To c - 1
'////Check if Numeric or not////
If rst.Fields(d).Type = adNumeric Then
If Len(rst.Fields(d).Value) > 255 Then
strModifier = Mid(rst.Fields(d).Value, 1, 255)
Else
strModifier = rst.Fields(d).Value
End If
Else
If Len(rst.Fields(d).Value) > 255 Then
buff = Mid(rst.Fields(d).Value, 1, 255)
Else
buff = rst.Fields(d).Value
End If
If Left(buff, 1) = "'" Then
strModifier = "''" & buff & "'"
ElseIf Right(buff, 1) = "'" Then
strModifier = "'" & buff & "''"
Else
strModifier = "'" & buff & "'"
End If
End If
If d <> c - 1 Then
InsStr = InsStr & strModifier & ","
Else
InsStr = InsStr & strModifier
End If
Next d
' Debug.Print "INSERT INTO " & tblNames(i) & " VALUES (" & InsStr & ")" '### Debug ###
DoCmd.RunSQL ("INSERT INTO " & tblNames(i) & " VALUES (" & InsStr & ")")
rst.MoveNext
Loop
End If
End If
Next i
Set rst = Nothing
GoTo ok:
dame:
ErrHandler Err
Table_UnlockAll
Exit Sub
ok:
If msgYomiKomi = "All" Then
MsgBox "マスタ更新機能 読込処理" & vbCrLf & vbCrLf & " テーブル読み込み完了。", vbInformation, "読込処理"
Else
MsgBox "マスタ更新機能 読込処理" & vbCrLf & vbCrLf & " " & msgYomiKomi & "テーブル読み込み完了。", vbInformation, "読込処理"
End If
End Sub
What I do is I loop to each tables specified on an array, create an insert statement for each line of data, and append it to their respective access table clones.
Yes, I know its very very VERY slow. Extremely slow. Excruciatingly slow. It pains me seeing how slow it is honestly. Really.
Can I save the datas from my ADO recordset into a query, so that I can Still use the INSERT SELECT Statement?
Thanks
P.S. Sorry for the jumbled characters, those are my code comments.... Im Japanese... go figure
