Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Create delimited data in access table

Discussion in 'Information Technology' started by Inquisitive1, Nov 17, 2005.

  1. Inquisitive1

    Inquisitive1
    Expand Collapse
    Guest

    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!
     
  2. Loading...


  3. schasteen

    schasteen
    Expand Collapse
    Guest

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

    "Inquisitive1" wrote:

    > 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!
    >
    >
     
  4. Inquisitive1

    Inquisitive1
    Expand Collapse
    Guest

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

    Thanks!
     
  5. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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.

    --
    Brendan Reynolds

    "Inquisitive1" <joseph.allison@delphi.com> wrote in message
    news:1132150020.534767.38650@g47g2000cwa.googlegroups.com...
    >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!
    >
     
  6. Inquisitive1

    Inquisitive1
    Expand Collapse
    Guest

    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.
     

Share This Page