Insert Recordset Data

[artemetis]

Hey there....

I am using a recordset to pull some data out of a table and in turn dump that data into a new table.

My code is as follows and works fine, but I am looking for assistance in dumping this into the new table.

Any help would be greatly appreciated.

--Art


Private Sub Form_Load()
Dim rs0 As Recordset
Dim ArraySplit() As String
Dim n As Integer
Set rs0 = CurrentDb.OpenRecordset("UserFile")

rs0.OpenRecordset

Do Until rs0.EOF
ArraySplit = Split(rs0![GroupList], "~")
For n = 0 To UBound(ArraySplit)
Debug.Print rs0![User] & ";" & rs0![Name] & ";" & _
rs0![Mgr] & ";" & ArraySplit(n)
Next n
rs0.MoveNext
Loop

rs0.Close

End Sub

[JKing]

What will the table you're inserting into look like? In otherwords what fields are in the other table and what fields from the current recordset do you want to insert?

[artemetis]

Thanks for the reply.

The only fields I need in the new table are the ones in the recordset above + a UID.

GroupList
User
Name
Mgr

[JKing]

Ok and what data types are all the fields? Are they text or numbers or dates.
Is the UID going to be an autonumber?

[artemetis]

Sorry, I should've posted that 411.
The UID will be autonumber and the rest are plain old text.

Thanks again!

[abolos]

Hey there....

I am using a recordset to pull some data out of a table and in turn dump that data into a new table.

My code is as follows and works fine, but I am looking for assistance in dumping this into the new table.

Any help would be greatly appreciated.

--Art


Private Sub Form_Load()
Dim rs0 As Recordset
Dim ArraySplit() As String
Dim n As Integer
Set rs0 = CurrentDb.OpenRecordset("UserFile")

rs0.OpenRecordset

Do Until rs0.EOF
ArraySplit = Split(rs0![GroupList], "~")
For n = 0 To UBound(ArraySplit)
Debug.Print rs0![User] & ";" & rs0![Name] & ";" & _
rs0![Mgr] & ";" & ArraySplit(n)
Next n
rs0.MoveNext
Loop

rs0.Close

End Sub

The command you need is Insert:

form.recordset= insert into <table> [field1],[field2],... values val1,val2,...

[artemetis]

The command you need is Insert:

form.recordset= insert into <table> [field1],[field2],... values val1,val2,...

Not really sure I follow how to add that line of code?

[JKing]

Hi, again. I think what you want to do is create a string for your sql and then execute it within the for loop.

Private Sub Form_Load()
Dim rs0 As Recordset
Dim ArraySplit() As String
Dim n As Integer
Set rs0 = CurrentDb.OpenRecordset("UserFile")
 
rs0.OpenRecordset
 
Do Until rs0.EOF
ArraySplit = Split(rs0![GroupList], "~")
For n = 0 To UBound(ArraySplit)
strSQL = "INSERT INTO yourTableName VALUES ('" & rs0![User] & "','" & rs0![Name] & "','" & _
rs0![Mgr] & "','" & ArraySplit(n) & "')"
Docmd.RunSQL(strSQL)
Next n
rs0.MoveNext
Loop
 
rs0.Close
 
End Sub
 

Notes:
yourTableName - replace this with actual name of the table you want to add this data to

The values you are inserting should be in the same order as they appear in the table you are adding them to. In this case we are inserting in the order of User, Name, MGR, GroupList. So the table we insert to should have columns in the order of UID, User, Name, MGR, GroupList.

Notice we did not include UID as part of the insert. This is because it is an autonumber and generates itself for each new record added.

Give that a try and let me know how you make out with it. If you have any further questions just ask.

JKing

[artemetis]

Thanks so much!
This seems to work, but it is prompting me for the insert at each record.
Is there a way around that?
There are thousands of records.

Thanks!
--Art

[JKing]

Yes, there is a command for turning the warnings off though you must always be sure to turn them back on when your action is finished.

Docmd.SetWarnings False
 
'Start of your insert code here
'more insert code here
'and some more here
'End of your insert code
 
Docmd.SetWarnings True 
 

[ADezii]

Thanks so much!
This seems to work, but it is prompting me for the insert at each record.
Is there a way around that?
There are thousands of records.

Thanks!
--Art

This should work nicely for you with no Errors, just make a couple of personalized substitutions:

Dim MyDB As DAO.Database, rs0 As DAO.Recordset
Dim ArraySplit() As String, rstNew As DAO.Recordset
Dim n As Integer
 
Set MyDB = CurrentDb()
Set rs0 = MyDB.OpenRecordset("UserFile", dbOpenSnapshot)
Set rstNew = MyDB.OpenRecordset("tblNewTable", dbOpenDynaset)
 
Do Until rs0.EOF
  ArraySplit = Split(rs0![Grouplist], "~")
  For n = 0 To UBound(ArraySplit)
      With rstNew
        .AddNew
           ![Grouplist] = ArraySplit(n)
           ![User] = rs0![User]
           ![Name] = rs0![Name]
           ![Mgr] = rs0![Mgr]
        .Update
      End With
  Next n
  rs0.MoveNext
Loop
 
rs0.Close
rstNew.Close