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.
>
Tidak ada komentar:
Posting Komentar