By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,386 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

Insert Recordset Data

P: 76
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
Jul 19 '07 #1
Share this Question
Share on Google+
10 Replies


JKing
Expert 100+
P: 1,206
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?
Jul 19 '07 #2

P: 76
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
Jul 19 '07 #3

JKing
Expert 100+
P: 1,206
Ok and what data types are all the fields? Are they text or numbers or dates.
Is the UID going to be an autonumber?
Jul 19 '07 #4

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

Thanks again!
Jul 23 '07 #5

P: 65
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,...
Jul 23 '07 #6

P: 76
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?
Jul 23 '07 #7

JKing
Expert 100+
P: 1,206
Hi, again. I think what you want to do is create a string for your sql and then execute it within the for loop.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Dim rs0 As Recordset
  3. Dim ArraySplit() As String
  4. Dim n As Integer
  5. Set rs0 = CurrentDb.OpenRecordset("UserFile")
  6.  
  7. rs0.OpenRecordset
  8.  
  9. Do Until rs0.EOF
  10. ArraySplit = Split(rs0![GroupList], "~")
  11. For n = 0 To UBound(ArraySplit)
  12. strSQL = "INSERT INTO yourTableName VALUES ('" & rs0![User] & "','" & rs0![Name] & "','" & _
  13. rs0![Mgr] & "','" & ArraySplit(n) & "')"
  14. Docmd.RunSQL(strSQL)
  15. Next n
  16. rs0.MoveNext
  17. Loop
  18.  
  19. rs0.Close
  20.  
  21. End Sub
  22.  
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
Jul 23 '07 #8

P: 76
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
Jul 23 '07 #9

JKing
Expert 100+
P: 1,206
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.

Expand|Select|Wrap|Line Numbers
  1. Docmd.SetWarnings False
  2.  
  3. 'Start of your insert code here
  4. 'more insert code here
  5. 'and some more here
  6. 'End of your insert code
  7.  
  8. Docmd.SetWarnings True 
  9.  
Jul 23 '07 #10

ADezii
Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, rs0 As DAO.Recordset
  2. Dim ArraySplit() As String, rstNew As DAO.Recordset
  3. Dim n As Integer
  4.  
  5. Set MyDB = CurrentDb()
  6. Set rs0 = MyDB.OpenRecordset("UserFile", dbOpenSnapshot)
  7. Set rstNew = MyDB.OpenRecordset("tblNewTable", dbOpenDynaset)
  8.  
  9. Do Until rs0.EOF
  10.   ArraySplit = Split(rs0![Grouplist], "~")
  11.   For n = 0 To UBound(ArraySplit)
  12.       With rstNew
  13.         .AddNew
  14.            ![Grouplist] = ArraySplit(n)
  15.            ![User] = rs0![User]
  16.            ![Name] = rs0![Name]
  17.            ![Mgr] = rs0![Mgr]
  18.         .Update
  19.       End With
  20.   Next n
  21.   rs0.MoveNext
  22. Loop
  23.  
  24. rs0.Close
  25. rstNew.Close
Jul 23 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.