Solved Re-writing pass-through query (1 Viewer)

CarlSeil

New member
Local time
Today, 03:07
Joined
Nov 30, 2020
Messages
14
Have query that is currently run as a passthough query in the legacy app (see threadhttps://www.access-programmers.co.uk/forums/threads/error-in-loading-dll-when-adding-reference-to-dao.315181/)

I was getting message "Error:Item not found in this collection" when running this pass-trough.

Basically, the VBA builds a string that (usually) runs a stored procedure (some snippage below).

Code:
    ssql = "exec sp_my_report "
     ssql = ssql & "my_parameters"
    Dim X As Variant
    Dim qd As QueryDef
    Dim db As Database
    Dim sReport_Style As String
    Dim swhere As String

    Set db = DBEngine(0)(0)
    Set qd = db.QueryDefs("SQL PASS THROUGH2")
    qd.sql = ssql
    qd.Close
    
   
    DoCmd.OpenQuery "SQL PASS THROUGH2"

   DoCmd.OpenForm "my_form", acNormal
   
    ...

What I found by putting console logs in there was that it was failing with "Error:Item not found in this collection" at Set qd = db.QueryDefs("SQL PASS THROUGH2") So, on a whim I copied the old SQL PASS THROUGH2 query from the legacy Access db. Aha, it works.

Question is: how would I create a SQL PASS THROUGH2 query from scratch if I didn't have one to copy from an old file?

[As an aside, why does a form that is missing a query or subquery as data source fail in VBA with message "OpenForm action was cancelled" instead of telling me what query or subquery is causing the problem? I'm like...I didn't cancel it, why did you cancel it?]
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:07
Joined
Oct 29, 2018
Messages
21,358
Question is: how would I create a SQL PASS THROUGH2 query from scratch if I didn't have one to copy from an old file?
In that case, you would use the CreateQueryDef method to create the query first.
 

Isaac

Lifelong Learner
Local time
Today, 01:07
Joined
Mar 14, 2017
Messages
8,738
Question is: how would I create a SQL PASS THROUGH2 query from scratch if I didn't have one to copy from an old file?
If you go to the Design > new Query design part of the Ribbon, then choose Pass Through as the type (which will basically just put you in SQL view), then over to the right on the Properties, you need to click ellipsis button to create the connection string.

However, it works perfectly fine (if you want the exact same connection) to copy and paste an old pass through query, and then edit the SQL.
 

CarlSeil

New member
Local time
Today, 03:07
Joined
Nov 30, 2020
Messages
14
If you go to the Design > new Query design part of the Ribbon, then choose Pass Through as the type (which will basically just put you in SQL view), then over to the right on the Properties, you need to click ellipsis button to create the connection string.

However, it works perfectly fine (if you want the exact same connection) to copy and paste an old pass through query, and then edit the SQL.
Thanks, I was missing that little button for the Pass Through type on the ribbon. Looks like we're constantly overwriting the ones that already exist most of the time.
 

Users who are viewing this thread

Top Bottom