Append Recordset from Oracle 9i db to another access db

[fogal]

Please Help!!!!
Get error message syntax error in from clause

Any suggestions welcome !

Private Sub Command29_Click()
Dim cnn1 As New ADODB.Connection

Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset

Dim strConnect1 As String
Dim strSQL1 As String

Dim strConnect2 As String
Dim strSQL2 As String

Dim fpath As String
fpath = CurrentProject.path & "\GSR_Reporter.mdb"

strConnect1 = "Provider=MSDAORA;Data Source=xxx;User ID=READ_ONLY;Password=READ_ONLY"
strSQL1 = "SELECT * FROM READ_ONLY.test"

Set rst1 = New ADODB.Recordset
rst1.Open strSQL1, strConnect1

Set rst2 = New ADODB.Recordset
strConnect2 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fpath

Do Until rst1.BOF Or rst1.EOF

strSQL2 = "Select a_b_c FROM GSR_EMWCriteria1" & _
"WHERE a_b_c = " & rst1!a_b_c & "' "

rst2.Open strSQL2, strConnect2

If rst2.BOF Or rst2.EOF Then
rst2.Close
rst2.Open "GSR_EMWCriteria1", strConnect2, adOpenDynamic, adLockOptimistic, adCmdTableDirect
rst2.AddNew
' Assuming all the fields are the same
rst2.Update
End If

rst2.Close
rst1.MoveNext

Loop

'------------------------------------------
If err.Number = 0 Then
MsgBox "test1", vbokayonly, "test2"
End If
'------------------------------------------

Set rst1 = Nothing
Set rst2 = Nothing
Set cnn1 = Nothing

'*******************************************

Exit_UpdateCustomer_Click:
Exit Sub

Err_UpdateCustomer_Click:
'DisplayError err.Number, err.description, "SynchInspections"
Resume Exit_UpdateCustomer_Click
End Sub

 

[cmmrfrds]

It looks like an unpaired single quote here.

strSQL2 = "Select a_b_c FROM GSR_EMWCriteria1" & _
"WHERE a_b_c = " & rst1!a_b_c & "' "

 

[fogal]

Append recordset to table in another access db?

I have a recordset that is a set od data gathered from oracle db. How can I then append this data to a table in another access db?

Please Help?

 

[fogal]

Append recordset to table in another access db?

I have a recordset that is a set of data gathered from oracle db. How can I then append this data to a table in another access db?

Please Help?

 

[cmmrfrds]

Open a separate connection for each of the data sources.

Dim cnn1 As New ADODB.Connection
Dim cnn2 As New ADODB.Connection

cnn1.Open strConnect1
rst1.Open strSQL1, cnn1

cnn2.Open strConnect2
rst1.Open "GSR_EMWCriteria1", cnn2, adOpenStatic, adLockOptimistic, adCmdTableDirect

If you need further help then describe the error and include the error message.