Linked Tables vs Pass-Through Queries

[Steve Happ]

Hello:

I've built an Access application (2003) that pulls some data from a SQL
Server database, allowing additional data to be added in an Access table. I
have been using linked tables to the SQL database with Access queries to
"merge" the added data to the SQL data. There are a couple of problems that
are causing me to rethink how it's done:
1. It's slow
2. The non-Access SQL application freezes up when my users are in the
Access application.

I have no need to open the SQL data in edit mode ... I'm simply reading and
displaying related data. I've read the comments about pass-through queries
as an option and have replaced the linked tables with pass-through queries,
but I'm running into snags:

1. Can you create a pass-through query that has data from both SQL database
and Access tables? I haven't been able to do so. So I create the
pass-through query with just SQL data and create another query to pull from
it and the Access tables. Is there a better way?

2. I had my main form's data record source as a query (with SQL linked
table data and access table). When I replaced the SQL linked tables with
pass-through queries, I could no longer add rows with my main form. Why?

3. My main report had a subreport at the end using linked table data. When
replacing the linked table with pass-through query, it now says you cannot
use a pass-through query in a subform.

SO ... is there a better way than pass-through queries to solve my initial 2
problems?

Thanks much. If you need more info to respond, I'm happy to give it, but
this has gotten too long already ... just ask.
Steve

 

[Douglas J. Steele]

Hello:

I've built an Access application (2003) that pulls some data from a SQL
Server database, allowing additional data to be added in an Access table.
I
have been using linked tables to the SQL database with Access queries to
"merge" the added data to the SQL data. There are a couple of problems
that
are causing me to rethink how it's done:
1. It's slow
2. The non-Access SQL application freezes up when my users are in the
Access application.

I have no need to open the SQL data in edit mode ... I'm simply reading
and
displaying related data. I've read the comments about pass-through
queries
as an option and have replaced the linked tables with pass-through
queries,
but I'm running into snags:

1. Can you create a pass-through query that has data from both SQL
database
and Access tables? I haven't been able to do so. So I create the
pass-through query with just SQL data and create another query to pull
from
it and the Access tables. Is there a better way?

There's no other way.

2. I had my main form's data record source as a query (with SQL linked
table data and access table). When I replaced the SQL linked tables with
pass-through queries, I could no longer add rows with my main form. Why?

Pass-through queries are read-only. Therefore, you can't add rows to them.
However, this seems to contradict your previous statements that "I have no
need to open the SQLdata in edit mode"

3. My main report had a subreport at the end using linked table data.
When
replacing the linked table with pass-through query, it now says you cannot
use a pass-through query in a subform.

This surprises me, and unfortunately I'm not in a position to test at the
moment.

SO ... is there a better way than pass-through queries to solve my initial
2
problems?

As there's is no way to solve your initial 2 problems, this seems a moot
question. <g>

 

[Steve Happ]

Douglas:

Thanks for your quick reply. Regarding: "Pass-through queries are
read-only. Therefore, you can't add rows to them. However, this seems to
contradict your previous statements that "I have no need to open the SQLdata
in edit mode"" ...
I don't want to add rows to the SQL table, just the Access Table. A field
on the Access table links to a field on the SQL table just to fill in the
data that's needed.

Regarding: "As there's is no way to solve your initial 2 problems, this
seems a moot question." ... Really? There's no way to solve the initial 2
problems?

Thanks\
Steve