query a joined local table with a linked dbo.sql.table

I have an Access 2010 accdb file which contain a local table.
I have also link this accdb file with a table resident in a SQL server, by using ODBC connection.
I have built sub-queries from a queries based on the local table with a dbo.table located on a server in the SQL.
Whenever running the sub-queries based on the queries as i mentioned , Access takes a long time to process the result.
I have heard that in order to speed up the result of the query I need to build a PASS-THROUGH query.
My problem is that I can`t connect or build any table in the sql server.
I must have local-table in the accdb + linked table from SQL i having both joined to manipulate data from both by using queries.
All queries built up, I did by using a DESIGN VIEW in Access 2010 . Without any VBA code of the DAO or ADO or PASS-THROUGH or SQL VIEW.
Please help, I think it's an interesting challenge:}}

If not: What I have done is to make a (temp) table in the Access DB using the minimal fields/records from the linked table. No guarantee the make-table query will be quick, but subsequent queries ought to be quicker.

It's difficult to see where the problem from just a description. The actual SQL from the queries would be useful as well as some information about the # of records, structure and indexes of the tables involved.

I think the suggestion to make a temp file is certainly a possibility.

This may be a pipeline issue where you're causing the SQL server to dump essentially the entire table over your network pipe in order to do its queries--so you may want to see if there's some tweaking you can do to reduce the data coming over the wire to the bare minimum.

Also using a snapshot query type for the sub queries (which will make the resulting query read-only) may help things.

Lastly you'll want to make sure you have appropriate indexes on both your local tables and the linked tables on SQL server you're using in your query. If you see a column in the where clause there's a pretty good chance you'll want an index on that column.

thanks all for the answer, but still can`t get solution from it.
there is no update in the queries, i`m talking about only manipulate data.
I do not have the ability to do any creation or access to sql server for many reasons.
production server that i shouldn`t touch from my side . i have hardly access the view tables in there to do the query, my local table data i have is from importing xls file to it every day. and i need to do query to the remote sql server table to get some fields and row from there.

doesn`t pass-through query help me out, may i use pass-through query to link local table with the sql remote table, is it possible, if yes then how can i do it .
does using vba code will help more than using the design view...?\
i hope that i can found suitable solution :}

No you can't use pass through to create a linked table. You can create a separate pass through query to access the SQL table directly without a linked table. however I cannot think of a situation where this would make your queries run faster.

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Drtopserv,
We have offered some solutions to possibly make your queries work faster.
a. Do a make-table query off of your linked table,
as in 'Select * into tempTable from yourLinkedTable'.
b. Better still, if table size is an issue:
'Select field1, field2, field4, etc into tempTable from yourLinkedTable'

Featured Post

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…