[MS_AccessPros] Re: Creating a public variable dbs as dao.database

 

John and Duane - Appreciate the explanation, John--thanks!

I'm having trouble. It will work for a while and then I get a run-time error '91'(Object variable or With block variable not set). As expected debugging shows the following as the problem:

Set rs = dbPerm.OpenRecordset(pstrSQL)

When I use a stop and try to step through it, it stops at that line and does not go to Proc_Err.

Your help is REALLY appreciated!
Connie

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> The data available in a table gets established each time you open a
> recordset, so reusing a pointer to the database established at startup is
> no problem from that perspective. You will have a problem with data only
> if you open a recordset at startup and expect it to be current during the
> life of the application if you don't reopen it.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: mrsgoudge <no_reply@yahoogroups.com>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Tuesday, October 23, 2012 2:44 AM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Re: Creating a public variable dbs as dao.database
>
> I would not be making changes to structures. If dbPerm is set to
> CurrentDb at the point the database is opened and is used in the
> Concatenate module 2 hours later after a bunch of data has been entered,
> will all the new information be part of dbPerm?
>
> Am I being clearer?
>
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom
> <duanehookom@> wrote:
> >
> > I'm not sure what type of refreshing you are concerned about. Are you
> >making changes to table structures or similar?
> >
> > Duane Hookom
> > MS Access MVP
> >
> >
> > > To: MS_Access_Professionals@yahoogroups.com
> > > From: no_reply@yahoogroups.com
> > > Date: Mon, 22 Oct 2012 20:03:37 +0000
> > > Subject: [MS_AccessPros] Re: Creating a public variable dbs as
> >dao.database
> > >
> > > Thanks again, Duane! One last question :-) (do you believe that???)
> > >
> > > Since I don't understand a lot about CurrentDb and it's evidently kind
> >of complicated. Does my database PermDb get refreshed (or whatever it's
> >called) when the Concatenate code starts to use it?
> > >
> > > I'm realizing that it gets set when the database is opened when Main
> >Menu opens and that could be a long time before it is used.
> > >
> > > Connie
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom
> ><duanehookom@> wrote:
> > > >
> > > > I believe this should work. I wouldn't worry about setting the
> >dbPerm to nothing.Some times another error my cause the dbPerm to be lost.
> > > >
> > > > Duane Hookom
> > > > MS Access MVP
> > > >
> > > > ----------------------------------------
> > > > > From: no_reply@yahoogroups.com
> > > > >
> > > > > Duane-
> > > > >
> > > > > I'm finally able to work on this. Want to run a couple of things
> >by you:
> > > > >
> > > > > 1. I have a module with Dims that creates the Public object
> >dbPerm. Is there something that could happen that would set that db =
> >Nothing that I'd have to account for? Or is the errro handling only
> >needed in case it's set to Nothing?
> > > > >
> > > > > 2. I have added the following code in the Proc_Err portion of your
> >Concatenate code. Is it correct? (ignoring the code number)
> > > > >
> > > > > On Error Resume Next
> > > > > If Err.Number = 91 Then
> > > > > Set dbPerm = CurrentDb
> > > > > Resume
> > > > > End If
> > > > > Exit Function ' or Exit
> > > > >
> > > > > 3. Do I need to set PermDb = Nothing upon closing Access? If so,
> >How would I do that?
> > > > >
> > > > > Thanks!
> > > > > 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-6427073
> >0881E>
> > > > > > > > > > > >
> >https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730
> >881E
> > > > > > > > > > > >
> > > > > > > > > > > > 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]
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > [Non-text portions of this message have been removed]
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > ------------------------------------
> > > > > > > > > > >
> > > > > > > > > > > Yahoo! Groups Links
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > [Non-text portions of this message have been removed]
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > ------------------------------------
> > > > > > > > >
> > > > > > > > > Yahoo! Groups Links
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > ------------------------------------
> > > > > > >
> > > > > > > Yahoo! Groups Links
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > > [Non-text portions of this message have been removed]
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (25)

Recent Activity:

• New Members 6
• New Links 1
• New Files 3

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___