1

I am trying to insert ms access table into Oracle table using the below code. I get an error "table or view does not exist" at cn.Execute strSql line. I think the code is unable to identify the access table name. Any help is greatly appreciated. Thanks!

Function test()
Dim cn As New ADODB.Connection
Dim rs As DAO.Recordset
Dim strSql As String
Dim db As DAO.Database
Dim ssqlFields As String
Dim i As Integer
Dim ssql As String

Set db = CurrentDb

Set cn = New ADODB.Connection
Set rs = db.OpenRecordset("SELECT * FROM T_556_557_ORCL", dbOpenDynaset)

cn.Open ( _
"User ID = XXXX" & _
";Password=YYYY" & _
";Data Source=ABCD" & _
";Provider=OraOLEDB.Oracle")

cn.CommandTimeout = 300
strSql = "INSERT INTO TDMTRDOP.TEST SELECT Ent_Ex_Summ,QTY FROM T_556_557_ORCL"
cn.Execute strSql

Set rs = Nothing
Set cn = Nothing
End Function
Nick
  • 13
  • 4
  • 1
    You will need to insert the contents of RS as "T_556_557_ORCL" won't exist in cn's database. – Nathan_Sav Jul 09 '21 at 13:37
  • Thanks Nathan. Will you be able to help me with the code? I just need to know how to reference the RS in the Insert statement. – Nick Jul 12 '21 at 07:48
  • https://stackoverflow.com/questions/5864160/code-to-loop-through-all-records-in-ms-access – Nathan_Sav Jul 13 '21 at 07:15

0 Answers0