append table with records in recordset via vba...

[VBANovice]

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.Connectio n
Dim rRecordset As New ADODB.Recordset
Dim RString As String
RString = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\Docum ents and Settings\Dan\My Documents\db3.m db;"
Set RConn = New ADODB.Connectio n
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!

[ADezii]

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.Connectio n
Dim rRecordset As New ADODB.Recordset
Dim RString As String
RString = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\Docum ents and Settings\Dan\My Documents\db3.m db;"
Set RConn = New ADODB.Connectio n
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).
[CODE=vb]'Open up a 2nd Recordset on the Table on which you want to
'Append Data from the External Database
Dim rRecordset_2 As ADODB.Recordset
Set rRecordset_2 = New ADODB.Recordset
With rRecordset_2
.Source = "tblTest"
Set .ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimisti c
End With

rRecordset_2.Op en

'Loop through Original Recordset and Append selective Fields to
'their counterparts in the internal Table
Do While Not rRecordset.EOF
rRecordset_2.Ad dNew
rRecordset_2![LastName] = rRecordset![LastName]
rRecordset_2![FirstName] = rRecordset![FirstName]
rRecordset_2![Address] = rRecordset![Address]
rRecordset_2.Up date
rRecordset.Move Next
Loop

rRecordset.Clos e
rRecordset_2.Cl ose[/CODE]

[VBANovice]

Thanks! I really appreciate it!

[ADezii]

Thanks! I really appreciate it!

No problem, any questions feel free to ask.