Using VBA create a new table from an exiting table and 1

[JoeVegas]

Using VBA I need to create a new table and move data from a single field in an existing table to (10) separate fields in the new table.
Existing table: aaa
bbb
ccc
ddd
eee
fff
ggg
hhh
iii
jjj

New table: f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 (Fields)
aaa bbb ccc ddd eee fff ggg hhh iii jjj

How can I do this. TIA

 

[huggyboy]

Try this - no doubt will be needing something along these lines in near future myself so i created from scratch

you will have to look up the access sql create syntax yourself if anything more complicated that strings

Sub UpdateTest()

    Dim rstT1 As Recordset
    Dim rstT2 As Recordset
    Dim strSql As String
    Dim arrywork As Variant
    
    ' create the new table
    sqlStr = "CREATE TABLE TableNew(" _
        & "tNewDummyfield text , " _
        & "tNewfield1 text , " _
        & "tNewfield2 text , " _
        & "tNewfield3 text , " _
        & "tNewfield4 text );"
    DoCmd.RunSQL sqlStr
    
    ' open the recordset to the new table
    Set rstT2 = New Recordset
    strSql = "SELECT * FROM TableNew;"
    rstT2.Open (strSql), CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
    ' populate the recordset from the old table
    Set rstT1 = New Recordset
    strSql = "SELECT * FROM Table1;"
    rstT1.Open (strSql), CurrentProject.Connection

    ' loop around the rows from the old table
    Do Until rstT1.EOF
        ' populate an array from the field in the old table
        ' - assuming the column is called t1field1 & data delimited by 1 space ie aaa bbb ccc ddd
        ' - in your case might have to do some string processing to get rid of the spaces etc
        arrywork = Split(rstT1!t1field1, " ")
        ' create a new row in the new table recordset
        rstT2.AddNew
        ' loop around the array ...
        For intkount = 0 To UBound(arrywork)
            ' ... setting each new field
            '  - assuming fields to be populated start at 2nd field (fields start at index = 0)
            rstT2.Fields(intkount + 1) = arrywork(intkount)
        Next
        ' force the update to the table on the database
        rstT2.Update
        ' move to the new row in the old table
        rstT1.MoveNext
    Loop
    
    ' close the 2 recordsets
    rstT1.Close
    rstT2.Close
    
End Sub

 

[huggyboy]

Thanks for the star