Pass-through query slow when used as data source
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
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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.
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.
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"
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
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:
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?
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.
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:
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
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.