VBA Split Append Field to Multiple Rows

[JMichaelM]

Please help me. I've spent so much time on this troubleshooting and been coding for a couple of years or so. I've coded HTML VBA and straight SQL VBA but got caught up on this one big time. Research is critical in this line of work is important I suppose. I know this is quite simple but tried everything and cant get it write in the SQL VBA portion. I need to be able to add all the rest of the fields in the source table/query to the target table. This could be 6 to as many as 50. So Ideally if the source table has field 4, field 5, field 6, field, 7, I need to be able to add it to the target table as well. The code splits one field into multiple rows from one field but that doesn't necessarily relate to the problem entirely. Thanks.

Here is the code:

Public Sub addToTable()
Dim rstObj As DAO.Recordset, dbObj As DAO.Database
Dim InsertSQL As String
Set dbObj = CurrentDb()
Set rstObj = dbObj.OpenRecordset("Test3 Query")
Do While Not rstObj.EOF
Dim memArr() As String
memArr = Split(rstObj.Fields("ID"), ",")
For i = 0 To UBound(memArr)
InsertSQL = "INSERT INTO Test3Table(ID, PO) VALUES(""" & rstObj.Fields("PO") & """, """ & memArr(i) & """)"
DoCmd.RunSQL (InsertSQL)
Next
rstObj.MoveNext
Loop
End Sub


File attached:

Module 6
Source Table:Test3
Target Table:Test3Table
Query Test 3 Query

 

[isladogs]

Its not completely obvious what you are trying to do or what you think the problem is

Several comments on what your code actually does

1. Why is the data in the source table like this?
This is a database, not a spreadsheet.
See SourceTable screenshot

2. Secondly, your code has no protection against repeatedly appending the data - see DestTable screenshot

3. The code is VERY SLOW and without turning off warning messages even slower as it loops through one record at a time

4. The append code adds records with one or more spaces before the data which will make it very difficult to use in queries etc. Trim the source data before appending

5. It also creates a final blank record which trimming would probably also fix

I would rethink the approach.
For example, you could import into a buffer table first as separate records, trim the spaces then just run a standard 'unmatched' append query with warnings off (or using CurrentDB.Execute)

 

[JMichaelM]

To answer your question, this is not the actual dataset though understand your concerns and probably just how I entered it.. I would eventually just turn off warnings. I need to know how to also append additional fields.

 

[moke123]

this works, i'm just not sure if its what you want.

Public Sub SplitField()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim vID As Variant
    Dim strInsert As String
    Dim i As Integer
    Dim vFix As String

    strSql = "select * from  Test3"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    If rs.BOF And rs.EOF Then
        GoTo MyExit
    End If

    Do Until rs.EOF
       
        vFix = Trim(rs!id)
        If Right(vFix, 1) = "," Then
            vFix = Left(vFix, Len(vFix) - 1)
        End If

        vID = Split(vFix, ",")
        For i = 0 To UBound(vID)
            strInsert = "Insert into Test3Table(PO,ID,City,State) values('" & rs!PO & "','" & Trim(vID(i)) & "','" & rs!city & "','" & rs!State & "')"
            db.Execute strInsert, dbFailOnError
        Next i

        rs.MoveNext
    Loop

MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

 

[isladogs]

If moke's code is what you want, that's great.

If not, suggest you upload a spreadsheet file with two worksheets.
1. Real or realistic data from the source table
2. What you want the destination table to look like with that data

Doing that will allow us all to understand what you are trying to achieve.

Also, just wondering if your source data comes from excel.
If it does, you could use the text to columns feature to parse the data before importing into access

 

[JMichaelM]

mook the field with multiple entries are suppose to split into rows

 

[JMichaelM]

how do I get id field separated into rowa

 

[isladogs]

Suggest you upload spreadsheet of your data as described in post 5

 

[moke123]

mook the field with multiple entries are suppose to split into rows

thats what the code i posted does.

it takes a record and splits the id field.
it then writes each of the id fields along with the PO,City and state field corresponding to that id to your target table.