Solved

Performance issues with ODBC connections using queries and form built for search criterias in Access 2007

Posted on 2016-10-29
18
42 Views
Last Modified: 2016-11-04
I have 5 tables that are in SQL that I have connected to via an ODBC connection. I am using these tables to build a search criteria form so my users can search these two tables for historical data. One table is all our sales quotes and the other is our sales orders. They have different field names, etc so what I did was link to these tables via ODBC and then pull the fields that store the same kind of data and then change the names in individual queries to match them before I create a union query to merge them together. The queries all open relatively quick (within 5 seconds at the most). But when I get to my form and enter my criteria and load the combined query it takes over 5 mins to load this data. Why would the performance take such a big hit going to a form? Here is the breakdown of how I have things set up from the ODBC connection right to the form.

2 ODBC connections to the 5 tables in my SQL database.

qryQuoteMainDetail

SELECT dbo_qtmast.fquotedate AS DateCreated, dbo_qtmast.fquoteno AS OrderNo, "Quote" AS DocType, dbo_qtmast.fstatus AS Status, dbo_qtmast.fcustno AS CustNo, dbo_qtmast.fcompany AS CompanyName, dbo_qtmast.festimator AS Estimator, dbo_qtitem.fdesc AS PartDesc, dbo_qtitem.fcustpart AS CustomerPartNo, RTrim([fgroup]) AS GroupCode, dbo_qtitem.fprodcl AS ProdCL, dbo_qtitem.festqty AS Quantity, dbo_qtitem.funetprice AS UnitPrice, dbo_qtitem.fsource AS Source, dbo_qtitem.fmeasure AS UoM, Val(IIf(InStr([fdesc],"-.")>0,Mid([fdesc],InStrRev([fdesc],"-.")+1,5),0)) AS DiaTol
FROM dbo_qtmast INNER JOIN dbo_qtitem ON dbo_qtmast.fquoteno = dbo_qtitem.fquoteno
WHERE (((dbo_qtmast.fquotedate) Between DateSerial(Year(Date()),Month(Date())-24,1) And DateSerial(Year(Date()),Month(Date())+1,0)) AND ((dbo_qtmast.fquoteno)<>"CANCELLED"))
ORDER BY dbo_qtmast.fquotedate DESC;

Open in new window


qrySalesOrderMainDetail

SELECT dbo_somast.forderdate AS DateCreated, dbo_somast.fsono AS OrderNo, "Sales Order" AS DocType, dbo_somast.fstatus AS Status, dbo_somast.fcustno AS CustNo, dbo_somast.fcompany AS CompanyName, dbo_somast.festimator AS Estimator, dbo_soitem.fdesc AS PartDesc, dbo_soitem.fcustpart AS CustomerPartNo, RTrim([fgroup]) AS GroupCode, dbo_soitem.fprodcl AS ProdCL, dbo_soitem.fquantity AS Quantity, qrySORelease.funetprice AS UnitPrice, dbo_soitem.fsource AS Source, dbo_soitem.fmeasure AS UoM, Val(IIf(InStr([fdesc],"-.")>0,Mid([fdesc],InStrRev([fdesc],"-.")+1,5),0)) AS DiaTol
FROM (dbo_somast INNER JOIN dbo_soitem ON dbo_somast.fsono = dbo_soitem.fsono) INNER JOIN qrySORelease ON (dbo_soitem.finumber = qrySORelease.finumber) AND (dbo_soitem.fsono = qrySORelease.fsono)
WHERE (((dbo_somast.forderdate) Between DateSerial(Year(Date()),Month(Date())-24,1) And DateSerial(Year(Date()),Month(Date())+1,0)) AND ((dbo_somast.fstatus)<>"CANCELLED") AND ((dbo_soitem.fprodcl)<>"03"))
ORDER BY dbo_somast.forderdate DESC;

Open in new window


Those are the 2 main queries that I am connecting to via an ODBC connection.

