transformation code, simple, i am just too new to this

[SamDeMan]

Hi

i need a simple code. i am new to vba and ado. i would like to transform a table. i import a table with two columns. column1 has line1, line2, line3, and item#. then a blank row. then the sequence starts again. so for each item# i have three line rows, one item# row and an empty row. i want to organize this. i think if i create a table with column1.name line1, column2 line2, column3 line3, and column4 item# this will organize my item# one item per row.

to this i need two while loops, one that will take the copy the rows out of the table_import, and then an outer loop that will enter it into table_Data.

can anybody help me with the code.

thanks,

sam

 

[ghudson]

Tables do not store rows, tables store records.

Create a primary key or a required field if you do not want the table to hold empty records.

The order of the records in meaningless in a table. Use a query to sort your data for display in a form or report.

 

[Bat17]

If I get you right, I would link to the text file tempoarily and work from there.

This code is in DAO which is what I understand Depending on the verion of Access you have you may need to set a reference to it.

Sub test()
Dim strSQL As String
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim strF1 As String
Dim strF2 As String
Dim strF3 As String
Dim strF4 As String
On Error Resume Next ' to prevent error where no last blank row exist
strSQL = "Select * From testdata" ' name of linked table
Set rst = CurrentDb.OpenRecordset("tblMyData") ' your data table name here
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF
    strF1 = rs!Field1
    rs.MoveNext
    strF2 = rs!Field1
    rs.MoveNext
    strF3 = rs!Field1
    rs.MoveNext
    strF4 = rs!Field1
    rs.MoveNext
    rs.MoveNext
    rst.AddNew
    rst![line1] = strF1
    rst![line2] = strF2
    rst![line3] = strF3
    rst![ItemNumber] = strF4
    rst.update
Loop
Set rst = Nothing
Set rs = Nothing
End Sub

HTH

Peter

 

[SamDeMan]

hi peter,

thanks a lot for your answer. i didn't get a chance to test it, but basically i got your script until the while statement b4 i posted my question. why i couldn't figure out was the movenext and the addnew parts. your reply seems to have answered exactly what i needed.

thanks again, i hope to get this ado thing going, so that i can contribute to these groups on the topic.

sam