Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS Access module to convert table rows into comma delimeted values

Posted on 2009-02-16
3
Medium Priority
?
640 Views
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)
 
'Stop
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
  rst.MoveFirst
  strCustomerID = rst!CustomerID
  strItemID = rst!ItemID
  
  rst.MoveNext
  Do Until rst.EOF
    If strCustomerID = rst!CustomerID Then
      strItemID = strItemID & ", " & rst!ItemID
    Else
      sSQL = "INSERT INTO CustPurchItems (CustomerID, ItemID)  " _
           & "VALUES('" & strCustomerID & "','" & strItemID & "')"
      db.Execute sSQL
      strCustomerID = rst!CustomerID
      strItemID = rst!ItemID
    End If
    rst.MoveNext
  Loop
  
  ' 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

0
Comment
Question by:csouthards
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 375 total points
ID: 23656857
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()

/gustav
0
 

Author Comment

by:csouthards
ID: 23665328
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.
0
 
LVL 52

Expert Comment

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

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

572 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