ACCESS FEED: Re: [MS_AccessPros] Decreasing potential of "too many databases"

Hi All,

Afeter a bit more searching I found another link, this
time for Access2003 and and illustrating the difference
between using linked and local tables.

http://bytes.com/topic/access/answers/472616-new-twist-too-many-databases-error

Regards, Clive.

--- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@...> wrote:
>
> Hi All,
>
> Here is a link that sheds some more light on the subject
> even though it was for an earlier version of Access:
>
> http://www.office-archive.com/23-ms-access/037cc99c3e46753c.htm
>
> And another link that provides a solution if you are using
> Privacy Manager for HP Protect Tools:
>
> http://msmvps.com/blogs/access/
>
> Regards, Clive.
>
> --- In MS_Access_Professionals@yahoogroups.com, Liz Ravenwood <liz_ravenwood@> wrote:
> >
> > :-)
> >
> > Liz Ravenwood
> > Programmer/Analyst
> > Super First Class Products
> > B/E Aerospace
> > O: 1.520.239.4808
> > www.beaerospace.com
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> > Sent: Monday, August 13, 2012 1:35 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: RE: [MS_AccessPros] Decreasing potential of "too many databases"
> >
> > Not in this case. She's already setting db = CurrentDb and then not releasing
> > it. If you're gonna set it, might as well use it. I agree for a "one off,"
> > it's OK to use CurrentDb.Execute.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
> > Sent: Monday, August 13, 2012 10:23 PM
> > To: 'MS_Access_Professionals@yahoogroups.com'
> > Subject: RE: [MS_AccessPros] Decreasing potential of "too many databases"
> >
> > Bill, me too.
> >
> > Liz Ravenwood
> > Programmer/Analyst
> > Super First Class Products
> > B/E Aerospace
> > O: 1.520.239.4808
> > www.beaerospace.com
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
> > Sent: Monday, August 13, 2012 1:16 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Decreasing potential of "too many databases"
> >
> > Liz
> >
> > I disagree with John. When it comes to one execution and you're done I use
> > CurrentDB.Execute.
> >
> > Bill Mosca
> >
> > --- In MS_Access_Professionals@yahoogroups.com, Liz Ravenwood
> > <liz_ravenwood@> wrote:
> > >
> > > John, why? Even if you're just going to do a one line deal?
> > >
> > > Liz Ravenwood
> > > Programmer/Analyst
> > > Super First Class Products
> > > B/E Aerospace
> > > O: 1.520.239.4808
> > > www.beaerospace.com
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> > > Sent: Monday, August 13, 2012 12:49 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: RE: [MS_AccessPros] Decreasing potential of "too many databases"
> > >
> > > Connie-
> > >
> > > You should *never* IMHO use CurrentDb.Execute. Always set a db object, the
> > use
> > > db.Execute. Then Set db = Nothing before you exit.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > > Sent: Monday, August 13, 2012 9:43 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: Re: [MS_AccessPros] Decreasing potential of "too many databases"
> > >
> > > Thanks John. One more question--I'm going through my code and found the
> > > following. I have both db and CurrentDb.Execute. What happens in this
> > > case--Does CurrentDb.Execute open one more instance of the database in
> > addition
> > > to the one opened at Set db = CurrentDb? If so, how would I change it?
> > >
> > > Thanks again!
> > > Connie
> > >
> > > If response = vbYes Then
> > > 'Initialize
> > > Set db = CurrentDb
> > > 'Turn off the "are you sure you want to delete" message
> > > DoCmd.SetWarnings False
> > > Dim strSQL As String
> > > strSQL = "DELETE Withdrawn.* FROM Withdrawn WHERE Withdrawn.ListID
> > =
> > > " & Me.ListID & ";"
> > > CurrentDb.Execute strSQL, dbFailOnError
> > > 'Turn the warning capability back on
> > > DoCmd.SetWarnings True
> > > 'Save Record
> > > If Me.Dirty Then
> > > Me.Dirty = False
> > > End If
> > > 'Set Current Listing to Yes
> > > Set rstL = db.OpenRecordset("SELECT * FROM Listings WHERE ListID =
> > "
> > > & Me.ListID & ";")
> > > rstL.Edit
> > > rstL("CurrentListing").Value = -1
> > > rstL.Update
> > > 'Set Status to "For Sale"
> > > Forms!Listings.Status = "FOR SALE"
> > > DoCmd.Close acForm, "UndoWithdrawn", acSaveNo
> > >
> > > End If
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > > >
> > > > Connie-
> > > >
> > > > The "too many databases" is a mystery. Me, I like to control what's going
> > on,
> > > > so I always assign CurrentDb to an object variable and then make sure I
> > clear
> > > it
> > > > before exit. With CurrentDb, you know you're always opening one more
> > thread,
> > > > then killing it when you Set db = Nothing. I dunno what happens with a
> > > > reference to DBEngine.
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > >
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > > > Sent: Monday, August 13, 2012 9:14 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: Re: [MS_AccessPros] Decreasing potential of "too many databases"
> > > >
> > > > John - That is code I copied from Allen Browne's site. Should I be changing
> > > > that? Any idea why Crystal would experience a decrease in the "cannot open
> > > > databases" messages using DBEngine(0)(0)?
> > > >
> > > > Thanks!
> > > > Connie
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > > > >
> > > > > Connie-
> > > > >
> > > > > Why are you using DBEngine(0)(0)? I would declare a db object As
> > > > DAO.Database,
> > > > > set it to CurrentDb, then release it in your exit code.
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2010 Inside Out
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas.com/
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > > > > Sent: Monday, August 13, 2012 8:03 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: [MS_AccessPros] Decreasing potential of "too many databases"
> > > > >
> > > > > Good afternoon! Evening to you, John!
> > > > >
> > > > > I am working on getting rid of the "too many databases" message again and
> > > > doing
> > > > > my best to decrease the chances it will crop up. From some reading I've
> > > done,
> > > > > it seems that using the following function in a query could be a major
> > > > > contributor. Yes? when I do get the "too many databases" message
> > debugging
> > > > > shows that it's usually stopped at this point.
> > > > >
> > > > > I'm considering putting the result in the underlying table. For example,
> > > once
> > > > > the listers are entered and the ListingContacts form is closed this would
> > be
> > > > > entered for that listing in
> > > > > Listings.AllContacts.
> > > > >
> > > > > Input? I know this doesn't really follow the rules but I can only see the
> > > > > benefits.
> > > > >
> > > > > Thanks,
> > > > > Connie
> > > > >
> > > > >
> > > > > Public Function ConcatRelated(strField As String, _
> > > > > strTable As String, _
> > > > > Optional strWhere As String, _
> > > > > Optional strOrderBy As String, _
> > > > > Optional strSeparator = ", ") As Variant
> > > > > On Error GoTo Err_Handler
> > > > > 'Purpose: Generate a concatenated string of related records.
> > > > > 'Return: String variant, or Null if no matches.
> > > > > 'Arguments: strField = name of field to get results from and
> > > concatenate.
> > > > > ' strTable = name of a table or query.
> > > > > ' strWhere = WHERE clause to choose the right values.
> > > > > ' strOrderBy = ORDER BY clause, for sorting the values.
> > > > > ' strSeparator = characters to use between the concatenated
> > > > > values.
> > > > > 'Notes: 1. Use square brackets around field/table names with
> > spaces
> > > or
> > > > > odd characters.
> > > > > ' 2. strField can be a Multi-valued field (A2007 and later),
> > > but
> > > > > strOrderBy cannot.
> > > > > ' 3. Nulls are omitted, zero-length strings (ZLSs) are
> > > returned
> > > > as
> > > > > ZLSs.
> > > > > ' 4. Returning more than 255 characters to a recordset
> > > triggers
> > > > > this Access bug:
> > > > > ' http://allenbrowne.com/bug-16.html
> > > > > Dim rs As DAO.Recordset 'Related records
> > > > > Dim rsMV As DAO.Recordset 'Multi-valued field
> > > > > recordset
> > > > > Dim strSQL As String 'SQL statement
> > > > > Dim strOut As String 'Output string to
> > > > > concatenate to.
> > > > > Dim lngLen As Long 'Length of string.
> > > > > Dim bIsMultiValue As Boolean 'Flag if strField is
> > a
> > > > > multi-valued field.
> > > > >
> > > > > 'Initialize to Null
> > > > > ConcatRelated = Null
> > > > >
> > > > > 'Build SQL string, and get the records.
> > > > > strSQL = "SELECT " & strField & " FROM " & strTable
> > > > > If strWhere <> vbNullString Then
> > > > > strSQL = strSQL & " WHERE " & strWhere
> > > > > End If
> > > > > If strOrderBy <> vbNullString Then
> > > > > strSQL = strSQL & " ORDER BY " & strOrderBy
> > > > > End If
> > > > > Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
> > > > > 'Determine if the requested field is multi-valued (Type is above 100.)
> > > > > bIsMultiValue = (rs(0).Type > 100)
> > > > >
> > > > > 'Loop through the matching records
> > > > > Do While Not rs.EOF
> > > > > If bIsMultiValue Then
> > > > > 'For multi-valued field, loop through the values
> > > > > Set rsMV = rs(0).Value
> > > > > Do While Not rsMV.EOF
> > > > > If Not IsNull(rsMV(0)) Then
> > > > > strOut = strOut & rsMV(0) & strSeparator
> > > > > End If
> > > > > rsMV.MoveNext
> > > > > Loop
> > > > > Set rsMV = Nothing
> > > > > ElseIf Not IsNull(rs(0)) Then
> > > > > strOut = strOut & rs(0) & strSeparator
> > > > > End If
> > > > > rs.MoveNext
> > > > > Loop
> > > > > rs.Close
> > > > >
> > > > > 'Return the string without the trailing separator.
> > > > > lngLen = Len(strOut) - Len(strSeparator)
> > > > > If lngLen > 0 Then
> > > > > ConcatRelated = Left(strOut, lngLen)
> > > > > End If
> > > > >
> > > > > Exit_Handler:
> > > > > 'Clean up
> > > > > Set rsMV = Nothing
> > > > > Set rs = Nothing
> > > > > Exit Function
> > > > >
> > > > > Err_Handler:
> > > > > MsgBox "Error " & Err.Number & ": " & Err.Description, _
> > > > > vbExclamation, "ConcatRelated()"
> > > > > Resume Exit_Handler
> > > > > End Function
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > > This email (and all attachments) is for the sole use of the intended
> > recipient(s) and may contain privileged and/or proprietary information. Any
> > unauthorized review, use, disclosure or distribution is prohibited. If you are
> > not the intended recipient, please contact the sender by reply e-mail and
> > destroy all copies of the original message.
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> > This email (and all attachments) is for the sole use of the intended
> > recipient(s) and may contain privileged and/or proprietary information. Any
> > unauthorized review, use, disclosure or distribution is prohibited. If you are
> > not the intended recipient, please contact the sender by reply e-mail and
> > destroy all copies of the original message.
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> > This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
> >
>