Stuart Sanders
lists at bitshk.com
Wed Dec 7 08:58:55 CST 2005
Actually Stuart doesn't use ADO in Access and never has. Even now I stick
with DAO. In a pure Jet (ie Access) environment I've never really found
ADO to offer anything I need that DAO already doesn't already have. Plus
DAO allows you to do more to the actual database and is from what I've
read (never really done a personal comparison) is faster as it doesn't add
an extra layer.
It was merely my suggestion to him as he may already have code that
requires the ADO reference. So keeping the ADO reference higher means the
only changes needed were those I (and others) listed. Since there are
coding and keyword differences between the two, it allows him to run DAO
code as and when he needs.
Stuart
-----Original Message-----
From: "William Hindman" <wdhindman at bellsouth.net>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Date: Wed, 7 Dec 2005 08:06:57 -0500
Subject: Re: [AccessD] Concatenating data into lists
> ...just a couple of further notes ...AccessXP defaults to ADO, Dev's
> module
> is written in DAO ...I try not to mix the environments simply because
> it
> makes code maintenance a pita ...if I'm using Jet I use DAO because,
> ime,
> its much faster and frankly, I know it a lot better ...and MS has
> essentialy
> abandoned ADO so I'm not investing any more time than necessary in it
> ...but
> if I'm using anything but Jet as my be, then ADO is probably the way to
> go
> ...the point being that while you can mix the environments, you then
> have to
> set both references and remember which you've defaulted to (placed
> first in
> the ref list) ...Stuart places the ADO reference above the DAO and I do
> just
> the opposite ...it all depends on your local needs as to which works
> best or
> you ...in your case I suspect that if you place the DAO 3.6 ref above
> the
> ADO ref, Dev's code will play fine without any further changes ...but
> it
> "might" screw up other code in your mdb ...and it might not if you've
> learned to ignore most MS program samples and fanatically define your
> dbs as
> ADO or DAO ...just my 2 cents, I'm sure others will demur :)
>
> William
>
> ----- Original Message -----
> From: "David & Joanne Gould" <dajomigo at tpg.com.au>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Wednesday, December 07, 2005 6:58 AM
> Subject: Re: [AccessD] Concatenating data into lists
>
>
> > Thank you both for your suggestions. I will try them properly
> tomorrow
> > after I have had som sleep.
> >
> > David
> >
> > At 10:17 PM 7/12/2005, Gustav Brock wrote:
> >>Hi David
> >>
> >>Or adjust this variation with a saved query which I posted recently:
> >>
> >>Here's a variation where you save the SQL to look up the
> Qualifications in
> >>a separate query assuming that your primary key, VolNum, is a number:
> >>
> >> PARAMETERS lngKey Long;
> >> SELECT
> >> QualificationCode
> >> FROM
> >> tblQualifications
> >> WHERE
> >> VolNum = lngKey
> >> ORDER BY
> >> QualificationCode;
> >>
> >>Save this as, say, qdyQualifications.
> >>
> >>Now, adjust you main query to include the function below to look
> something
> >>like this:
> >>
> >> SELECT
> >> *,
> >>
> >>
> ConcatenateRecords('qdyQualifications',[VolNum],'QualificationCode',',
> ')
> >> FROM
> >> tblVolunteers;
> >>
> >>/gustav
> >>
> >><code>
> >>
> >>Public Function ConcatenateRecords( _
> >> ByVal strSource As String, _
> >> ByVal lngKey As Long, _
> >> ByVal strField As String, _
> >> Optional ByVal strSeparator As String = ";") _
> >> As String
> >>
> >>' Concatenates values from one field (strField) from all
> >>' records in query strSource using parameter value lngKey.
> >>' Values are separated by strSeparator.
> >>' Default output like:
> >>' 34;56;34;67;234
> >>'
> >>' 1999-10-12. Cactus Data ApS, CPH
> >>
> >> Dim dbs As DAO.Database
> >> Dim qdf As DAO.QueryDef
> >> Dim rst As DAO.Recordset
> >> Dim fld As DAO.Field
> >>
> >> Dim booPluralis As Boolean
> >> Dim strFields As String
> >>
> >> On Error GoTo Err_ConcatenateRecords
> >>
> >> Set dbs = CurrentDb()
> >>
> >> If Len(strSource) > 0 And Len(strField) > 0 Then
> >> Set qdf = dbs.QueryDefs(strSource)
> >> qdf.Parameters(0) = lngKey
> >> Set rst = qdf.OpenRecordset()
> >> Set fld = rst.Fields(strField)
> >>
> >> With rst
> >> While Not .EOF
> >> If booPluralis = True Then
> >> ' There is more than one record.
> >> ' Add separator.
> >> strFields = strFields & strSeparator
> >> End If
> >> strFields = strFields & Trim(fld.value)
> >> booPluralis = True
> >> .MoveNext
> >> Wend
> >> .Close
> >> End With
> >>
> >> Set fld = Nothing
> >> Set rst = Nothing
> >> Set qdf = Nothing
> >> End If
> >>
> >> Set dbs = Nothing
> >>
> >> ConcatenateRecords = strFields
> >>
> >>Exit_ConcatenateRecords:
> >> Exit Function
> >>
> >>Err_ConcatenateRecords:
> >> MsgBox "Error " & Err.Number & ". " & Err.Description
> >> Resume Exit_ConcatenateRecords
> >>
> >>End Function
> >>
> >></code>
> >>
> >>
> >>--
> >>AccessD mailing list
> >>AccessD at databaseadvisors.com
> >>http://databaseadvisors.com/mailman/listinfo/accessd
> >>Website: http://www.databaseadvisors.com
> >
> > At 10:27 PM 7/12/2005, Stuart Sanders wrote:
> >
> >>The code was likely written using Miscrosoft DAO rather than ADO
> which is
> >>set by default in Access 2003.
> >>
> >>If you feel like the exercise you can probably convert the code to
> ADO,
> >>but since I haven't looked at it I don't know how much other ado
> specific
> >>code it contains.
> >>
> >>For a simple solution, the easiest thing would be to add a reference
> to
> >>DAO and change the declares for database and recordset to
> DAO.Database and
> >>DAO.recordset.
> >>
> >>To change the reference, from a code window, click on the tools menu
> and
> >>then references...
> >>
> >>The one you are looking for is called the Microsoft DAO x.x Object
> >>Library. In my case its 3.6, but I'm not using access 2003.
> >>
> >>Make sure the reference stays below the Microsoft ActiveX Data
> Objects
> >>library so that your existing code doesn't need to change.
> >>
> >>Stuart
> >>
> >>
> >>-----Original Message-----
> >>From: David & Joanne Gould <dajomigo at tpg.com.au>
> >>To: Access Developers discussion and problem solving
> >><accessd at databaseadvisors.com>
> >>Date: Wed, 07 Dec 2005 22:05:08 +1100
> >>Subject: [AccessD] Concatenating data into lists
> >>
> >> > I have been trying to learn how to use Dev Ashish's wonderful
> module to
> >> > turn data from a child table into a comma delimitered list. It
> works
> >> > perfectly every time I use Northwind to test it and even works if
> I
> >> > import
> >> > tables and queries into Northwind from another database. But I
> can't
> >> > get it
> >> > to work in any other database. One line in the code has me
> thinking
> >> > (Set db
> >> > as Database - I am using Access XP and it doesn't recognise this
> code.
> >> > But
> >> > I just comment it out and Northwind is happy.
> >> >
> >> > I don't get any error messages, it just doesn't put anything into
> the
> >> > query
> >> > field. Anyone who wants to see an example of what I have been
> working
> >> > on, I
> >> > can send a copy of the test database (only about 20kb zipped)
> >> >
> >> > TIA
> >> >
> >> > David Gould
> >> > DG Solutions
> >> >
> >> >
> >> > --
> >> > AccessD mailing list
> >> > AccessD at databaseadvisors.com
> >> > http://databaseadvisors.com/mailman/listinfo/accessd
> >> > Website: http://www.databaseadvisors.com
> >>
> >>
> >>--
> >>AccessD mailing list
> >>AccessD at databaseadvisors.com
> >>http://databaseadvisors.com/mailman/listinfo/accessd
> >>Website: http://www.databaseadvisors.com
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com