MS Access module to convert table rows into comma delimeted values

Posted on 2009-02-16
Last Modified: 2012-05-06
I have been working with a module code created in Access 2000 to generate comma delimited values from multiples rows of a table.

The module has two routines:

1) Create a table called CustPurchItems, if it does not exist, with two columns CustomerID and ItemID.
2) Table "SalesRegWhsleOnly-Items" has the same 2 columns CustomerID and ItemID.  The code is supposed to group all similar records from CustomerID and concatenate their ItemID column values into a comma delimited text string.  The new concatenated data is then copied into the previously created table "CustPurchItems".

I have a simple form to execute the module code, but in Access 2007 it stalls and will not complete.  The database is in Access 2002/2003 format, but is being used in Access 2007.  Please let me know if there is something wrong with my coding.  
Option Compare Database

Option Explicit

Break -------

Public Function FixTable() As Boolean

On Error Resume Next

Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String

Dim strCustomerID As String, strItemID As String

Set db = CurrentDb()

Call RecreateTables(db)


sSQL = "SELECT CustomerID, ItemID FROM SalesRegWhsleOnly-Items " _

       & "ORDER BY CustomerID, ItemID ASC"

Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then


  strCustomerID = rst!CustomerID

  strItemID = rst!ItemID



  Do Until rst.EOF

    If strCustomerID = rst!CustomerID Then

      strItemID = strItemID & ", " & rst!ItemID


      sSQL = "INSERT INTO CustPurchItems (CustomerID, ItemID)  " _

           & "VALUES('" & strCustomerID & "','" & strItemID & "')"

      db.Execute sSQL

      strCustomerID = rst!CustomerID

      strItemID = rst!ItemID

    End If




  ' Insert Last Record

    sSQL = "INSERT INTO CustPurchItems (CustomerID, ItemID)  " _

         & "VALUES('" & strCustomerID & "','" & strItemID & "')"

    db.Execute sSQL

End If

Set rst = Nothing

Set db = Nothing

'DoCmd.OpenForm "frmOutput"

End Function

Break ---------------

Public Function RecreateTables(ByRef dbs As DAO.Database)

On Error Resume Next

Dim sSQL As String

' Delete Table, if exists

If DCount("*", "MsysObjects", "[Name]='CustPurchItems'") = 1 Then

    DoCmd.DeleteObject acTable, "CustPurchItems"

End If

' Create Temp Table

sSQL = "SELECT CustomerID, ItemID INTO CustPurchItems " _

       & "FROM SalesRegWhsleOnly-Items WHERE 1 = 0;"

dbs.Execute sSQL

End Function

Open in new window

Question by:csouthards
    LVL 48

    Accepted Solution

    Not much info. What is the error?

    Code looks OK, but it may not like (ByRef dbs As DAO.Database).
    In that function I would just use:

    Set dbs = CurrentDb()


    Author Comment

    Apparently, the problem was the name of my table.  I simply changed the name to "tblSales" rather than SalesRegWhsleOnly-Items and it worked.  Thanks for the assitance.
    LVL 48

    Expert Comment

    by:Gustav Brock
    Maybe you could have used [SalesRegWhsleOnly-Items] in the SQL.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    Invalid procedure call or argument 1 23
    Running Sum Issue 7 21
    MS Access question 11 39
    Confirm a record has been added MS Access 4 17
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now