From there I create a union query to combine them into 1 query.

qryUnionSalesOrdersAndQuotesA

SELECT * FROM qryQuoteMainDetail UNION SELECT * FROM qrySalesOrderMainDetail;

Open in new window


The next query is the one that stores all the criterias for the fields I want to search using controls on my form. This query is very large because of all the different combinations of searches that can be used on my form

qrySalesOrderQuoteHistoryBrowse

SELECT qryUnionSalesOrdersAndQuotesA.OrderNo, qryUnionSalesOrdersAndQuotesA.DocType, qryUnionSalesOrdersAndQuotesA.DateCreated, qryUnionSalesOrdersAndQuotesA.CustNo, qryUnionSalesOrdersAndQuotesA.CompanyName, qryUnionSalesOrdersAndQuotesA.GroupCode, qryUnionSalesOrdersAndQuotesA.Quantity, qryUnionSalesOrdersAndQuotesA.UoM, qryUnionSalesOrdersAndQuotesA.PartDesc, qryUnionSalesOrdersAndQuotesA.CustomerPartNo, qryUnionSalesOrdersAndQuotesA.UnitPrice, [Quantity]*[UnitPrice] AS ExtPrice, qryUnionSalesOrdersAndQuotesA.Estimator, qryUnionSalesOrdersAndQuotesA.Source, GetPrimarySize([PartDesc]) AS DiaParse, qryUnionSalesOrdersAndQuotesA.DiaTol, DocTypeFilter([DocType]) AS DocFilter
FROM qryUnionSalesOrdersAndQuotesA
WHERE (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType])) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.DiaTol) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((GetPrimarySize([PartDesc])) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.Quantity) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.GroupCode)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((qryUnionSalesOrdersAndQuotesA.CustNo)=[Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null)) OR (((qryUnionSalesOrdersAndQuotesA.DateCreated) Between [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateFrom] And [Forms]![frmSalesOrderQuoteHistoryBrowse]![txtDateTo]) AND ((DocTypeFilter([DocType]))=[Forms]![frmSalesOrderQuoteHistoryBrowse]![optType]) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboCustomer]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![cboGroupCode]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMin]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtQtyMax]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxDia]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMinTol]) Is Null) AND (([Forms]![frmSalesOrderQuoteHistoryBrowse]![txtMaxTol]) Is Null));

Open in new window


I then use that query above as the rowsource for my listbox on my form where the results are loaded.
The listbox on my form does not have a rowsource loaded when the form opens. This allows the form to open quicker since there is not data loaded into it. The user will enter their criteria into the controls on the form. Then they click the cmdGo button to query the results and load the data into my listbox. This is where it takes up to 5mins or more to load the results. Why would it take that long on my form? The queries don't take that long to open directly. Here is the code behind my cmdGo button.

cmdGo

Dim strSQL As String

strSQL = "SELECT qrySalesOrderQuoteHistoryBrowse.OrderNo, qrySalesOrderQuoteHistoryBrowse.DocType, qrySalesOrderQuoteHistoryBrowse.DateCreated, qrySalesOrderQuoteHistoryBrowse.CustNo, qrySalesOrderQuoteHistoryBrowse.CompanyName, qrySalesOrderQuoteHistoryBrowse.GroupCode, qrySalesOrderQuoteHistoryBrowse.UoM, qrySalesOrderQuoteHistoryBrowse.PartDesc, qrySalesOrderQuoteHistoryBrowse.CustomerPartNo, qrySalesOrderQuoteHistoryBrowse.Quantity, qrySalesOrderQuoteHistoryBrowse.UnitPrice, qrySalesOrderQuoteHistoryBrowse.ExtPrice, qrySalesOrderQuoteHistoryBrowse.Estimator, qrySalesOrderQuoteHistoryBrowse.Source, qrySalesOrderQuoteHistoryBrowse.DiaParse, qrySalesOrderQuoteHistoryBrowse.DiaTol " & vbCrLf & _
"FROM qrySalesOrderQuoteHistoryBrowse " & vbCrLf & _
"ORDER BY qrySalesOrderQuoteHistoryBrowse.DateCreated DESC;"

    Me.lstItems.RowSourceType = "Table/Query"
    Me.lstItems.RowSource = strSQL
    Me.lstItems.Requery

