Does MS access(2003) have anything comparable to S

问题:

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 Alpha

2 Beta

1 Gamma

1 Delta

Result of query should be

1 Alpha,Gamma,Delta

2 Beta

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

回答1:

You can concatenate the records with a User Defined Function (UDF).

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

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 

回答2:

I believe you can create VBA functions and use them in your access queries. That might help you.

回答3:

There is not a way that I know of to run stored procedures in an Access database. However, Access can execute stored procedures if it is being used against a SQL backend. If you can not split the UI to Access and data to SQL, then your best bet will probably be to code a VBA module to give you the output you need.

回答4:

To accomplish your task you will need to use code. One solution, using more meaningful names, is as follows:

Main table with two applicable columns:

Table Name: Widgets

Field 1: ID (Long)

Field 2: Color (Text 32)

Add table with two columns:

Table Name: ColorListByWidget

Field 1: ID (Long)

Field 2: ColorList (Text 255)

Add the following code to a module and call as needed to update the ColorListByWidget table:

Public Sub GenerateColorList()

Dim cn As New ADODB.Connection
Dim Widgets As New ADODB.Recordset
Dim ColorListByWidget As New ADODB.Recordset
Dim ColorList As String

Set cn = CurrentProject.Connection

cn.Execute "DELETE * FROM ColorListByWidget"
cn.Execute "INSERT INTO ColorListByWidget (ID) SELECT ID FROM Widgets GROUP BY ID"

With ColorListByWidget
   .Open "ColorListByWidget", cn, adOpenForwardOnly, adLockOptimistic, adCmdTable
   If Not (.BOF And .EOF) Then
      .MoveFirst
      Do Until .EOF
         Widgets.Open "SELECT Color FROM Widgets WHERE ID = " & .Fields("ID"), cn
         If Not (.BOF And .EOF) Then
            Widgets.MoveFirst
            ColorList = ""
            Do Until Widgets.EOF
               ColorList = ColorList & Widgets.Fields("Color").Value & ", "
               Widgets.MoveNext
            Loop
         End If
         .Fields("ColorList") = Left$(ColorList, Len(ColorList) - 2)
         .MoveNext
         Widgets.Close
      Loop
   End If
End With


End Sub

The ColorListByWidget Table now contains your desired information. Be careful that the list (colors in this example) does not exceed 255 characters.

回答5:

No stored procedures, no temporary tables.

If you needed to return the query as a recordset, you could use a disconnected recordset.

回答6:

Perhaps instead of asking if Jet has stored procedures, you should explain what you want to accomplish and then we can explain how to do it with Jet (it's not clear if you're using Access for your application, or just using a Jet MDB as your data store).

回答7:

Well, you can use a Recordset object to loop through your query in VBA, concatenating field values based on whatever criteria you need.

If you want to return the results as strings, you'll be fine. If you want to return them as a query, that will be more complicated. You might have to create a temporary table and store the results in there so you can return them as a table or query.

回答8:

You can use GetString in VBA which will return the recordset separated by any value you like (e.g. comma, dash, table cells etc.) although I have to admit I've only used it in VBScript, not Visual Basic. W3Schools has a good tutorial which will hopefully lend itself to your needs.

回答9:

You can write your stored procedure as text and send it against the database with:

Dim sp as string
sp = "your stored procedure here" (you can load it from a text file or a memo field?)

Access.CurrentProject.AccessConnection.Execute sp

This supposes you are using ADODB objects (ActiveX data Objects Library is coorectly referenced in your app).

I am sure there is something similar with DAO ...

回答10:

@Remou on DHookom's Concatenate function: neither the SQL standard nor the Jet has a CONCATENATE() set function. Simply put, this is because it is a violation of 1NF. I'd prefer to do this on the application side rather than try to force SQL to do something it wasn't designed to do. Perhaps ACE's (Access2007) multi-valued types is a better fit: still NFNF but at least there is engine-level support. Remember, the question relates to a stored object: how would a user query a non-scalar column using SQL...?

@David W. Fenton on whether Jet has stored procedures: didn't you and I discuss this in the newsgroups a couple of years ago. Since version 4.0, Jet/ACE has supported the following syntax in ANSI-92 Query Mode:

CREATE PROCEDURE procedure (param1 datatype[, param2 datatype][, ...]) AS sqlstatement;

EXECUTE procedure [param1[, param2[, ...]];

So Jet is creating and executing something it knows (in one mode at least) as a 'procedure' that is 'stored' in the MDB file. However, Jet/ACE SQL is pure and simple: it has no control-of-flow syntax and a PROCEDURE can only contain one SQL statement, so any procedural code is out of the question. Therefore, the answer to whether Jet has stored procedures is subjective.