Passthrough query as a function Recordset

Hi Experts; I could really use some help. I thought if I used a pass through query as a recordset would make my code run faster -- not working...

The SQL table has a million records and I would like to limit the recordset to just a single ID# which might mean 3 or 4 records will be returned.

Table has a MainID key (numeric)

As the user selects a MainID, I want to limit the records returned to those that match the MainID -- and then run through the remaining code -- which runs very fast -- the search to locate the MainID takes up to 10+ seconds.

I need to speed this up. The Passthrough query contains links between 3 related tables and I thought that having one single source would work best for my purposes.

So instead of using the below I'm looking for some advice on how to restructure and improve performance.

   Dim rsFINAN As DAO.Recordset
   Set rsFINAN = CurrentDb.OpenRecordset("qry_PassThruClientDebtFinance", dbOpenDynaset)

    strFCriteria = "[MainID] = " & lngBNo
    rsFINAN.FindFirst strFCriteria   <<<<<<<<<<<<<<<<<<<<<<<<<<<10+ seconds......
    If rsFINAN.NoMatch = False Then

Thanks a lot for any help you can provide - as always.

Best, Eileen
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
can you post the SQL of qry_PassThruClientDebtFinance

It doesn't look like you are taking advantage of the filtering that should be done by the SQL server, but it is hard to tell.  Normally, I would redefine the SQL of the pass-through and then open the recordset, something like:

Dim strSQL as string
Dim db as DAO.Database
Dim rsFINAN As DAO.Recordset

set db = currentdb
strSQL = "SELECT * from yourSQLTable WHERE [MainID] = " & lngBNo
db.querydefs("qry_PassThruClientDebtFinance").SQL = strSQL
Set rsFINAN = CurrentDb.OpenRecordset("qry_PassThruClientDebtFinance", dbOpenDynaset)

if rs.eof then
    msgbox "No records found"
    GoTo ProcExit

'other code would go here

End IF

    On Error Resume Next
    set rs = nothing
    set db = nothing
    Exit Sub
    'error handling code here
    Resume ProcExit

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
To expand on this, if you need records for more than one MainID, create a list of these and rebuild the SQL like this:

strSQL = "SELECT * from yourSQLTable WHERE [MainID] In (someId, otherId, anotherId)

This usually runs as fast as the server allows.

If you structure the query to include the criteria, you probably won't need it to be a pass-through since Access makes every attempt to "pass-through" all queries.  As Dale pointed out, you are not taking advantage of SQL Server at all.  Your query is unqualified and so it asks SQL Server to return ALL rows to memory on the local machine.  Then your FindFirst is doing the job that the query engine would have done had you sent the criteria with the query itself.  The whole point of using SQL server is to get the server to do the heavy lifting so you never want to send a query that requests all rows.  it defeats the whole purpose and will probably be slower than running the same query against an ACE database since Access is optimized to work closely with ACE.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.