Open in new window


Is there any easier, and faster way to get this search form to work better? My users get frustrated because it takes so long to load. Am I missing something on the structure of how I have this built from the ODBC to the form itself?
0
Comment
Question by:Lawrence Salvucci
  • 7
  • 7
  • 3
  • +1
18 Comments
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41865753
Hi.

You can try to do a Passtrhough Query via VBA.

Firstly, you must create a passtrhough query to you server (any simple query is enough), this is the temporal query.

You must create the query sql with code in a string variable.

Once you have it, you can assign that sql to the temporal query you have.

Currentdb.Querydefs("MyTemporalQuery").Sql = strSQL

Open in new window


You only need the listbox attached to the temporal query.

As this is a passtrhough query, it runs faster than normal queries.

Best regards.

Antonio.
Barcelona ( Spain)
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41865838
Not sure I totally understand how this will be put together and replace what I currently have. I also believe I have tried to create a Passthrough query and it still has performance issues like I am currently experiencing.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41865996
1.  Make sure that the server-side tables have indexes on the dates and other fields used as criteria.
2.  Run the query profiler to determine what SQL Access is sending to the server.  It is possible that due to the use of VBA functions, Access is requesting all the data from the server rather than sending the query to the server.
3.  Although Access makes every attempt to make EVERY query a pass-through query, sometimes, it can't (see #2).  In that case, you will need to build the pass-through query yourself.  Since pass-through queries do not take arguments, you will need to build the SQL using VBA and then save the query as a pass through and bind that to your RecordSource or RowSource.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866011
Hi Pat,
Thanks for the reply. Just a few questions for you. Would I replace the first 2 queries prior to the union query with pass through queries that have the source as the linked tables from my SQL Server? Would it make more sense if I built those 2 queries along with the union query in SQL and then linked to those instead of linking to the SQL tables? Then I could create the SQL VBA code with the criteria parameters in my vba code behind my form where the listbox is. Would that help with the performance of the form loading the results? I'm just wondering if I have too many queries and that is causing it to take so long to load the results. Or is it because of all the criteria options that is slowing it down? The tables on the server-side have indexes on the dates and most of the other fields used in the criteria.
Larry
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41866122
At first i must ask ....how many records are you trying to fetch back....if all the job is done with linked tables you can forget performance....it is going to be slow
The only way to get performance is to create a stored procedure that you are going to call it through Access and pass the dates as parameters...you get the results of the SP to a recordset or to a temp table  and you use them to populate the listbox
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866130
Hard to say how many records there will be. It depends on what the user selects for the criteria. Between those tables in SQL there are approx. 70,000 records right now in total. But that will obviously grow over time. There are 11 controls on my form that control the criteria so there could be 10 records returned or 50,000 returned. I have no idea how to create a stored procedure in SQL for this. And there are more than just the dates as the parameters as I mentioned with all the controls on my form. How would I pass those criteria selections to this stored procedure? This is where I get lost since I am not that great with SQL.
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41866133
If you are not utilizing the processing power of SQL then you are going to be slow and as records increase you will become slower and slower.... ..Try to transfer your queries one by one to the SQL and create passthrough queries to get the results...
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866135
Do I leave the query that has all the criteria in it in Access and just move the rest back to SQL? If I do that then what do I link from Access to SQL then? Right now I am linking directly to the tables in SQL. Do I now link to the queries or views in SQL and then create pass through queries to those new links?
0
 
LVL 13

Accepted Solution

