Bind a table from an MDB to a form in an ADP (2002)

[adamgoossens]

Hi folks,

I have tried just about everything I can think of to get this arrangement to work. I've scoured the KB (this, this and this ) and tried every combination I can think of - nothing is working.

Here's the scenario: I store per-user settings in a file named "LocalSettings.mdb". The situation has come up where I need to bind a form to a table in this MDB.

Here's the code I'm currently using in the Form_Open event of the target form:

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    With rs
        .Source = "tblSearchCriteria"
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        
        Set .ActiveConnection = basPersonal.GetLocalSettingsADOLink
        
        .Open
    End With

    Set Me.Recordset = rs

Oh, if it helps, the ConnectionString used in GetLocalSettingsADOLink is Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.path & "\" & "LocalSettings.mdb".

This is where it gets odd. If I use a client-side cursor, when I try to edit fields on the form I get a message in the status bar: "Recordset is not updatable". If I use a server-side cursor, I get the message "Form is read only.".

Yet in *both* cases I can successfully manipulate the recordset directly in code.

Has anybody been able to successfully pull this off? Prior to Access 2002 they suggested you use DAO for this (since DAO plays really nice with Jet), but ADO was brought up to a level playing field with DAO in 2002 (apparently).

Thanks,
-Adam.