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).
What I found by putting console logs in there was that it was failing with "Error:Item not found in this collection" at
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?]
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?]