vb中如何实行access中的dconcat函数的功能
想在vb中实行以下功能:表A:
身份证号 联系电话
4111111111111111 1111
4111111111111111 2222
4111111111111111 3333
用SQL语句将上述记录合并成一条记录,用“,”分隔
4111111111111111 1111,2222,3333
在access中可以实现
Public Function DCONCAT(sExpr As String, sDomain As String, Optional sCriteria1 As String, Optional sCriteria2 As String, Optional sDelimiter As String)
On Error GoTo ErrHandler
Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim sResult As String
sResult = ""
sSQL = "select " & sExpr & " from (" & sDomain & ")"
'If sCriteria <> "" Then
sSQL = sSQL & " where " & sCriteria1 & "'" & sCriteria2 & "'"
'End If
rs.Open sSQL, CurrentProject.Connection, 3, 1
Do While Not rs.EOF
If sResult <> "" Then
sResult = sResult & sDelimiter
End If
sResult = sResult & rs.Fields(0).Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
DCONCAT = sResult
Exit Function
ErrHandler:
If rs.State <> adStateClosed Then
rs.Close
End If
Set rs = Nothing
DCONCAT = Err.Number & " : " & Err.Description
End Function
DoCmd.RunSQL "select * into aaa1 from (select 货品代码,dconcat('单据号码','单据','货品代码=',货品代码,chr(10)) from 单据)"
以上代码在access中可行,3000多条记录,6秒钟执行完毕,但如果在vb中,因为ADO不承认dconcat函数,无法执行,又不想在工程中加载access对象,不知道怎么处理。
实在不行的话,如何在vb中调用access的宏?能写出代码的话就更感激了。