Optimizing Query that uses linked tables and bound form w/joins

60 pts.
Tags:
Microsoft Access
SQL Server
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dmenke38
    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.
    185 pointsBadges:
    report
  • Jagoodie
    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.
    60 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following