MS access(2003) 有什么可与存储过程相媲美的.我想在 MS accessess 中运行一个复杂的查询 [英] Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
问题描述
我有一张桌子,叫它 TBL.它有两列,称为 A 和 B.现在在查询中,我需要一列作为 A,另一列应该是所有 B 的逗号分隔列表,这些列表与 TBL 中的 A 相对.例如TBL是这样的
I have a table, call it TBL. It has two columns,call them A and B. Now in the query I require one column as A and other column should be a comma seprated list of all B's which are against A in TBL. e.g. TBL is like this
1 个阿尔法
2 测试版
1 伽马
1 个增量
查询结果应该是
1 Alpha、Gamma、Delta
1 Alpha,Gamma,Delta
2 测试版
这种类型的事情很容易在存储过程中使用游标来完成.但是我不能通过 MS Access 来做到这一点,因为它显然不支持存储过程.有没有办法在 MS 访问中运行存储过程?或者有没有办法通过 SQL 来运行这种类型的查询
This type of thing is very easy to do with cursors in stored procedure. But I am not able to do it through MS Access, because apparently it does not support stored procedures. Is there a way to run stored procedure in MS access? or is there a way through SQL to run this type of query
推荐答案
您可以使用用户定义函数 (UDF) 连接记录.
You can concatenate the records with a User Defined Function (UDF).
以下代码可以按原样"粘贴到标准模块中.您示例的 SQL 将是:
The code below can be pasted 'as is' into a standard module. The SQL for you example would be:
SELECT tbl.A, Concatenate("SELECT B FROM tbl
WHERE A = " & [A]) AS ConcA
FROM tbl
GROUP BY tbl.A
此代码由 DHookom,Access MVP 提供,取自 http:///www.tek-tips.com/faqs.cfm?fid=4233
This code is by DHookom, Access MVP, and is taken from http://www.tek-tips.com/faqs.cfm?fid=4233
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
'(This SQL statement assumes FamID is numeric)
'===================================
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'===================================
'
'If the FamID is a string then the SQL would be
'===================================
'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
这篇关于MS access(2003) 有什么可与存储过程相媲美的.我想在 MS accessess 中运行一个复杂的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!