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

Heenan, Lambert Lambert.Heenan at AIG.com
Mon Mar 13 09:57:23 CST 2006


 "Public compiler constants can only be created in the user interface.  I
wonder what that means?"

If you declare a compiler consent in a module like this

#Const Foo = 1

Then "Foo" is only visible in the module it is declared in. If you need a
conditional compiler constant that is globally visible in all modules, then
you need to declare it though the menus (that's what "in the user interface"
means). To do so open any module and then select the application Properties
option on the module Tools menu.  You will see a dialog box which has a
textbox labeled "Conditional Compiler Arguments" and you can enter a list of
such arguments like this

Foo = 1 : Bar = 2

Notice the colon is used as a delimiter. You can only declare constants with
simple integer values. You can then use these compiler arguments in any code
module. Here's a trivial example.

Sub CheckCompilerArgs()

#If Foo = 1 Then
    Debug.Print "Foo = 1"
#End If

#If Bar = 1 Then
    Debug.Print "Bar = 1"
#End If

#If Bar = 2 Then
    Debug.Print "Bar = 2"
#End If

End Sub

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael R Mattys
Sent: Sunday, March 12, 2006 11:13 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Discovering Access version at runtime - was Access XP
forms bound to ADO recordsets


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 

-- 
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