MS Access列表框绑定Sqlite的ADODB记录集仅显示单条记录如何解决

故障核心原因 #

• Access原生Listbox的Recordset属性仅对本地ACE/Jet引擎的DAO记录集、或者客户端游标类型的ADODB记录集兼容，你当前使用的adOpenKeyset属于服务端游标，SQLite ODBC返回的该类游标无法被Listbox正确遍历所有行。你看到RecordCount返回100是因为adOpenKeyset游标本身可以正确统计行数，但Listbox遍历逻辑不兼容该类外部服务端游标，只能读取到游标当前指向的第一条记录。
• 你设置RowSourceType = "Table/Query"时，Access默认会尝试用本地数据库引擎解析行来源，和你绑定的外部SQLite的ADODB记录集不匹配，也会导致只能读取到首行记录。
• 你在赋值完Recordset后立刻释放了Rst和Cnn对象，外部ADODB记录集依赖活动的连接和对象引用，提前释放会导致记录集后续遍历失败。

修复方案 #

方案1：调整ADODB记录集属性，适配Listbox绑定 #

修改记录集游标参数，改用客户端静态游标即可正常绑定，修改后代码如下：

Private Sub cdRefresh_Click()
    Dim Cnn As Object, Rst As Object
    Dim cnString As String, SqlStr As String
    
    cnString = "DRIVER=SQLite3 ODBC Driver;Database=E:\Sample Data\Sales1.db"
    Set Cnn = VBA.CreateObject("ADODB.Connection")
    ' 新增：设置连接使用客户端游标
    Cnn.CursorLocation = 3 ' adUseClient
    SqlStr = "Select * From Sales Limit 100;"
    Cnn.Open cnString
    Set Rst = VBA.CreateObject("ADODB.Recordset")
    With Rst
        Set .ActiveConnection = Cnn
        .Source = SqlStr
        .LockType = 3 '' adLockOptimistic
        ' 游标类型改用静态游标，兼容性更好
        .CursorType = 3 '' adOpenStatic
        .Open
    End With
    Debug.Print "records #"; Rst.RecordCount
    With Me.List0
        .RowSource = ""
        .RowSourceType = "Table/Query"
        .ColumnCount = Rst.Fields.Count
        .ColumnHeads = True
        Set .Recordset = Rst
    End With
    ' 注意：不要立刻释放Rst和Cnn，可将其设为窗体级变量避免被自动回收
    ' Set Rst = Nothing
    ' Set Cnn = Nothing
End Sub

方案2：遍历记录集填充值列表，兼容性最高 #

不需要维持连接和处理游标兼容问题，适合数据量不大的场景：

Private Sub cdRefresh_Click()
    Dim Cnn As Object, Rst As Object
    Dim cnString As String, SqlStr As String
    Dim fld As Object, rowStr As String
    
    cnString = "DRIVER=SQLite3 ODBC Driver;Database=E:\Sample Data\Sales1.db"
    Set Cnn = VBA.CreateObject("ADODB.Connection")
    SqlStr = "Select * From Sales Limit 100;"
    Cnn.Open cnString
    Set Rst = VBA.CreateObject("ADODB.Recordset")
    With Rst
        Set .ActiveConnection = Cnn
        .Source = SqlStr
        .LockType = 3 '' adLockOptimistic
        .CursorType = 1 '' adOpenKeyset
        .Open
    End With
    Debug.Print "records #"; Rst.RecordCount
    With Me.List0
        ' 改用值列表类型
        .RowSourceType = "Value List"
        .ColumnCount = Rst.Fields.Count
        .ColumnHeads = True
        ' 清空旧内容
        .RowSource = ""
        ' 填充列标题
        For Each fld In Rst.Fields
            rowStr = rowStr & fld.Name & ";"
        Next
        .AddItem Left(rowStr, Len(rowStr) - 1)
        ' 遍历所有记录填充行
        Do While Not Rst.EOF
            rowStr = ""
            For Each fld In Rst.Fields
                rowStr = rowStr & Nz(fld.Value, "") & ";"
            Next
            .AddItem Left(rowStr, Len(rowStr) - 1)
            Rst.MoveNext
        Loop
    End With
    ' 可正常释放资源
    Rst.Close
    Cnn.Close
    Set Rst = Nothing
    Set Cnn = Nothing
End Sub

内容的提问来源于stack exchange，提问作者Leo Sam