08/02/21 02:30:27
'ADO参照追加
Private Sub hoge()
Dim cn As New ADODB.Connection
Dim rstKey As New ADODB.Recordset
Dim rstData As New ADODB.Recordset
Dim rstTrans As New ADODB.Recordset
Dim count As Integer
Dim flg As Boolean
Set cn = Application.CurrentProject.Connection
cn.Execute "DELETE FROM 変換後"
rstKey.Open "SELECT 納品先,商品名 FROM DATA GROUP BY 納品先,商品名", cn, adOpenForwardOnly, adLockReadOnly
rstTrans.Open "SELECT * FROM 変換後", cn, adOpenForwardOnly, adLockOptimistic
Do While Not (rstKey.EOF)
w = 0
flg = False
rstData.Open "SELECT * FROM DATA WHERE 納品先='" + rstKey.Fields(0) _
+ "' AND 商品名='" + rstKey.Fields(1) + "'", cn, adOpenForwardOnly, adLockReadOnly
Do While Not (rstData.EOF)
If w = 0 Then
flg = True
rstTrans.AddNew
rstTrans.Fields(0) = rstData.Fields(0)
rstTrans.Fields(1) = rstData.Fields(1)
End If
rstTrans.Fields(w * 2 + 2) = rstData.Fields(2)
rstTrans.Fields(w * 2 + 3) = rstData.Fields(3)
If w < 3 Then
w = w + 1
Else
rstTrans.Update
flg = False
w = 0
End If
rstData.MoveNext
Loop
rstData.Close
If flg Then
rstTrans.Update
End If
rstKey.MoveNext
Loop
rstKey.Close
rstTrans.Close
Set rstKey = Nothing
Set rstData = Nothing
Set rstTrans = Nothing
Set cn = Nothing
End Sub