Create delimited data in access table

[Inquisitive1]

A co-worker sent me this code to make comma delimited data in a table.
For example:

Change this:

Store# Item
1 Banana
1 Grapes
4 Apple
7 Pineapple
7 Watermelon

To:

Store# Item
1 Banana, Grapes
4 Apple
7 Pineapple, Watermelon

In some databases the code works, but in many it will not (I change the
variable names and file name). When I use the step into on the debug
menu, I notice that the table does not get created after running the
SQL code sSQL = "CREATE TABLE DelimitedCommCode (Duns Integer, CommCode
Text(10))".

Public Function UniqueCommCode() As Boolean
On Error Resume Next

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

Set db = CurrentDb()

' Delete Table, if exists
If DCount("*", "MsysObjects", "[Name]='DelimitedCommCode'") = 1 Then
DoCmd.DeleteObject acTable, "DelimitedCommCode"
End If

' Create the Delimited Define Variables and Length
sSQL = "CREATE TABLE DelimitedCommCode (Duns Integer, CommCode
Text(10))"
db.Execute sSQL

' Open the table to work with it
sSQL = "SELECT Duns, Comm FROM tbl_ContractCommoditiesNumeric " _
& "ORDER BY Duns, Comm ASC"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
strColA = rst!Duns
strColB = rst!Comm

rst.MoveNext
Do Until rst.EOF
If strColA = rst!Duns Then
strColB = strColB & ", " & rst!Comm
Else
sSQL = "INSERT INTO DelimitedCommCode (Duns, Comm) " _
& "VALUES('" & strColA & "','" & strColB & "')"
db.Execute sSQL
strColA = rst!Duns
strColB = rst!Comm
End If
rst.MoveNext
DoEvents
Loop

' Insert Last Record
sSQL = "INSERT INTO DelimitedCommCode (Duns, Comm) " _
& "VALUES('" & strColA & "','" & strColB & "')"
db.Execute sSQL
End If

Set rst = Nothing
Set db = Nothing

End Function


Does anybody see a problem with the code or have an idea why this
results in no data in the table created?

Thanks!

 

[Guest]

Why would you want comma delimited data in your table? This defeats the
purpose of a relational database.

 

[Inquisitive1]

There are business reasons for making a table comma delimited. Can
anybody advise?

Thanks!

 

[Brendan Reynolds]

The 'On Error Resume Next' is hiding from you the cause of - and therefore
the solution to - your problem. Comment out that line and you'll likely get
an error message when you try to run the code in the problem databases. It
may also help to change the line ...

db.Execute sSQL

.... to ....

db.Execute sSQL, dbFailOnError

If you're still unsure what to do at that stage, post back with the details
of the error message, and someone will probably be able to help you.

BTW: One obvious limitation of this code is that it will only work in an
MDB, it will not work in an ADP.

 

[Inquisitive1]

Brendan,

THANKS!!! That did the trick. I was able to track the lines causing
problems and the code is now working.

BTW: I seem to only work with MDB, not ADP.