Returning editable ADO Recordsets to an MS Access Form using a Class Module

Mati Diaz Source

PREFACE: I am using SQL Server 2008 R2 BackEnd and MS Access 2007 for the FrontEnd

I have a Class Module that returns any ADO Recordset I want from the SQL Server. I can then assign this to any form RecordSource property.

The problem is that when I try to edit the fields it says "This form is read-only" in the status bar. I want the form to be editable.

I have two forms

  1. FormEntities
  2. FormEntitiesEdit

The FormEntitiesEdit form does NOT use the Class Module. Rather all the code is in the form itself.

The purpose of the class module is avoid redundancy and just be able to use the Class Module to get a recordset from SQL Server easily.

FIRST HERE IS MY GLOBAL MODULE

    '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

SECOND IS THE CLASS MODULE (Name is cADO). THIS CLASS MODULE USES THE conn CONNECTION OBJECT ABOVE

    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

THIRD IS THE CODE BEHIND MY FormEntities FORM (USES THE THE 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

FOURTH AND FINALLY IS THE CODE BEHIND THE FormEntitiesEdit FORM (THIS FORM DOES NOT USE THE CLASS MODULE AND I CAN EDIT IT)

    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

If Access Jet SQL could do the SQL I would bind this form to a Linked Table instead. However I am using a hierarchical (recursive) query which Jet SQL cannot do so I have to bypass the idea of bound forms to Linked Tables.

I have .Allow Edits and .AllowAdditions on the form set to true.

I also tried changing the .LockType on the ADO Recordset to

  1. adOpenKeyset and
  2. adOpenDynamic

My LockType is adLockOptimistic

As you can see the properties are set correctly to be able to edit the recordset I return.

I know this is true because when I use the FormEntitiesEdit form with the same properties it lets me edit the field. But when I use the Class Module to return (using the same properties) it says it's read-only.

This is important because as you can see it is a lot simpler to use the Class Module, just need it to return an editable recordset.

Anyone have ideas? suggestions?

classms-accessaccess-vbams-access-2007ado

Answers

answered 2 years ago HansUp #1

The problem is here in the class' cOpenRecordset() method:

.CursorLocation = CONST_CursorLocationClient

Here is where you assign constant values ...

Private Const CONST_CursorLocationServer = 3
Private Const CONST_CursorLocationClient = 2

Unfortunately, you swapped those two values. Here are the ADODB.CursorLocationEnum constants ...

adUseClient = 3
adUseServer = 2

So your class is effectively doing this ...

.CursorLocation = adUseServer

But you need a client-side cursor if you want the recordset to be editable. I think your class approach may work if you simply redefine the constant, or you will expose a different problem ...

Private Const CONST_CursorLocationClient = 3

FormEntitiesEdit is editable because you're using the correct constant there ...

.CursorLocation = adUseClient

comments powered by Disqus