Results 1 to 3 of 3
  1. #1
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    Splitting the records

    I inherited database with a code which suppose to get the values from OrigDB table and split and copy records into OutDB based on “/” character. What the code does it copy only the first record and ignores the rest. For example, column “Line ID” in OriDB has record 10PH-1000-A/B/C, after the splitting I am expecting to see in OutDB table
    10PH-1000-A
    10PH-1000-B
    10PH-1000-C
    But I am only getting 10PH-1000-A.


    Wonder if somebody can help me with this as the code is way above my vba skills. Please see attached database and the code.
    Thanks
    Code:
    Private Sub btnSplit_Click()
    Dim dbCurr As DAO.Database
    Dim rsOldData As DAO.Recordset
    Dim str2 As String
    Dim i As Integer
    Dim strsql As String
    Dim varLN As Variant
    strsql = "SELECT * FROM OriDB"
    Set dbCurr = CurrentDb()
    Set rsOldData = dbCurr.OpenRecordSet(strsql)
    Do While rsOldData.EOF = False
        str2 = rsOldData![Line ID]
        
        If InStr(str2, "/") > 0 Then
            varLN = Split(str2, "/")
            For i = 1 To UBound(varLN)
                varLN(i) = Left(varLN(0), Len(varLN(0)) - 1) + varLN(i)
            Next
            For i = 0 To UBound(varLN)
                Dim strSQL2 As String
                Dim strSQL4 As String
                strSQL2 = "INSERT INTO OutDB SELECT * FROM OriDB WHERE [Line ID] = '" & str2 & " ';"
                dbCurr.Execute strSQL2
                strSQL4 = "UPDATE OutDB SET [Line ID] = '" & varLN(i) & "' WHERE [Line ID] = '" & str2 & " ';"
                dbCurr.Execute strSQL4
            Next
        Else
            Dim strsql3 As String
            strsql3 = "INSERT INTO OutDb SELECT * FROM OriDB WHERE [Line ID] = '" & str2 & " ';"
            dbCurr.Execute strsql3
        End If
        rsOldData.MoveNext
    Loop
    rsOldData.Close
    Set rsOldData = Nothing
    Set dbCurr = Nothing
    End Sub
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Here is a version that does not use array. Also, don't include the autonumber primary key in the INSERT action. If you need this PK as FK in OutDB, create a field to receive it.

    Code:
    Private Sub btnSplit_Click()
    Dim rsOldData As DAO.Recordset
    Dim strPre As String, Dim strLtrs As String, str2 As String
    Dim i As Integer
    Set rsOldData = CurrentDb.OpenRecordSet("SELECT * FROM OriDB")
    Do While rsOldData.EOF = False
        str2 = rsOldData![Line ID]
        strPre = Left(str2, InStrRev(str2, "-"))
        strLtrs = Replace(Mid(str2, InStrRev(str2, "-") + 1), "/", "")
        For i = 1 To Len(strLtrs)
            CurrentDb.Execute "INSERT INTO OutDB([Line ID], [Line Type], [Maintain Temp]) " & _
                              " VALUES('" & strPre & Mid(strLtrs, i, 1) & "', '" & rsOldData![Line Type] & "', " & rsOldData![Maintain Temp] & ")"
        Next
        rsOldData.MoveNext
    Loop
    
    rsOldData.Close
    Set rsOldData = Nothing
    End Sub
    Code assumes every record in OriDB has a value in [Line ID] with appropriate structure.

    Recommend no spaces nor punctuation/special characters (underscore only exception) in names.
    Last edited by June7; 07-19-2018 at 05:55 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This is my take. You are trying to write to output ALL fields from the source into the output table. This includes ID, which you have in the output table as a primary key, thus it can't be duplicated. The reason you don't know about this failure is because you don't use dbFailOnError parameter when using .Execute method, thus it fails silently. Suggest you read up on that and add it. However you will also need to either remove the PK from the target table, or perhaps better, don't grab all of the fields (modify your sql to not include the ID field from the source).
    EDIT
    late to the party again. I see that the revised code dropped the ID field from the append. Looks nice.
    Last edited by Micron; 07-19-2018 at 01:20 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Regarding splitting the DB
    By Kundan in forum Access
    Replies: 2
    Last Post: 07-09-2018, 05:47 AM
  2. Splitting Records in Continuous Form
    By William McKinley in forum Forms
    Replies: 4
    Last Post: 01-01-2015, 12:16 PM
  3. Replies: 6
    Last Post: 11-04-2014, 10:56 PM
  4. splitting db
    By slimjen in forum Database Design
    Replies: 1
    Last Post: 10-09-2013, 07:22 AM
  5. Splitting one record into multiple records
    By AccessUser123 in forum Programming
    Replies: 4
    Last Post: 06-15-2011, 08:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums