Appending records: 2 ADO connections

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have setup a DSN-less connection to an oracle server. I want to append
records from a table in that connection to a table in my current database.
Here is the code i'm using. "tblEmployees" is the table in my database that
I'm trying to append the records to. "HONEST.EEMPLOYEE" is the table in my
oracle connection the records are in. The sql i'm using isnt working. It's
returning the message: "[ODBC driver for oracle][oracle]ORA-00942: table or
view does not exist". I'm certain I understand why its happening, but I have
no idea how to get around it.

Dim cn As New ADODB.Connection
Dim cnCurrProj As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim strSQL As String

On Error GoTo Form_Load_Error

Set cn = New ADODB.Connection
Set cnCurrProj = CurrentProject.Connection
Set rs = New ADODB.Recordset

' create ODBC connection to oracle tables
With cn
.ConnectionString = "PROVIDER=MSDASQL;" & _
"DRIVER={Microsoft ODBC for Oracle}; " & "SERVER=" &
strCONSTSERVER & _
"; " & "UID=" & strCONSTUSERNAME & "; " & "PWD=" &
strCONSTUSERPASS & ""
.Open
End With

' delete employee table records in current database
strSQL = "DELETE tblEmployee.* FROM tblEmployee;"
DoCmd.RunSQL strSQL

' append records from oracle table to current database table
strSQL = "INSERT INTO tblEmployee ( EMPNUM, EMPNAME, LENT1 ) " _
& "SELECT HONEST.EEMPLOYEE.EMPNUM, HONEST.EEMPLOYEE.EMPNAME,
HONEST.EEMPLOYEE.LENT1 " _
& "FROM HONEST.EEMPLOYEE " _
& "GROUP BY HONEST.EEMPLOYEE.EMPNUM, HONEST.EEMPLOYEE.EMPNAME,
HONEST.EEMPLOYEE.LENT1"
cn.Execute strSQL

' close objects
rs.Close
cn.Close
 
The sql i'm using isnt working. It's
returning the message: "[ODBC driver for oracle][oracle]ORA-00942: table or
view does not exist".

strSQL = "DELETE tblEmployee.* FROM tblEmployee;"
Click to expand...

Try:

strSQL = "DELETE FROM tblEmployee;"

Jamie.

--
 
Back
Top