ACCESS FEED: [MS_AccessPros] Re: Creating a public variable dbs as dao.database -- TempVars

Hi Crystal,

I've found that TempVars do not store objects. So since I'm trying to store a DB they won't work. Am I not getting what you were saying???

Thanks!
Connie

--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> > "I have Access 2007"
>
> use TempVars or database properties, they don't get lost when errors happen like global variables do
>
> TempVars are easier to use but get lost when the database is closed.ï¿½ Database properties are kept.
>
> __________
> TempVars
>
> in VBA:
> TempVars.Add "tvVariableName", value
>
> if the tempvar already has a value, it will be changed
>
> you can retrieve the value like this:
>
> TempVars!tvVariableName
> or this:
> TempVars("tvVariableName")
>
>
>
> Warm Regards,
> Crystal
>
> ï¿½*
> ï¿½ï¿½ (: have an awesome day :)
> ï¿½*
>
>
>
> ________________________________
> From: Duane Hookom <duanehookom@...>
> To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
> Sent: Wednesday, October 17, 2012 1:53 AM
> Subject: RE: [MS_AccessPros] Re: Creating a public variable dbs as dao.database
>
> I don't know what the error number is. There must be something that triggers the setting of the db object prior to running the code. I would do this in the On Open event of some form.
>
> Duane
>
>
> > To: MS_Access_Professionals@yahoogroups.com
> > From: no_reply@yahoogroups.com
> > Date: Wed, 17 Oct 2012 04:17:33 +0000
> > Subject: [MS_AccessPros] Re: Creating a public variable dbs as dao.database
> >
> > Duane--valuable input! Thank you!
> >
> > Is Error 91 the one to be concerned with? Are there others? So I'd create and then set the public variable in the module. If the public variable is created in it's own module, does that mean that it's created and set upon opening of Access or do I need to do something to make that happen?
> >
> > Connie
> >
> > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > >
> > > I prefer Concatenate() since I understand it better and it seems to provide greater flexibility. Concatenat() can be used to replace DLookup() or any other domain aggregate function like:
> > > DMax("OrderDate","ORDERS","CustomerID =" & [CustomerID])
> > > Concatenate("SELECT Max(OrderDate) FROM ORDERS WHERE CustomerID = " & [CustomerID])
> > >
> > > You are correct regarding the public db variable. You must catch the error if the db object hasn't been set. The error handling must set the db variable and continue executing the function.
> > >
> > > Duane Hookom
> > > MS Access MVP
> > >
> > >
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > From: no_reply@yahoogroups.com
> > > > Date: Tue, 16 Oct 2012 21:00:27 +0000
> > > > Subject: [MS_AccessPros] Re: Creating a public variable dbs as dao.database
> > > >
> > > > Duane,
> > > >
> > > > Appreciate you comments and explanation. Is there a reason you recommend the generic concatenate function you mentioned?
> > > >
> > > > I have some instances where I have used ConcatRelated in a query because it's necessary. In order to "modify the code to use a public DB object so it doesn't have to continually create and set a database object to nothing" would I set the public DB in a module of its own with something like Public myGlobalVar As String? I have Access 2007.
> > > >
> > > > Thanks!
> > > > Connie
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > > > >
> > > > > The information I posted was copied and pasted from Allen's web page. I'm not sure about the version(s).
> > > > >
> > > > > I would use the generic concatenate function found at http://www.tek-tips.com/faqs.cfm?fid=4233. When used with larger recordsets (in a query), I have modified the code to use a public DB object so it doesn't have to continually create and set a database object to nothing.
> > > > >
> > > > > Duane Hookom
> > > > > MS Access MVP
> > > > >
> > > > > ----------------------------------------
> > > > > > From: wrmosca@
> > > > > >
> > > > > > Duane -
> > > > > >
> > > > > > Thanks for clarifying.
> > > > > >
> > > > > > > It does not clean up after itself (can result in Not enough databases/tables errors).
> > > > > >
> > > > > > Is that still the case? I thought that bug was fixed in 2003.
> > > > > >
> > > > > > Bill
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > > > > > >
> > > > > > > ELookup() is probably Allen Browne's code at http://www.allenbrowne.com/ser-42.html where he states:
> > > > > > > Why a replacement?
> > > > > > > DLookup() has several shortcomings:
> > > > > > >
> > > > > > > It just returns the first match to finds. Since you cannot specify a sort order, the result is unpredictable. You may even get inconsistent results from the same data (e.g. after compacting a database, if the table contains no primary key).
> > > > > > > Its performance is poor.
> > > > > > > It does not clean up after itself (can result in Not enough databases/tables errors).
> > > > > > > It returns the wrong answer if the target field contains a zero-length string.
> > > > > > > ELookup() addresses those limitations:
> > > > > > >
> > > > > > > An additional optional argument allows you to specify a sort order. That means you can specify which value to retrieve: the min or max value based on any sort order you wish to specify.
> > > > > > > It explicitly cleans up after itself.
> > > > > > > It runs about twice as fast as DLookup(). (Note that if you are retrieving a value for every row of a query, a subquery would provide much better performance.)
> > > > > > > It correctly differentiates a Null and a zero-length string.
> > > > > > >
> > > > > > > ConcatRelated() is also Allen's http://www.allenbrowne.com/func-concat.html.
> > > > > > >
> > > > > > > Duane Hookom
> > > > > > > MS Access MVP
> > > > > > >
> > > > > > >
> > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > > From: wrmosca@
> > > > > > > > Date: Thu, 11 Oct 2012 08:40:05 -0700
> > > > > > > > Subject: RE: [MS_AccessPros] Re: Creating a public variable dbs as dao.database
> > > > > > > >
> > > > > > > > Connie -
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Using ELookup (I'm assuming that is a public procedure) is not going to do
> > > > > > > > anything that DLookup can't do. DLookup just gets a snapshot anyway. The
> > > > > > > > connection is open just long enough to get the result.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > What is ConcatRelated?
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > I manage the forms through a main menu so the user cannot open more than is
> > > > > > > > necessary. Reports use a snapshot so, again, the connection is only open long
> > > > > > > > enough to get the data.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Without seeing your database, I can't offer any specific suggestions. I assume
> > > > > > > > you are using a local front end. How many users are in the back end at one time?
> > > > > > > > 25 or so is the comfortable maximum even though Access will support 255
> > > > > > > > concurrent users. If you are exceeding the open connections more than once a
> > > > > > > > yearly quarter and can't solve the problem I strongly suggest you move your back
> > > > > > > > end into SQL Server Express. That is a free version of SQL Server. There is a
> > > > > > > > bit of a learning curve, but if you've been working in Access you'll figure out
> > > > > > > > how to create tables as the design page looks very much like the Access version.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > >
> > > > > > > > Bill
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > > > > > > > Sent: Wednesday, October 10, 2012 9:40 AM
> > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > > Subject: [MS_AccessPros] Re: Creating a public variable dbs as dao.database
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Bill--Thanks! I had been setting all db and rs to nothing but his comment made
> > > > > > > > me wonder.
> > > > > > > >
> > > > > > > > After reading the following can you think of anything else I should be checking?
> > > > > > > > I can still force the message about too many open databases if I open a bunch of
> > > > > > > > forms or reports. I'm concerned that having too many open databases could be an
> > > > > > > > increasing problem as more data is added. On some forms I have quite a few
> > > > > > > > comboboxes based on queries and the number of records for those will increase as
> > > > > > > > more data is entered.
> > > > > > > >
> > > > > > > > So far I have:
> > > > > > > > 1. Gotten rid of as many ConcatRelated's as this seemed to be a cause.
> > > > > > > > 2. Changed DLookups to ELookups
> > > > > > > > 3. Searched the code several times for unclosed databases and recordsets.
> > > > > > > > 4. Am trying to force the closing of all unnecessary open forms/reports. If I
> > > > > > > > actually get this going I'm guessing my problem is "solved". But it seems like a
> > > > > > > > bandaid and I'd really like to learn what I'm doing incorrectly so I don't do it
> > > > > > > > in the future.
> > > > > > > >
> > > > > > > > Since so many things are intertwined (Listings, Sellers, Buyers, etc) I'm
> > > > > > > > guessing that it would be hard to archive much. And I'm sure the number of
> > > > > > > > records we're working with is a tiny portion of other businesses where Access is
> > > > > > > > used.
> > > > > > > >
> > > > > > > > Thanks!
> > > > > > > > Connie
> > > > > > > >
> > > > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca" <wrmosca@>
> > > > > > > > wrote:
> > > > > > > > >
> > > > > > > > > Connie
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > You can create a global variable as suggested. Or you can practice good coding
> > > > > > > > > by setting your db object to nothing within each procedure where you set it.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Example:
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Public Function Test_Code()
> > > > > > > > >
> > > > > > > > > Dim db As DAO.Database
> > > > > > > > >
> > > > > > > > > Dim rs As DAO.Recordset
> > > > > > > > >
> > > > > > > > > Dim strSQL As String
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Set db = CurrentDb
> > > > > > > > >
> > > > > > > > > strSQL = "SELECT "
> > > > > > > > >
> > > > > > > > > Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
> > > > > > > > >
> > > > > > > > > Set rs = Nothing
> > > > > > > > >
> > > > > > > > > Set db = Nothing
> > > > > > > > >
> > > > > > > > > End Function
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > While I don't know how anyone could come up against the limit of objects if
> > > > > > > > they
> > > > > > > > > are handled properly, it's probably because I manage my pointers as they
> > > > > > > > should
> > > > > > > > > be managed. I don't know your source that you quoted here so I won't cast
> > > > > > > > > disparaging accusations against him.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > I just think he's wrong if you code properly.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Regards,
> > > > > > > > > Bill Mosca,
> > > > > > > > > Founder, MS_Access_Professionals
> > > > > > > > > That'll do IT <http://thatlldoit.com/> http://thatlldoit.com
> > > > > > > > > MS Access MVP
> > > > > > > > >
> > > > > > > > >
> > > > > > > > <https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E>
> > > > > > > > > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> > > > > > > > >
> > > > > > > > > My Nothing-to-do-with Access blog
> > > > > > > > >
> > > > > > > > > <http://wrmosca.wordpress.com> http://wrmosca.wordpress.com
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> > > > > > > > > Sent: Monday, October 08, 2012 2:58 PM
> > > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > Subject: [MS_AccessPros] Creating a public variable dbs as dao.database
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > I was on the net and found someone saying that CurrentDb sometimes in complex
> > > > > > > > > and recursive procedures can easily "reach a limit of open references (or
> > > > > > > > > instances) CurrentDb can hold." He has code that illustrates this and then
> > > > > > > > gives
> > > > > > > > > the following suggestion:
> > > > > > > > >
> > > > > > > > > "The workaround is to declare a public variable dbs as dao.database, set it to
> > > > > > > > > CurrentDb at program startup and then use it instead of CurrentDb. "
> > > > > > > > >
> > > > > > > > > Does anybody see a problem with doing this?
> > > > > > > > >
> > > > > > > > > Thanks!
> > > > > > > > > Connie
> > > > > > > > >
>
>
> [Non-text portions of this message have been removed]
>