by:
John Tsioumpris earned 250 total points
ID: 41866146
Everything is going to be SQL and i mean everything....The only connection point will be a passthrough query where you pass the parameters...
I didn't realized how huge was your query until i passed to a SQL formatter....600 lines...that a lot...
Just a hint because probably with this size you are going to get lost...a quick and dirty solution is to create passthrough query where all the parameters are "represented" by dummy values ..e.g.for a month criteria you give a value of 13 (Const InValimonth as string= "13")...this will be the source
And a 2nd passthrough query with ...just write the simplest SELECT....it will be the destination....
Then you go and to your VBE you set a querydef and you get the SQL of the source
strSQL =qdf.SQL

Open in new window

You close the "source" qdf
You use the Replace to find and replace the values with values from the form controls
Replace(StrSQL,ConstInvalidMonth,ValueFromTheControl).....if something is not "easily" accessible just broaden the string e.g ConstInvalidSomething as string = "=blah" to replace it with "LIKE theControlValue"
You open the destination qdf
qdf.SQL = strSQL
you close it...
Just fire the passthrough to get the result...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866151
Oh Boy. This isn't going to be easy for me. I have no idea how to pass the parameters through a passthrough query back to SQL. So all this would still be done in a stored procedure within SQL, correct?

This is the steps I am walking through in my head with how this will be set up in SQL. Can you confirm if my train of thought is correct?

1. I would have my 2 basic queries pulling the data from each of the files.
2. Then I would union them together in a union query.
3. Then I would create a stored procedure based on that union query.
4. Then I would create a passthrough query in Access to point to the stored procedure and pass the parameters to that stored procedure.

When I set up an ODBC connection to my SQL server I would assume I would choose this stored procedure as my linked table, correct?
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41866166
The Stored procedure's output will either "feed" a recordset or a temp table....
Set rs= cmd.Execute

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41866305
Do you know of any tutorials that I can read that talk about passthrough queries and passing the parameters to a stored procedure in SQL? That's where I'm not that familiar with things and would like to understand it better.
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41866460
Well there a ton of tutorials for passthrough queries...just search the Articles here on EE or google it...
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41868795
I am taking a stab at building a stored procedure after reading up quite a bit on pass-thru queries and stored procedures with parameters. I am having a problem with the union query that I need to put into my stored procedure since this data is coming from two different tables. The fields are named the same in the views so they will match up correctly. But how do I get the union part written in my stored procedure? The parameters need to query both views though. When I try to test it and create this procedure I get an error that says:

"The multi-part identifier "Q1.DateCreated" could not be bound."

CREATE PROCEDURE [dbo].[upMySprocHistory]
(
@FieldDateCreated Varchar(50) = NULL,
@FieldCustNo Varchar(50) = NULL,
@FieldGroupCode Varchar(50) = NULL,
@Quantity Varchar(50) = NULL
)
AS
SELECT * FROM viewQuoteMainDetail AS Q1
   UNION ALL 
   SELECT * FROM viewSalesOrderMainDetail As S1
WHERE ((@FieldDateCreated Is Null) Or (Q1.DateCreated)>= @FieldDateCreated)

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41874282
John,
Access attempts to "pass through" EVERY query.  If I run a select query that will select 10 records out of a million, Access sends the query to the server and the server returns ONLY 10 rows.  It doesn't return a million.  If I run a pass through query with no criteria, the server will send back a million rows!  Is it better?  No.  You need to understand how to use criteria to limit the rows returned.  Of course not understanding how this all works, people can prevent Access from being able to send the query to the server and in that case, Access will request that the entire table be returned.  The vast majority of my queries against million row tables run just fine.  Occasionally, I create pass through queries for updates or views for common joins.  But to say that all Access queries against linked ODBC tables will be slow is just plain wrong.
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41874528
@Pat are you referring to me?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41874680
If you are the one recommending that ALL queries should be converted to pass-through, then yes.
0
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41874702
No you got me wrong...i meant all the queries should be converted to views on the server...because he has a rather complicated situation i recommended to  create pass through queries in order to build the final query as step by step process..
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now