Topics Search

Pass-through query slow when used as data source

Pass-through query slow when used as data source
Views: 95
So here's my setup. We have a database with several tables that have over 255 fields. So we must use pass-through queries to access some of the data.

Usually no problem.

Here is my pass-through query:

qry001: SELECT db001.table001.field001 from db001.table001

If I execute this, it instantly spits me to datasheet view with all the millions of records ready for viewing.

If I modify it to:

SELECT db001.table001.field001 from db001.table001 WHERE db001.table001.field001 = '123'

It works fine and displays the one record instantly.

Now on to the problem

When I make a new query, using this pass-through query as a data source such as:

SELECT qry001.field001 FROM tblLocal001 LEFT JOIN qry001 ON tblLocal.field001 = qry001.field001;

This query takes forever to run.

And the thing is, tblLocal001 only has one record in it. In production, this table will have thousands so I need to understand why this query is taking so long and fix it if possible
Sponsored Links:

Similar posts...


pass thru query

pass thru query Icon
Using access 2003 front end sql server 2008 back end. When opening a form with a subform based on a pass through query, I am getting the following error:

You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport

I am using the pass through because subforms based on regular access query is slow since upsizing to sql server and thought pass through would be faster. I've search the internet but haven't come across any solutions.

Query runs slow

Query runs slow Icon
I up sized my 2003backend to SQL 2008. I kept my queries n access frontend. Now my update and append queries run really slow. What can I do. Should I look into converting to stored proc or pass through queries. Would this speed up performance.

Update access table from a pass-through query

Update access table from a pass-through query Icon
I want update a table (in Access 2010) based on the results from a pass-through query that pulls data from an old Fox Pro database.
With the following query I push data into my access table (everything works fine):

When I try to update the same table using the same pass-through query:

I get this error: "Operation must use an updatable query"

ORA-01013 error.

ORA-01013 error. Icon
When I run a pass-thru query, the data is returned, however if a make-table query is run on the pass-thru, the ORA error is returned. The pass-thru query's time out is set to 0. I don't think this is an oracle error because the pass-thru query works

Pass parameters to a query from a form

Pass parameters to a query from a form Icon
I have a query that returns a count of the number of products a customer currently has. The query has a parameter that asks for the customer_id.

I want to use the query as the control source of a field on a form. I need the form to pass the currently selected customer_id (the one they are looking at) as the query parameter value. How is this done, or is there a better way to do this? The form only looks at a single customer (and therefore customer_id) per page. Query is below:

Parameter Passing to a Report

Parameter Passing to a Report Icon
Here is the situation: (I did not create this report.) I am running a report using the “DoCmd.OutputTo” in a VBA module. The report’s data source is a query which is the result of another query with the input date range prompts on the other query.

The report is always run for the previous work day. In my VBA module I want to figure the previous day’s date and pass it to the report process so that I can automate the report running without having to enter a date range.

How do you pass data to a query prompt in a VBA nodule?

Using filtered form data as record source for report

Using filtered form data as record source for report Icon
I have a split form in an Access 2007 database where the data grid shows the records from a query. The user can filter any combination of fields from the the drop down headers. I have a command button above the data grid that opens a report with the same query as its record source. I'm trying to figure out how to pass the filtered data to the report so it will show only those records that the user is seeing in the data grid of the split form.

Automate pass-through query to run with different criteria and append to table

Automate pass-through query to run with different criteria and append to table Icon
I have a pass-through query that pulls data for a certain cluster number from the DB2 IBM database using an OBDC. I can only run the query for two group numbers at a time since there is a large amount of data that is returned.

I also have a make table query and append table query. What I would like to be able to do is connect to the OBDC DB2 server, run the pass-throughquery for the first cluster number, create a table with the make table query, run the pass through query again with second group number, and append this to the table from the make tablequery and so forth until I have gone through the list of cluster numbers.

I have a list of cluster numbers: (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20….etc.) which I need to run the query on and append to the table.

My pass-through query looks something like this:

Pass Through Query Report

Pass Through Query Report Icon
I have created a pass through query to show the order volume for a specific group of sales reps. I would like to get a report set up based on that query that will summarize the data (i.e. tell the number of orders entered and the dollar amount for those orders instead of listing all order individually). I tried creating a report, but everytime I try to run it, my database locks up on me. Is there a way to generate a report based on a pass through query

Extend Access with Pass-Through Queries

Extend Access with Pass-Through Queries Icon
In this article, we will devote our efforts to the latter of the three options, and concentrate on the use of Pass-Through queries as the medium of communication. Our examination of Pass-Through queries will include the following: the nature of Pass-Through queries, and instances in which their use is warranted; the advantages and disadvantages incumbent within the choice to use Pass-Through queries; creation and operation of a Pass-Through query to a MSSQL Server 2000 database.