Pass Through Query Strangeness

[Keith Wilby]

I've re-posted this topic because the other thread had become rather messy.

I've written a pass-through query which works fine. I want to use the PTQ
as the source for an append query so that I can take a snapshot of Oracle
data and store it locally.

Now here's what I've done for research purposes:

1 - create "data.mdb" and create and save a table with appropriate fields.
Close "data.mdb".

2 - create "FE.mdb" and link to the table in "data.mdb". Store the PTQ
(save the password in the connection string) and the append query in
"FE.mdb".

3 - Run the append query - it works.

4 - Close Access then re-open "FE.mdb" and re-run the append query - I get
an ODBC connection failure error. All subsequent re-tries on the append
query fail even though the PTQ still runs fine.

The only way I can get the append query to run again is be deleting and
re-creating "FE.mdb", and then it will only run for that one session. Even
re-creating the queries in the existing file doesn't work.

Can anyone replicate and/or explain this behaviour? Remember that the
pass-through query *always* runs, it's the append query that's the problem
and *only* with a linked table.

Many thanks.

Keith.

[Keith Wilby]

"Keith Wilby" <he**@there.comwrote in message
news:46**********@glkas0286.greenlnk.net...

I seem to have stumbled across a solution by trial and error. I find that
if I put the path to the file containing the linked table into the
"Destination DB" property of the append query it runs reliably ... I have no
idea why though.

Keith.

[Bri]

Keith Wilby wrote:

"Keith Wilby" <he**@there.comwrote in message
news:46**********@glkas0286.greenlnk.net...

I seem to have stumbled across a solution by trial and error. I find that
if I put the path to the file containing the linked table into the
"Destination DB" property of the append query it runs reliably ... I have no
idea why though.

Keith.

This works because it doesn't go through the link to get to the
destination database. The problems seems to be with going through the
link to the BE table.

--
Bri

[Keith Wilby]

"Bri" <no*@here.comwrote in message
news:PVwVh.92752$6m4.23857@pd7urf1no...

Keith Wilby wrote:

>"Keith Wilby" <he**@there.comwrote in message
news:46**********@glkas0286.greenlnk.net...

I seem to have stumbled across a solution by trial and error. I find
that if I put the path to the file containing the linked table into the
"Destination DB" property of the append query it runs reliably ... I have
no idea why though.

Keith.

This works because it doesn't go through the link to get to the
destination database. The problems seems to be with going through the link
to the BE table.

Yes that did dawn on me (eventually) and I've now deleted the linked tables.
Perhaps I've found a bug-ette with linked tables.

Regards,
Keith.