Convert CombineChildRecord code from DAO to ADO?

[ChazD]

Hi All,

The article at

http://support.microsoft.com/kb/318642/EN-US/

contains DAO code to combine child records; however, 2007 uses ADO and I
don't know how to convert. I'd appreciate any assistance this community
could give. I've copied and pasted the code below for convenience.

Thanks in Advance,
ChazD

Function CombineChildRecords(strTblQryIn As String, _
strFieldNameIn As String, strLinkChildFieldNameIn As String, _
varPKVvalue As Variant, Optional strDelimiter) As Variant

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim varResult As Variant

Set db = CurrentDb
Set qd = db.CreateQueryDef("")

If IsMissing(strDelimiter) Then strDelimiter = "; "
strSQL = "SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]"
qd.SQL = strSQL & " WHERE [" & strLinkChildFieldNameIn & "] = [ParamIn]"
qd.Parameters("ParamIn").Value = varPKVvalue

Set rs = qd.OpenRecordset()

Do Until rs.EOF
varResult = varResult & rs.Fields(strFieldNameIn).Value & strDelimiter
rs.MoveNext
Loop

rs.Close

If Len(varResult) > 0 Then varResult = Left$(varResult, _
Len(varResult) - 2)

CombineChildRecords = varResult

Set rs = Nothing
Set qd = Nothing
Set db = Nothing
End Function

 

[Chris O'C via AccessMonster.com]

Access 2007 uses DAO by default. If your app doesn't have it, it means it
was upgraded to 2007 from an earlier version where only the ADO library was
used, or the original DAO library was removed and the ADO library added.

To add the DAO library, push Alt+F11 to open the code window. Menu Tools >
References. Scroll down to Microsoft DAO Object Library and mark the
checkbox.

If the VBA code isn't disambiguated between ADODB objects and DAO objects,
you'll run into trouble when you compile the code.

Chris
Microsoft MVP

Hi All,

The article at

http://support.microsoft.com/kb/318642/EN-US/

contains DAO code to combine child records; however, 2007 uses ADO and I
don't know how to convert. I'd appreciate any assistance this community
could give. I've copied and pasted the code below for convenience.

Thanks in Advance,
ChazD

Function CombineChildRecords(strTblQryIn As String, _
strFieldNameIn As String, strLinkChildFieldNameIn As String, _
varPKVvalue As Variant, Optional strDelimiter) As Variant

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim varResult As Variant

Set db = CurrentDb
Set qd = db.CreateQueryDef("")

If IsMissing(strDelimiter) Then strDelimiter = "; "
strSQL = "SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]"
qd.SQL = strSQL & " WHERE [" & strLinkChildFieldNameIn & "] = [ParamIn]"
qd.Parameters("ParamIn").Value = varPKVvalue

Set rs = qd.OpenRecordset()

Do Until rs.EOF
varResult = varResult & rs.Fields(strFieldNameIn).Value & strDelimiter
rs.MoveNext
Loop

rs.Close

If Len(varResult) > 0 Then varResult = Left$(varResult, _
Len(varResult) - 2)

CombineChildRecords = varResult

Set rs = Nothing
Set qd = Nothing
Set db = Nothing
End Function