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