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

Michael R Mattys mmattys at rochester.rr.com
Sun Mar 12 22:12:52 CST 2006


Conditional compilation doesn't quite seem to meet the task,
but I noticed this enigmatic statement in help: "Public compiler
constants can only be created in the user interface."  I wonder
what that means?

So then, the only other alternative that comes to mind is to use
mde libraries for each version and invoke one or the other based
upon the value of acSysCmdAccessVer.

Michael R. Mattys
MapPoint Developer
www.mattysconsulting.com

----- Original Message ----- 
From: "John Colby" <jwcolby at colbyconsulting.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Sunday, March 12, 2006 7:12 PM
Subject: [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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com 




More information about the AccessD mailing list