ACCESS FEED: 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.