Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

JoeVegas

Programmer
Oct 3, 2001
53
US
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
 
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

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top