mysql - 在VBA中查询无法完成

Question

我想找到不同表中记录的总和并将输出插入到新列中，当我运行代码时它会显示错误:

“查询无法完成。要么查询结果的大小为 大于数据库的最大大小(2GB)或没有足够的 磁盘上的临时存储空间，用于存储查询结果”

它突出显示该行

STD.Open sql, cnn, adOpenStatic

我的代码如下

Option Compare Database
Option Explicit

Public cnn As New ADODB.Connection
Public db As DAO.Database

Public Sub SMain()
    Set db = Access.Application.CurrentDb
    Set cnn = CurrentProject.Connection

   Get_Value

End Sub

Private Sub Get_Value()
    Dim sql As String
    Dim STD As New ADODB.Recordset
    Dim ODR As DAO.Recordset
    Set ODR = db.OpenRecordset("Total_tbl")

    Do Until ODR.EOF
    DoEvents
    sql = "SELECT SUM(MONT_VOL.tot_n* STD_tbl.factor_n)AS TOTAL_N FROM MONT_VOL " & _
    " INNER JOIN (STD_tbl INNER JOIN Total_tbl ON STD_tbl.AREA =Total_tbl.AREA_1" & _
    " AND STD_tbl.AID = Total_tbl.AID)" & _
    " ON MONT_VOL.BID = STD_tbl.BLOCK" & _
    " WHERE MONT_VOL.BDATE = Total_tbl.Adate" & _
    " GROUP BY MONT_VOL.BID"
    STD.Open sql, cnn, adOpenStatic

        If STD.RecordCount <> 0 Then
            ODR.Edit
            ODR!New_Col= STD!TOTAL_N
            ODR.Update
        End If

        STD.Close
        ODR.MoveNext
    Loop
End Sub

我犯了什么错误？我是否正确调用了输出

ODR!New_Col= STD!TOTAL_N

Answer 1

如果查询太大(错误消息表明)，那么让我们将其分割成更小的 block 。这只有在 MySQL 中才可能实现，Access 不支持 LIMIT 或 OFFSET，解决方法很困惑，尤其是对于总计查询

我在这里做出一些假设:

1. 所有相关表都存储在同一个 MySQL 数据库中
2. 您的表具有可用于 ADO 的有效连接字符串

请注意，仅在 MySQL 中执行查询可能足以修复此错误。

Private Sub Get_Value()
    Dim sql As String
    Dim STD As New ADODB.Recordset
    Dim ODR As DAO.Recordset
    Set ODR = db.OpenRecordset("Total_tbl")
    'Create a new ADODB connection that's directly to MySQL, and doesn't use Access
    Dim adoConn2 As ADODB.Connection
    adoConn2.ConnectionString = CurrentDb.TableDefs("MONT_VOL").Connect
    adoConn2.Open
    'Initialize variables used for pagination
    Dim RecordCount As Integer
    Dim PageSize As Integer
    Dim Offset As Integer
    Offset = 0
    RecordCount = 1
    PageSize = 100
    Do Until ODR.EOF
    DoEvents

    While RecordCount <> 0
        sql = "SELECT SUM(MONT_VOL.tot_n* STD_tbl.factor_n)AS TOTAL_N FROM MONT_VOL " & _
        " INNER JOIN (STD_tbl INNER JOIN Total_tbl ON STD_tbl.AREA =Total_tbl.AREA_1" & _
        " AND STD_tbl.AID = Total_tbl.AID)" & _
        " ON MONT_VOL.BID = STD_tbl.BLOCK" & _
        " WHERE MONT_VOL.BDATE = Total_tbl.Adate" & _
        " GROUP BY MONT_VOL.BID" & _
        " LIMIT " & Offset & "," & PageSize

        STD.Open sql, adoConn2, adOpenStatic
        RecordCount = STD.RecordCount
            If STD.RecordCount <> 0 Then
                ODR.Edit
                ODR!New_Col = STD!TOTAL_N
                ODR.Update
            End If

            STD.Close
            Offset = Offset + PageSize
        Wend
        ODR.MoveNext
    Loop
    adoConn2.Close
End Sub