[AccessD] Discovering Access version at runtime - was Access XP forms bound to ADO recordsets

>and for MS Access 2000 this feature doesn't work.

And this brings up to an important question, can a framework dynamically
configure to use code based on the version?

I use the same framework for A2K and above.  The reason I cut off at A2K
that raisevents do not work in A97 and I make some use of RaiseEvents in the
framework.  Now we have code which works in AXP but not A2K and below.  So I
would like to carve this stuff out and only load it if the version being run
under is AXP or above.  A quick Google search does not come up with
anything.

If this can be done, XP also supports form event sinks in two different
class modules without the page faults that A2K would trigger if you tried
that, so it might work well to place these event sinks out in a completely
separate class and instantiate that class only if the version was XP or
above.

Thoughts about this?


John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Friday, March 10, 2006 7:36 PM
To: Access Developers discussion and problem solving
Subject: Re: [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