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

append table with records in recordset via vba...

P: 5
Below is my code....I am simply connecting one database to another via VBA. I cannot figure out how to take the records out from the recordset and put them in a table. Me stumped...

'create connection to another database
Dim RConn As ADODB.Connection
Dim rRecordset As New ADODB.Recordset
Dim RString As String
RString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Dan\My Documents\db3.mdb;"
Set RConn = New ADODB.Connection
RConn.Open RString
rRecordset.Open "OriginalTable", RConn


If rRecordset.BOF And rRecordset.EOF Then
MsgBox "No Records"
Else
MsgBox "There are records"
End If

How do I take the data from the recordset and add it to a table? Thks in advance!
May 20 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,628
Below is my code....I am simply connecting one database to another via VBA. I cannot figure out how to take the records out from the recordset and put them in a table. Me stumped...

'create connection to another database
Dim RConn As ADODB.Connection
Dim rRecordset As New ADODB.Recordset
Dim RString As String
RString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Dan\My Documents\db3.mdb;"
Set RConn = New ADODB.Connection
RConn.Open RString
rRecordset.Open "OriginalTable", RConn


If rRecordset.BOF And rRecordset.EOF Then
MsgBox "No Records"
Else
MsgBox "There are records"
End If

How do I take the data from the recordset and add it to a table? Thks in advance!
Here is some sample code I created that will point you in the right direction. You need to create a 2nd Recordset based in your Internal Table, loop through all Records in the Original Recordset, then Append selective Fields to your Table. This code is for demo purposes, it works on my PC but it won't on yours. The code itself could be improved upon but I feel as though it may confuse matters (syntax wise).
Expand|Select|Wrap|Line Numbers
  1. 'Open up a 2nd Recordset on the Table on which you want to
  2. 'Append Data from the External Database
  3. Dim rRecordset_2 As ADODB.Recordset
  4. Set rRecordset_2 = New ADODB.Recordset
  5. With rRecordset_2
  6.   .Source = "tblTest"
  7.    Set .ActiveConnection = CurrentProject.Connection
  8.   .CursorType = adOpenKeyset
  9.   .LockType = adLockOptimistic
  10. End With
  11.  
  12. rRecordset_2.Open
  13.  
  14. 'Loop through Original Recordset and Append selective Fields to
  15. 'their counterparts in the internal Table
  16. Do While Not rRecordset.EOF
  17.   rRecordset_2.AddNew
  18.     rRecordset_2![LastName] = rRecordset![LastName]
  19.     rRecordset_2![FirstName] = rRecordset![FirstName]
  20.     rRecordset_2![Address] = rRecordset![Address]
  21.   rRecordset_2.Update
  22.     rRecordset.MoveNext
  23. Loop
  24.  
  25. rRecordset.Close
  26. rRecordset_2.Close
May 20 '07 #2

P: 5
Thanks! I really appreciate it!
May 20 '07 #3

ADezii
Expert 5K+
P: 8,628
Thanks! I really appreciate it!
No problem, any questions feel free to ask.
May 20 '07 #4

Post your reply

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