[AccessD] Access XP forms bound to ADO recordsets

John,

I did try ADODB disconnected recordsets with MS Access XP/2003 MDBs and they 
seems to be working rather well.

That is a surprise here because the book "Microsoft Access Developer's Guide 
to SQL Server", which I read before states this can't be done. But the book 
I have is for MS Access 2000/MS SQL 2000 - and for MS Access 2000 this 
feature doesn't work. I don't have a new edition of the book - if it exists 
the authors may have written about this new useful feature....

I hope I'm not mistaken - I did recheck many times - yes, it seems to work! 
Please verify....

In P.S. of this message there is a sample code, which basicaly works OK but 
which needs more investigations - it doesn't yet process all the use cases. 
This code is for [authors] table from [pubs] sample database. It can work 
with both MS SQL and MS Access BEs use:

- BindFormToSQLADORecordset - to connect to MS SQL BE and

- BindFormToJetADORecordset to connect to MS Access BE.

I did check with MS SQL profiler - communication with MS SQL BE is minimal 
and connection is used only when database operation is in progress - i.e. on 
short period of time for properly organized communication with BE database.

If additional investigations/testing will show that this simple method works 
for all the required use cases then that's "what doctor ordered." - I mean 
you can relax and happily continue staying in your comfortable "bounders 
camp" for quite some time... (Although I'd recommend to use GUIDs for PKs)

It would be also very useful to make it working in disconnected optimistic 
batch update mode but I doubt it's possible - I'd be happy somebody will 
present here a sample how similar simple method can be used in disconnected 
optimistic batch mode.

Shamil

P.S.

1. create new mdb
2. connect to [authors] table from [pubs] MS SQL sample database
3. use make table query to clone [dbo_authors] to local MS Access table 
[authors]
4. close sample database
5. open sample database in shared mode
6. create form for [authors] table
7. removed form's recordsource
8. put the following code into form's module (edit connection strings if 
needed)
9. open form in normal view (to switch MS SQL/Jet backend 
comment/uncommented the code lines in Form_Load sub)
10. Enjoy!

Private mrst As ADODB.Recordset
Private mcnn As ADODB.Connection

Private mcolDeletedRowsIds As New Collection

Private Sub Form_Load()
    'BindFormToSQLADORecordset
    BindFormToJetADORecordset
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    UpdateBE
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    mrst.AddNew
End Sub

Private Sub Form_Delete(Cancel As Integer)
    If mcolDeletedRowsIds.Count = 0 Then
       mcolDeletedRowsIds.Add mrst!au_id.Value
    Else
       mcolDeletedRowsIds.Add mrst!au_id.Value, , 1
    End If
End Sub

Private Sub Form_Current()
Dim strSql As String
Dim i As Integer
    If mcolDeletedRowsIds.Count > 0 Then
       For i = mcolDeletedRowsIds.Count To 1 Step -1
          strSql = "delete from [authors] where au_id = '" & _
                   mcolDeletedRowsIds.Item(i) & "'"
          mcnn.Execute strSql
          mcolDeletedRowsIds.Remove i
       Next i
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
    mrst.Close
    mcnn.Close
End Sub

Private Sub BindFormToSQLADORecordset()
Dim strSql As String
Dim strCnn As String
    Set mcnn = New ADODB.Connection
    strCnn = "Provider=SQLOLEDB.1;" & _
             "Server=(local);" & _
             "Database=Pubs;" & _
             "Integrated Security = SSPI"
    mcnn.Open strCnn

    strSql = "select * from [authors]"
    Set mrst = New ADODB.Recordset
    mrst.CursorLocation = adUseClient

    mrst.Open strSql, mcnn, adOpenKeyset, adLockOptimistic

    mrst.ActiveConnection = Nothing

    Set Me.Recordset = mrst
End Sub

Private Sub UpdateBE()
    Set mrst.ActiveConnection = mcnn
    mrst.UpdateBatch adAffectCurrent
    Set mrst.ActiveConnection = Nothing
End Sub

Private Sub BindFormToJetADORecordset()
Dim strSql As String
Dim strCnn As String
    Set mcnn = New ADODB.Connection
    strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & CurrentDb.Name & _
             ";User Id=admin;Password=;"
    mcnn.Open strCnn

    strSql = "select * from [authors]"
    Set mrst = New ADODB.Recordset
    mrst.CursorLocation = adUseClient

    mrst.Open strSql, mcnn, adOpenKeyset, adLockOptimistic

    Set mrst.ActiveConnection = Nothing

    Set Me.Recordset = mrst
End Sub

----- Original Message ----- 
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Thursday, March 09, 2006 9:54 PM
Subject: [AccessD] Access XP forms bound to ADO recordsets


> Can someone give me an overview of what this involves?  Is the recordset
> then "disconnected"?  How do you handle form/subforms?
>
> I have a specific application where the users have a bound form which 
> pulls
> a set of records (people) that they will be making calls to.  There is a
> bound subform where they enter the results of the call - about 5 or 6
> fields, the user's ID, date/time, call status, note etc.  They get a TON 
> of
> locking issues with this. The user is almost exclusively ADDING new 
> records
> into the call status table, though they do occasionally "correct" info in
> the person's record - address / phone etc.
>
> The issue though is that as soon as they try to create a NEW record in the
> call status subform, they get the infamous "another person has locked this
> record" which is particularly puzzling since they are not editing an
> existing record but rather adding a new record.  I changed the note from
> Memo to String(255) to avoid "page locking" issues, deleted all of the
> indexes on the fields in the call status records etc but the issues still
> occur.
>
> My understanding is that Index data is also stored in pages so that there
> might be an issue with the page that an index is attempting to store it's
> data in as the record is created is locked by another user creating an 
> index
> using that same page.  Of course there is a relationship between the 
> person
> and the call status so there is at least one index that simply cannot be
> even seen, never mind deleted, at least without removing the relationship.
>
> So I am trying to resolve this continuous nagging locking issue.  I am 
> even
> willing to go unbound, but they really want to be able to see the previous
> calls in the subform so that they can tell what went on in previous calls.
> I thought that if I could bind the main people form and the child call
> status forms to ADO recordsets, then the update (and subsequent locking
> issues) would be very momentary and the probability of a lock issue
> occurring would decrease dramatically.  However I have never done the "ADO
> recordset binding" before.
>
> Does anyone have any knowledge of this?
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com