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

One more thing...

I have lots of queries that use 15 - 20 tables. Those queries are sent as one JET SQL statement...one connection.

Bill

--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> Hi John,
>
> I am referring to connections the way that Access counts them -- not real connections ;)
>
> Warm Regards,
> Crystal
>
> *
> (: have an awesome day :)
> *
>
>
> ________________________________
> From: John Viescas <JohnV@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Wednesday, August 15, 2012 2:22 AM
> Subject: RE: [MS_AccessPros] Decreasing potential of "too many databases"
>
> Crystal-
>
> I don't disagree with you that RI can cause extra connections, but you said:
>
> " my understanding is that each linked tables DOES count as at least one
> connection -- more if there is RI "
>
> My point is each linked table does not, in my opinion, cause extra connections.
> That is, # of defined linked tables or even # of open linked tables <> number of
> connections. Access shares existing connections when it can.
>
> 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 Crystal
> Sent: Wednesday, August 15, 2012 9:42 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Decreasing potential of "too many databases"
>
> Hi John,
>
> agree on what? That RI means more connections? I know this is true. A database
> connection in the Access count is not what we think of in human terms.
>
> Warm Regards,
> Crystal
>
> *
> (: have an awesome day :)
> *
>
>
>
> ________________________________
> From: John Viescas <JohnV@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Tuesday, August 14, 2012 11:53 PM
> Subject: RE: [MS_AccessPros] Decreasing potential of "too many databases"
>
> I'm not sure I agree with Crystal. One of the optimizations you can do for a
> client-server app using linked tables is to open a Recordset on one of the
> tables when the app starts up and leave it open. The reason this works is
> Access builds a connection only when needed and destroys the connection when all
> usage is finished. The overhead to build / destroy a connection is fairly high,
> but if you have a connection open already, Access will share it for any further
> use. Without the connection open and if you have an app that tends to open only
> one form at a time (say, from a Switchboard), the overhead to rebuild a
> connection each time a form opens really slows things down. By leaving a
> connection open, it can be shared and doesn't have to be rebuilt.
>
> 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 John Marshall
> Sent: Wednesday, August 15, 2012 3:43 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Decreasing potential of "too many databases"
>
> So my brain does not always connect to my fingers. ;-)
>
>
>
> John.
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Stuart Luckman
> Sent: Tuesday, August 14, 2012 9:23 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Decreasing potential of "too many databases"
>
>
>
>
>
>
>
> Could even be Referential Integrity ....
>
> Cheers,
>
> Stuart
>
> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> Marshall
> Sent: Wednesday, 15 August 2012 9:17 AM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Decreasing potential of "too many databases"
>
> Other than Real Idiot, it could stand for Relational Integrity.
>
> John... Visio MVP
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Steve
> Conklin
> Sent: Tuesday, August 14, 2012 7:05 PM
> To: MS Access Pros List
> Subject: RE: [MS_AccessPros] Decreasing potential of "too many databases"
>
> Crystal, I probably will smack my head and say Doh! but what is RI? Steve
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> From: strive4peace2008@... <mailto:strive4peace2008%40yahoo.com>
> <mailto:strive4peace2008%40yahoo.com>
> Date: Tue, 14 Aug 2012 14:24:59 -0700
> Subject: Re: [MS_AccessPros] Decreasing potential of "too many databases"
>
> Hi Bill,
>
> my understanding is that each linked tables DOES count as at least one
> connection -- more if there is RI
>
> Warm Regards,
>
> Crystal
>
> http://www.YouTube.com/LearnAccessByCrystal
>
> *
>
> (: have an awesome day :)
>
> *
>
> ________________________________
>
> From: Bill Mosca <wrmosca@... <mailto:wrmosca%40comcast.net>
> <mailto:wrmosca%40comcast.net> >
>
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>
> Sent: Tuesday, August 14, 2012 12:44 PM
>
> Subject: Re: [MS_AccessPros] Decreasing potential of "too many databases"
>
> Clive
>
> I don't buy the idea that each linked table is a connection. It's really the
> number of connctions that counts. Unless you are retrieving rows or running
> DML ('action') queries, no connection is made. You can see that with the
> creation and destruction of the locking file.
>
> If I remember right Windows XP can only handle 10 connections per user with
> a split DB. And I have applications that link to over 100 tables.
>
> Regards,
>
> Bill Mosca, Founder - MS_Access_Professionals
>
> http://www.thatlldoit.com
>
> Microsoft Office Access MVP
>
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-6427073088
> 1E
>
> My nothing-to-do-with-Access blog
>
> http://wrmosca.wordpress.com
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "Clive" <zctek@>
> wrote:
>
> >
>
> > 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-er
> ror
>
> >
>
> > Regards, Clive.
>
> >
>
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> Viescas
>
> > > > Sent: Monday, August 13, 2012 1:35 PM
>
> > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Liz
> Ravenwood
>
> > > > Sent: Monday, August 13, 2012 10:23 PM
>
> > > > To: 'MS_Access_Professionals@yahoogroups.com
> <mailto:%27MS_Access_Professionals%40yahoogroups.com>
> <mailto:%27MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> Mosca
>
> > > > Sent: Monday, August 13, 2012 1:16 PM
>
> > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> Viescas
>
> > > > > Sent: Monday, August 13, 2012 12:49 PM
>
> > > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> mrsgoudge
>
> > > > > Sent: Monday, August 13, 2012 9:43 PM
>
> > > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> mrsgoudge
>
> > > > > > Sent: Monday, August 13, 2012 9:14 PM
>
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
>
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> mrsgoudge
>
> > > > > > > Sent: Monday, August 13, 2012 8:03 PM
>
> > > > > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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.
>
> > > >
>
> > >
>
> >
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
> [Non-text portions of this message have been removed]
>