?
Solved

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

Posted on 2013-01-27
13
Medium Priority
?
389 Views
Last Modified: 2013-03-19
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:}}
0
Comment
Question by:drtopserv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38824842
Will you be trying to update/edit the linked SQL table?

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.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38825117
<problem is that I can`t connect or build any table in the sql server.>

as temp solution , as per above post use stage table in access
as per solution find your DBA and ask to assist you on sql server site

for speed - you may need to set there indexes (sql server) , maybe even use stored procedures
0
 
LVL 16

Accepted Solution

by:
kmslogic earned 1500 total points
ID: 38825239
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.
0
PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

 

Author Comment

by:drtopserv
ID: 38825482
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.
0
 

Author Comment

by:drtopserv
ID: 38825484
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 :}
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38825493
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.
0
 

Author Comment

by:drtopserv
ID: 38825527
I think there  must be solution around :{{{{
need to seek , thanks for your answer anyway:}
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38825547
< i need to do query to the remote sql server table to get some fields and row from there. >
yes, you  need to do query to the remote sql server table to get some fields and row from there.

work with your DBAs team to have it
and for now staging table needs to be considered
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38825559
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'

c. or better, if there are criteria:

'Select field1, field2, field4, etc into tempTable from yourLinkedTable
where somefield = LocalTable.fieldInLocalTable'

tempTable being a staging table as mentioned by EugeneZ
0
 

Author Comment

by:drtopserv
ID: 38825567
Ok, the linked table you mean is the local table or the sql one.
if you are talking about the table resident in sql , then it`s not a good solution.
it`s very big table! .
0
 

Author Comment

by:drtopserv
ID: 38825569
Oh sorry i can see you mean the sql-server-table as linked table :{
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38825576
Yes.
 kmslogic (ID: 38825239) stated good points which were missing from simple suggestions.

Especially: Size of linked table, interms of record count, table structure
SQL, meaning specific fields needed from linked table
Tweaking to reduce amount of data needing to go to access
0
 

Author Closing Comment

by:drtopserv
ID: 38998270
thnx i`ll go for your suggestion
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question