ms-AccessVBA不會創建新記錄并重寫舊記錄

我有一個包含超過 300 萬條記錄和一些欄位的表。看起來像這樣：

我需要的是一個由這個組成的表格，它顯示一個摘要，所以我可以根據“MaghPRV”、“MabshPRV”和“Bag”制作一份報告，向用戶顯示根據他們的計數訂購了“MeliDrv”。像這樣的東西：

所以，這是 excel 中的例行程式，但是基于大量記錄（超過 3M）我必須使用 Access，并且我已經撰寫了一個 VBA 代碼來執行此操作。問題是我在最終表格中只得到一條記錄，并且更改代碼（如添加 .MoveNext 等）并沒有改變這一點。它也很慢，根據我的搜索，沒有記錄集就沒有辦法做到這一點。謝謝你的幫助

Option Explicit

'------------------Variables----------
Sub MacroTest()
Dim dbData As DAO.Database
Dim lsPrv As Variant
Dim sSQL As String, sSQL2 As String, sSQL3 As String
Dim rs As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset
Dim cnt As Integer, i As Integer, j As Integer
Dim tmpMELIDRV As Long


'----------------Initialization--------------------
lsPrv = Array("Ardebil", "Esfahan", "Alborz", "Ilam", "E Azarbayjan", "W Azarbayjan", "Bushehr", "Tehran", "Chaharmahal", "S Khorasan", "Razavi Khorasan", "N Khorasan", "Khuzestan", "Zanjan", "Semnan", "Sistan", "Fars", "Ghazvin", "Kordestan", "Kerman", "Kermanshah", "Kohgiluyeh", "Golestan", "Gilan", "Lorestan", "Mazandaran", "Markazi", "Hormozgan", "Hamedan", "Yazd")
Set dbData = CurrentDb

dbData.Execute "CREATE TABLE tblDRV1 (drvID CHAR, drvMobile Char, carID varChar, carType varChar,MaghPrv Char, MabPrv Char, Cnt Integer);"
    'For i = 0 To 30
        'For j = 0 To 30
        i = 1
        j = 1
            sSQL = "Select MELIDRV,MOBILE,PELAK,BAG,MAGHPRV,MABSHPRV From TotQQ Where MABSHPRV='" & lsPrv(i) & "' AND MAGHPRV='" & lsPrv(j) & "';"
            Set rs = dbData.OpenRecordset(sSQL, dbOpenSnapshot)
            'IF (rs.EOF AND rs.BOF)THen GOTO
            rs.MoveFirst
            tmpMELIDRV = "1"
            While Not (rs.EOF)
                If rs!MELIDRV <> tmpMELIDRV Then
                    sSQL2 = "Select count(*) As Cnt From TotQQ Where MELIDRV='" & rs!MELIDRV & "';"
                    Set rs2 = dbData.OpenRecordset(sSQL2, dbOpenSnapshot)
                    cnt = rs2!cnt
                    'sSQL3 = "INSERT INTO tblDRV1 (drvID,drvMobile,carID,carType,MaghPrv,MabPrv,Cnt) Values ('" & rs!MELIDRV & "','" & rs!MOBILE & "','" & rs!PELAK & "','" & rs!BAG & "','" & rs!MAGHPRV & "','" & rs!MABSHPRV & "','" & rs2!cnt & "');"
                    'dbData.Execute sSQL3
                    Set rs3 = dbData.OpenRecordset("tblDRV1")
                    rs3.AddNew
                    rs3!drvID.Value = rs!MELIDRV
                    rs3!drvMobile.Value = rs!MOBILE
                    rs3!carID.Value = rs!PELAK
                    rs3!carType.Value = rs!BAG
                    rs3!MAGHPRV.Value = rs!MAGHPRV
                    rs3!MABPRV.Value = rs!MABSHPRV
                    rs3!cnt.Value = rs2!cnt
                    rs3.Update
                    rs3.Close
                    tmpMELIDRV = rs!MELIDRV
                    rs.MoveNext
                Else
                    rs.MoveNext
                End If
            Wend
rs.Close
rs2.Close
Set dbData = Nothing
Set rs = Nothing
Set rs2 = Nothing

End Sub

uj5u.com熱心網友回復：

正如@Storax 所說，只需使用查詢。
從您的第一個表中，這將創建第二個表。

SELECT      MeliDrv, NameDrive, Mobile, Pelak, Serial_Mashin, 
            Cardid_T, BAG, MaghPRV, MabshPRV, Count(*) AS Count
FROM        TotQQ 
GROUP BY    MeliDrv, NameDrive, Mobile, Pelak, Serial_Mashin, 
            Cardid_T, BAG, MaghPRV, MabshPRV

標籤：

上一篇：無法將類語法轉換為鏈表的函式語法

下一篇：使用回圈陳述句的結果作為組合框的行源