Henry Simpson
hsimpson88 at hotmail.com
Thu Mar 20 21:47:00 CST 2003
Well... unhandled errors do not close forms opened from the database window
or by docmd, nor queries or reports nor cause controls on open forms to lose
their properties including rowsources or text. There are some anomalies.
Assume a form that can be opened from the database window named frmTest, try
the following code in a module:
Global a As Form_frmTest
Public b As Form_frmTest
Dim c As Form_frmTest
Sub VariableTest()
Static d As Form_frmTest
Dim e As Form_frmTest
Set a = New Form_frmTest
a.Visible = True
Set b = New Form_frmTest
b.Visible = True
Set c = New Form_frmTest
c.Visible = True
Set d = New Form_frmTest
d.Visible = True
DoCmd.OpenForm "frmTest"
End Sub
Sub VariableTest2()
Dim f As Form_frmTest
Set f = New Form_frmTest
f.Visible = True
DoCmd.OpenForm "frmTest"
End Sub
Close all open forms. Place a breakpoint (F9) on both End Sub lines. Place
the cursor in the first procedure and hit the F5 key to run the code. Then
in the immediate pane of the debug window, run the following:
?forms.count
?forms(0).name
?forms(1).name
?forms(2).name
?forms(3).name
?forms(4).name
Then hit F8 or F5 in the code window to finish running the module code and
switch to the debug window and rerun the line
?forms.count
Then run the following line in the immediate pane
end
and then rerun the ?forms.count line
Aha. Only one form left open, but there is still one that survived.
Close the open form and then run the code in the second procedure and while
it is stopped at the break point, rerun the ?forms.count line. Then let
the code run to completion and again rerun the ?forms.count line.
What have we learned about the persistence of Global, Public, module level
and procedure level and static procedure level variables? What's strange is
that the local variable in the first procedure survived execution of the sub
while the local variable in the second went out of scope. This is also why
you don't refer to forms in the forms collection by name. If ever you use
multiple instances, you must use an object variable to identify an instance.
If you close these forms in the debug window by running the line
docmd.Close acform,"frmTest"
several times, you will see that the last form closed won't be the
'persistent' form.
Hen
>From: "John W. Colby" <jcolby at colbyconsulting.com>
>Reply-To: accessd at databaseadvisors.com
>To: <accessd at databaseadvisors.com>
>Subject: RE: [AccessD] Using Global Connections
>Date: Thu, 20 Mar 2003 20:36:04 -0500
>
>Eric,
>
>If you have unhandled errors causing globals to lose their value you have
>big problems. Unhandled errors pretty much reset the whole damned world,
>not just "global" variables.
>
>John W. Colby
>Colby Consulting
>www.ColbyConsulting.com
>
>-----Original Message-----
>From: accessd-admin at databaseadvisors.com
>[mailto:accessd-admin at databaseadvisors.com]On Behalf Of StaRKeY
>Sent: Thursday, March 20, 2003 4:30 PM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] Using Global Connections
>
>
>First of all, I am not against globals but I think there are a couple of
>things to bare in mind:
>
>1) - kept open connections could slow down traffic in general, usually only
>if your app is used by a lot of
> people/connections
>2) - what is the max. amount of open connections allowed?
>3) - globals can loose their value in case an unhandled error occurs
>4) - no Mark, in a LOT of cases you are NOT the only programmer over
>time...
>
>Last but not least, I've seen many different Access applications and to be
>honest, whenever I was asked to come fix or upgrade an app. I've never
>really cared about how the job was done unless it actually was
>creating/being a problem.
>
>My two cents..
>Eric
>
>-----Original Message-----
>From: accessd-admin at databaseadvisors.com
>[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Mark L. Breen
>Sent: donderdag 20 maart 2003 22:22
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Using Global Connections
>
>
>Hello John,
>
>I am glad to hear that I am on the right track anyway. The reason that I
>asked the question was that I noticed a few people referring to globals
>recently. Also, I have been reading a bit about them and the author was
>complaining about globals. But, as we are both agreed, for a global
>constant connection, there is a lot of upside and very little downside to
>using one. I had considered using a class and setting properties, bit
>again, that is only really complictating it further. However, I could make
>it a read only class, and that would have some benefit, or I could do as
>you
>do and use a function.
>
>Based on the fact that I am the programmer, nobody else is going to mess
>with my global variable, I can probably continue to use a normal global and
>pass it around.
>
>I am reading with interest your migration to .net, I am about to start the
>same thing myself in the next few weeks.
>
>If I have anything to post, I will.
>
>I think that everybody on the list should let each other know if we come
>across any good reading material, especially online / free stuff.
>
>Thanks to all for the feedback on the globals.
>
>Best Regards
>
>
>Mark L. Breen
>Solution Providers
>Ireland
>
>
>
>
>----- Original Message -----
>From: "John W. Colby" <jcolby at colbyconsulting.com>
>To: <accessd at databaseadvisors.com>
>Sent: Saturday, March 15, 2003 5:03 PM
>Subject: RE: [AccessD] Using Global Connections
>
>
> > Mark,
> >
> > I also use globals for the db connection. This is a case where the
> > connection is specific, a penalty is paid for creating / deleting it,
>and
>it
> > is needed all over the app. The connection is not something that is
>going
> > to be updated (written to) by pieces of the application, it is going to
>be
> > created once and then used all over. That is a good candidate for a
>global.
> >
> >
> > Globals tend to cause problems when they are *updated* from all over the
> > place, and the reason is that is quite difficult to track down who is
>doing
> > the updating when it is incorrectly updated. If for example a serial
>port
> > is opened and closed by 10 different functions, one function tries to
>use
> > the serial port and it is closed. Who closed it? It's supposed to be
> > opened, but some process closed it unexpectedly. These kinds of
>problems
> > can be a real bear to track down and fix.
> >
> > It becomes a bigger problem if the system is written and maintained by a
> > team of programmers where nobody knows the whole story. Globals by
> > definition are available to anyone at any time.
> >
> > Scope exists for a reason. If a variable is only used by a specific
> > function, then it's variables have no business being global to the
>program
> > (or even the module). If a variable is only used by code inside an
>instance
> > of a class but needs to be accessed by several functions inside the
>class,
> > then it should be defined inside the class (in the header) as a PRIVATE
> > variable. Thus only code inside the class can modify the variable. If
>a
> > variable needs to be shared by many functions inside of a module, but
>only
> > these functions modify the variable, then the variable should be PRIVATE
>to
> > that module. By making any of these variables truly Global to the
>project,
> > you end up with things being (able to be) set by code that has no
>business
> > setting the variable.
> >
> > On the other hand, if as in your example, a database connection is
>needed,
> > and a function calls a function which calls a function, which needs that
> > connection, why pass the connection through 3 functions that don't even
>need
> > the connection simply so that the fourth function that does need it can
>have
> > access to the connection?
> >
> > In a case like this, I tend to make the connection a private variable
>inside
> > a module, with a function that initializes the variable when the db
>opens,
> > and then define a function that READS the connection variable and
>returns
> > the value. This in effect makes it read only. Anyone can call the
>function
> > that gets the value, but since the variable is PRIVATE to the module, no
>one
> > outside the module can write to it.
> >
> > John W. Colby
> > Colby Consulting
> > www.ColbyConsulting.com
> >
> > -----Original Message-----
> > From: accessd-admin at databaseadvisors.com
> > [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Mark L. Breen
> > Sent: Friday, March 14, 2003 12:37 PM
> > To: accessd at databaseadvisors.com
> > Subject: Re: [AccessD] Using Global Connctions
> >
> >
> > Hello All,
> >
> > I was not aware that a previous debate (sounds a little heated) had been
> > already carried out. Sorry for duplicating the discussion.
> >
> > In the last few emails, Marcus was the only one that explicitly stated
>that
> > he likes the idea of using Globals for a db connection.
> >
> > So leaving aside passing around an order nunber or a customer id as a
>global
> > (which I would never even consider), what do you guys do to hand around
>a
> > database connection? Do you use Globals or do you make the connction
>each
> > time or do you use some kind of class. Any sample code would be
>interesting
> > to read.
> >
> > What I do is have the following code in a module
> >
> > ' This will be the global connection that we will share, we open it once
> > only and share it throughout the applications life
> > Global gccnn As New ADODB.Connection
> > Global Const gcstrConStr As String = "Provider=SQLOLEDB.1;Integrated
> > Security=SSPI;Persist Security Info=False;Initial Catalog=spps40;Data
> > Source=PC1"
> >
> >
> > and then in the first form_load I have
> >
> > ' Give it a connect string
> > gccnn.ConnectionString = gcstrConStr
> >
> > ' And make the connection
> > gccnn.Open
> >
> >
> >
> > What do you guys think of this strategy, should I use a class to pass it
> > around. And if I am not using a class, do you think that it is a little
>bit
> > untidy or is downright bad practice.
> >
> > Again, I am just curious what the rest of the world is doing in their
>apps.
> >
> > Thanks
> >
> > Mark
> >
> >
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
> >
> > ----------------------------------------------------
> > Is email taking over your day? Manage your time with eMailBoss.
> > Try it free! http://www.eMailBoss.com
> >
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>---
>Message is tested virus free
>Virus Database (VPS): 19-3-2003
>Tested on: 20-3-2003 22:29:50
>
>(c) 2000-2003 ALWIL Software.
>http://www.avast.com
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>
>----------------------------------------------------
>Is email taking over your day? Manage your time with eMailBoss.
>Try it free! http://www.eMailBoss.com
><< winmail.dat >>
_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail