Insert Into Complex

[JPaulo]

Hi all;

My table in 100 fields and did not want to copy 100 times the INSERT INTO.
It aims to do for all 100 fields, with only one line of INSERT INTO ?

Private Sub MyButton_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
  
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("Select * from tblSample  ORDER BY ID ASC")
    
    Do Until rst.EOF
        CurrentDb.Execute "INSERT INTO [temp] (X,Y) VALUES ('" & rst.Fields(0).Name & "','" & rst.Fields(0) & "')"
        CurrentDb.Execute "INSERT INTO [temp] (X,Y) VALUES ('" & rst.Fields(1).Name & "','" & rst.Fields(1) & "')"
        CurrentDb.Execute "INSERT INTO [temp] (X,Y) VALUES ('" & rst.Fields(2).Name & "','" & rst.Fields(2) & "')"
        CurrentDb.Execute "INSERT INTO [temp] (X,Y) VALUES ('" & rst.Fields(3).Name & "','" & rst.Fields(3) & "')"
        CurrentDb.Execute "INSERT INTO [temp] (X,Y) VALUES ('" & rst.Fields(4).Name & "','" & rst.Fields(4) & "')"
        rst.MoveNext
  Loop
Set rst = Nothing
    MsgBox "Successfully added..."
End Sub

 

[Galaxiom]

Tables with 100 fields almost invariably suggests an inappropriate database structure.

 

[JPaulo]

My database is working very well and this code too, just not logical I create 100 lines INERT INTO.

Any idea ?

 

[DCrake]

In your super duper lightning fast system would it not be prudent to insert a loop in your Do Loop

For nIndex = 0 To 99        
      CurrentDb.Execute "INSERT INTO [temp] (X,Y) VALUES ('" & rst.Fields(nIndex).Name & "','" & rst.Fields(nIndex) & "')"
Next X

 

[vbaInet]

Looks like repetition across your columns, Galaxiom could be right there. Have no idea why you are implementing it that way. In any case, create a second loop inside your Do While statement.

 

[JPaulo]

Works a charm.

Thank you very much.

 

[JPaulo]

vbaInet fine friend ?

I'm reversing the table and then export to Excel, that is my intention with this code.

Now it works perfect.

Thank you very much.

 

[vbaInet]

I'm very well thanks. I hope you are too.

All the thanks goes to David for providing you with the code.

Glad to know it's working perfectly.

 

[JPaulo]

Thank you for all;

My final code:

Private Sub MyButton_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
  
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("Select * from tblSample  ORDER BY ID ASC")
    
Do While Not rst.EOF
    For nIndex = 0 To 99
      CurrentDb.Execute "INSERT INTO [temp] (X,Y) VALUES ('" & rst.Fields(nIndex).Name & "','" & rst.Fields(nIndex) & "')"
    Next
rst.MoveNext
    Loop
Set rst = Nothing
    MsgBox "Successfully added..."
End Sub

 

[vbaInet]

Excellent. Also, as a suggestion:

For nIndex = 0 To (rst.Fields.Count - 2)
It would be '2' if one of your fields is the ID and obviously minus 1 because your loop starts from 0. Just so you don't have to go back and change the code.

 

[JPaulo]

The imagination is superior to knowledge, congratulations and thanks.

 

[vbaInet]

The imagination is superior to knowledge, congratulations and thanks.

Imagination is fruitless without applying knowledge hehe!! Good luck.