Optimizing Query that uses linked tables and bound form w/joins
Home > IT Answers > SQL Server > Optimizing Query that uses linked tables and ...
Jagoodie
55 pts.
0
Q:
Optimizing Query that uses linked tables and bound form w/joins
Access, SQL Server, Linked Tables
I have a bound form whose record source is an Access
query.  That Access query involves 3 linked SQL Server tables (one inner
join, one left join).  The form takes a while to open. I have run a SQL
Profiler and it appears that access is sending two select fields from table
without where clauses, and one with (I only have one criteria in my base query,
so this makes sense) 



I was unaware that Access handled these queries involving linked tables like
this.. I thought I would run the join, to only select the records actually
involved. 



Is their a better way to do this with a bound form?  I tried a linked VIEW, but because of the
join, Access sees that view as not-updatable..



Software/Hardware used:
Microsoft Access, SQL Server
ASKED: Sep 24 2009  3:10 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
46795 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
This makes sense as Access sees the tables as seperate objects so it will do the join within Access instead of in the SQL Server.

The only way to handle this with a view would be to set it up so that each update only touched a single base table at a time.

You could try setting up some subforms and putting a single base tables worth of data on each subform. This way you are only pulling down the row that you need for each subform and Access and SQL Server should respond correctly.

The only other option that I see would be up change it from a bound for to calling a stored procedure on the SQL Server, which would then return just the one record you need. Then call a second stored procedure to handle the updating of the data.
Last Answered: Sep 24 2009  7:03 PM GMT by Mrdenny   46795 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Dmenke38   185 pts.  |   Sep 25 2009  6:33PM GMT

Are you updating the SQL Server tables, or just retrieving values from them?

If just mining them for values, you can do a pass-through query that does the selection on the server and returns (hopefully) a subset of data. That should speed things up. Access replicates the entire linked table locally when it is linked, and the local copy is not updatable. You would be better off retrieving a subset with the pass-through query and then using the query result set as a source for a combo box.

I improved one form’s opening time from 4 minutes down to 15 seconds by doing this with a UDB (DB2 on Unix) server. This tactic also reduced the network load significantly.

If you are updating the SQL Server tables, you need to do it directly with the appropriate action in a pass-through query, such as an Add, Delete, or Update in an event procedure. This does not cause replication, and the action takes place directly on the server. The price is learning the SQL Server syntax rules, as they vary slightly from Access 2003 syntax. You also have to hand code the query.

 

Jagoodie   55 pts.  |   Sep 28 2009  6:13PM GMT

I am indeed using this bound form to both edit and add data, as well as retrieve - unfortunately. Yes, the best route will be to move to passthrough queries, as I have done for several other forms. This form is especially large (many fields) and will take time to convert it. If the view had been update-able, it would have been a cake walk. And I love cake.

 
0