Connie-
Where is dbPerm defined, and where is it set? It's losing the pointer.
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 8:24 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [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-642707
>>3
> >0881E>
> > > > > > > > > > > >
>
>>https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-6427073
>>0
> >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
>
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (26) |
Tidak ada komentar:
Posting Komentar