problem with a function in 2007 2

[simon551]

Hi, I have this function that I've been using to concatenate multiple records. It's been working fine in multiple databases for me. This is the first time I've tried to use it in an MS Access 2007 .accdb and it's spitting up. Of course there may be another problem...
Can you look at my code and see if you see any problem?
The error I get is "Compile Error: User-defined type not defined"

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ") _
            As String
    'example
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID, FirstName, DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    '    John, Mary, Susan
    'in a Query
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    '     WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '

    '======For DAO uncomment next 4 lines=======
    '======     comment out ADO below    =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

    '======For ADO uncomment next two lines=====
    '======     comment out DAO above     ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                    .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    '====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

when the error comes up Access highlights the phrase: rs As New ADODB.Recordset


Thanks!

 

[Remou]

You need to check your references. You will need Microsoft ActiveX Data Objects 2.1 Library.

 

[simon551]

Thanks, Remou. I did a google search for that and I can't find any obvious way to download that...

is that all I need to do-download a file somewhere? you said I need to check my references. I'm not sure how to do that either. I found the code for this function here:

http://www.tek-tips.com/faqs.cfm?fid=4233

 

[pbaldy]

In the VBA editor, go into Tools/References. As Remou said, you'll need the ADO library, and if you've left in the DAO stuff you'll need the DAO library. You should not have to download anything.

Paul
MS Access MVP 2007/2008

 

[simon551]

nice. It was just a matter of clicking the box next to Microsoft ActiveX Data Objects 2.1 Library.

Thank you both very much for the help!