Insert into Linked table fails

[Randy]

I have an Access 2003 database which contains a App.mdb, Data.mdb and a
linked Oracle database table.
The App.mdb has a simple form with one button that runs the following sub
(for testing I have removed all code down to this simple process):
=====
Private Sub btm_Append_Click()
Dim mSQLCmd As String
mSQLCmd = "INSERT INTO tbl_A SELECT Fld_1, Fld_2, Fld_3, Fld_4 FROM
ORA_table WHERE Fld_4='test'"
CurrentDb.Execute mSQLCmd
End Sub
=====
If I put the tbl_A table INSIDE the App.mdb the above code inserts 66,000+
different records from the Oracle table. This is what I want.

If I move the tbl_A table to Data.mdb and link to the table from App.mdb the
above code (with NO changes) inserts 66,000+ records ALL CONTAINING THE EXACT
SAME VALUES....or said differently it inserts the same one record 66,000+
times.

Has anyone seen this type of thing before?

We have several Access database that link to the Oracle database, so I know
that side is fine.

Thanks, Randy

 

[dch3]

Check that the link to ORA_table in both tables points to the same database
and the same table.

If that's not the problem, then open the DATA.MDB file and play around with
the query as a SELECT query to confirm which records are being selected, from
there build the append query. I always use the Query Builder as such and then
copy & paste the SQL statement into VBA. Also, as I seem to recall, if you're
working with a large number of records you'll want to save the SQL statement
as a query as opposed to having it as a SQL statement in VBA. If memory does
serve, once your run the query as a query once, Jet optimizes the query at
that time. Running it as a SQL Statement via code results in Jet having to
optimize the statement each time it needs to be run prior to it actually
being run. I'm not the expert on DB optimization, but I seem to recall
reading that somewhere.

David H

 

[Randy]

I have done extensive additional testing.
I removed the Access table entirely and did just a simple SELECT query
pulling just five fields from the Oracle table.

I found that the SELECT query returns the one record 66K+ times.

But if I do a GROUP BY and include all five fields the query returns the
correct 66K+ records.

This to me points to an index or table problem on the Oracle side.
I am trying to talk to the Oracle group.

Thanks for anyone who gave this some thought, but for now I am pretty sure
this is not an Access issue.
Randy