John Colby
jwcolby at ColbyConsulting.com
Sun Mar 12 22:24:34 CST 2006
That would be what I am after. Thanks, John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman Sent: Sunday, March 12, 2006 10:09 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Discovering Access version at runtime - was AccessXPforms bound to ADO recordsets ...not sure what you're asking here JC but you can certainly get the Access versions ...acSysCmdAccessVer will get the loaded version and acSysCmdRuntime will tell you whether its a full install or just a runtime install ...I've not based code loads on the Access version but I do based on whether or not its a runtime install ...hth. William ----- 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 XPforms 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