user defined not defined

[shank]

I've had the below function from Duane working for years. Upgraded to MS
Access 2007 and it I get an error: Undefined function Concatenate and
sometimes another error: user-defined type not defined

Googling suggests I don't have the correct references. I have these:
Visual Basic For Applications
Microsoft Access 12.0 Library
OLE Automation
Microsoft DAO 3.6 Object Library

Am I missing something?
thanks

- - - - - - - - - - - - - - - - - - - - - - - -
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = "") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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

 

[ruralguy via AccessMonster.com]

Have you applied the Office 2007 SP1 and the HotFix?
http://support.microsoft.com/kb/960307

I've had the below function from Duane working for years. Upgraded to MS
Access 2007 and it I get an error: Undefined function Concatenate and
sometimes another error: user-defined type not defined

Googling suggests I don't have the correct references. I have these:
Visual Basic For Applications
Microsoft Access 12.0 Library
OLE Automation
Microsoft DAO 3.6 Object Library

Am I missing something?
thanks

- - - - - - - - - - - - - - - - - - - - - - - -
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = "") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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

 

[Albert D. Kallal]

I've had the below function from Duane working for years. Upgraded to MS
Access 2007 and it I get an error: Undefined function Concatenate and
sometimes another error: user-defined type not defined

Googling suggests I don't have the correct references. I have these:
Visual Basic For Applications
Microsoft Access 12.0 Library
OLE Automation
Microsoft DAO 3.6 Object Library

Am I missing something?
thanks

There is no ADO reference in the above. And, you should not be using the dao
3.6 reference.

Try creating a BLANK database..and look at the references...that is ALL you
should need if you using DAO only.

Are you using ADO, or DAO throughout your application?

In the above you incorrectly set dao, but in your code below you choosing
ADO?

So, it not only going to confuse ms-access, but just looking at your code as
posted, you have no ADO in the above, have inserted a DAO reference the
above, and then in your code your commented out the dao part of your code?

So, you have a lot of flip flopping here....

Your default references should be:

Visual Basic For Applications
Microsoft Access 12.0 Library
OLE Automation
Microsoft office 12.0 Access database engine Object.

Now, since your code is using ADO (and I assume you using ado throughout
your application then?).

That being the case, you need to add an ADO reference.

I would add:

Microsoft ActiveX Data Objects 2.5 Library.

If your preference in most of your code is ADO, then of course you need to
move the ADO "up" in the reference list above that of DAO (which is now
called ACE (access database engine Object).

So:

Visual Basic For Applications
Microsoft Access 12.0 Library
OLE Automation

Microsoft ActiveX Data Objects 2.5 Libary. <--- ADO
Microsoft office 12.0 Access database engine Object. <---DAO