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