使用类模块将可编辑的ADO记录集返回到MS Access窗体

前言：我正在为FrontEnd使用SQL Server 2008 R2 BackEnd和MS Access 2007

我有一个类模块，它从SQL Server返回我想要的任何ADO记录集 . 然后，我可以将其分配给任何表单 RecordSource 属性 .

问题是，当我尝试编辑字段时，它会在状态栏中显示 "This form is read-only" . 我希望表单可以编辑 .

我有两种形式

• FormEntities

• FormEntitiesEdit

FormEntitiesEdit表单不使用类模块 . 相反，所有代码都在表单中 .

类模块的目的是避免冗余，只需使用类模块就可以轻松地从SQL Server获取记录集 .

这里首先是我的全球模块

'Default error message. 'eh' stands for error handler
    Public eh As String

    'Global variables for universal use
    Public conn As ADODB.Connection
    Public rs As ADODB.Recordset
    Public com As ADODB.Command

第二个是CLASS MODULE（名字是 cADO ） . 本课程模块使用上面的 conn 连接对象

Option Explicit

    Private Const CONST_LockType = 3
    Private Const CONST_CursorType = 1
    Private Const CONST_CursorLocationServer = 3
    Private Const CONST_CursorLocationClient = 2

    Private m_Recordset As ADODB.Recordset
    'For Public Recordset function
    Private cSQL$
    '**********************************************************************
    Public Function cGetRecordset(ByRef sql) As ADODB.Recordset

        Set m_Recordset = New ADODB.Recordset

        cSQL = sql
        cOpenRecordset

        Set cGetRecordset = m_Recordset

    End Function
    '**********************************************************************
    Public Property Set Recordset(Value As ADODB.Recordset)
        'Assigns private variable a property
        If Not Value Is Nothing Then Set m_Recordset = Value

    End Property
    '**********************************************************************
    Public Property Get Recordset() As ADODB.Recordset
        'Reads the recordset from the private variable and assigns to new object variable
        Set Recordset = m_Recordset

    End Property


    '********************************** PRIVATE SECTION **********************************

    Private Sub cOpenRecordset()

    On Error GoTo eh

        'Ensures that if a recordset is opened from previously that it closes before opening a new one
        If m_Recordset.State  adStateClosed Then m_Recordset.Close

        Set m_Recordset.ActiveConnection = conn

        With m_Recordset
            .LockType = CONST_LockType
            .CursorType = CONST_CursorType
            .CursorLocation = CONST_CursorLocationClient
            .Source = cSQL
            .Open .Source
        End With

        If Not m_Recordset.EOF Then m_Recordset.MoveFirst

    Exit Sub
    eh:
        eh = "Error # " & Str(Err.Number) & " was generated by " & _
        Err.Source & Chr(13) & Err.Description
        MsgBox eh, vbCritical, "Open Recordset System"
    End Sub
    '**********************************************************************
    Private Sub cCloseRecordset()
        m_Recordset.Close
        Set m_Recordset = Nothing
    End Sub
    '**********************************************************************
    Private Sub Class_Terminate()

        If Not (m_Recordset Is Nothing) Then Set m_Recordset = Nothing

    End Sub

第三个是我的表格背后的代码（使用 cADO CLASS MODULE）

Option Explicit

    Dim db As cADO
    '**********************************************************************
    Private Sub Form_Current()
        LoadTab
    End Sub
    '**********************************************************************
    Private Sub Form_Load()

        Set db = New cADO
        FetchRecordSource
    End Sub
    '**********************************************************************
    Private Sub FetchRecordSource()

        db.cGetRecordset ("SELECT * FROM dbo.Entities")
        Set Forms("fEntities").Recordset = db.Recordset

    End Sub

第四，最后是 FormEntitiesEdit 表格背后的代码（此表格不使用类模块，我可以编辑它）

Option Compare Database
    Option Explicit

    Dim rsEntity As New ADODB.Recordset

    '**********************************************************************
    Private Sub Form_Load()
        FetchRecordSource
    End Sub

    '**********************************************************************
    Private Sub FetchRecordSource()

        Set rsEntity.ActiveConnection = conn

        'Sets the record source for the main form
        With rsEntity
            .LockType = adLockOptimistic
            .CursorType = adOpenKeyset
            .CursorLocation = adUseClient
            .Source = "SELECT * FROM dbo.Entities"
            .Open .Source
        End With
        Set Forms("fEntitiesEdit").Recordset = rsEntity

    End Sub
    '**********************************************************************
    Private Sub CloseConn()
        rsEntity.Close
    End Sub

如果Access Jet SQL可以执行SQL，我会将此表单绑定到链接表 . 但是，我使用的是Jet SQL无法执行的分层（递归）查询，因此我必须绕过绑定表的绑定表的想法 .

我有 . 允许将表单上的Edits和.AllowAdditions设置为true .

我也尝试将ADO Recordset上的.LockType更改为

• adOpenKeyset和

• adOpenDynamic

我的LockType是adLockOptimistic

如您所见，属性设置正确，可以编辑我返回的记录集 .

我知道这是真的，因为当我使用具有相同属性的 FormEntitiesEdit 表单时，它允许我编辑该字段 . 但是当我使用类模块返回（使用相同的属性）时，它表示它是只读的 .

这很重要，因为您可以看到使用类模块要简单得多，只需要它就可以返回一个可编辑的记录集 .

有人有想法吗？建议？