X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Using Ado To Create Passthrough Query, Access 2016    
post Jan 24 2020, 04:53 PM

Posts: 205
Joined: 4-September 01

I am wanting/needing to create a passthrough query in code using ADO as a recordsource for a report.

I am connecting to various stored procedures for the other objects in my database and want to remain consistent in method and reuse the same connection. Ideally I would set the report recordset however this isn't permitted for some reason known to MS.

So I have found this article Create Pass Through Using ADO from microsoft that purports to do what I would like to do.

I have copied the code across by copy and paste and altered the code marginally to suit my application being:
Private Sub cssSetReport(strSQL As String, rpt As Report)
'Reports cant bind the same as a form
'Therefore for the report we take a different routing creating a passthrough query as the reports recordsource
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command

    Set cat = New ADOX.Catalog
    Set cmd = New ADODB.Command
    If gcn.State = 0 Then cssOpenConnection
    cat.ActiveConnection = gcn
    Set cmd.ActiveConnection = gcn
    cmd.CommandText = strSQL
    cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
    'Modify the following connection string to reference an existing DSN for
'the sample SQL Server PUBS database.

    cmd.Properties _
     ("Jet OLEDB:Pass Through Query Connect String") = _
    cat.Procedures.Append "qryTemp", cmd
    rpt.RecordSource = "qryTemp"

Set cat = Nothing
  Set cmd = Nothing

End Sub

As the article suggests I have set my references to:
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.8 for DDL and Security (2.1 not available)

As this is my first time using this code, I am wanting to parrot it and will then come back on next pass and work out what each step is doing and start to customise it to better suit. I have of course altered the ODBC line and it is correct in the actual code.

However I cant get this code to run at all. Its hanging every time on cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True and I cant see if I have mistyped it, although I thought I copied and pasted it, nor can I at this stage explain what this line is doing and consequently decide if its critical or not as I haven't gone that deep yet.

Error is Run-time Error 3265 - 'Item not found in this collection'

As I understand it, this error is normally a spleling caused error. However I copied and pasted, therefore I don't think this is true...could be, but unlikely. I am wondering about the reference to ADO Ext. 2.8 as 2.1 is not available to me. I am also wondering if as the article was headed as applying to Access 2002 even though it was last updated in October 2019 is there something I am missing.

I just don't know where to start on diagnosing of this issue? Or/And for that matter is there a better/easier/faster/safer way to bind a report to a Stored Procedure on the fly in MS Access?
This post has been edited by dynamicdave: Jan 24 2020, 05:02 PM

Perth, Western Australia - The most isolated capital in the world
Go to the top of the page
Doug Steele
post Jan 24 2020, 05:04 PM

UtterAccess VIP
Posts: 22,244
Joined: 8-January 07
From: St. Catharines, ON (Canada)

What database does gcn point to: the one on the server, or your Access front-end? I believe it needs to be to the Access front-end: you point to the server database through the Jet OLEDB:Pass Through Query Connect String property.

Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
post Jan 25 2020, 03:32 AM

Posts: 679
Joined: 26-May 15
From: The middle of Germany

I am wanting/needing to create a passthrough query in code using ADO as a recordsource for a report.

Why do you want to use ADO for this? Even if you create the Pass-Through-Query with ADO it will still be executed with DAO. So, I would rather create it with DAO, which is rather simple.

Nevertheless, ...
The inconsistencies in the property names strike me as rather odd.

"Jet OLEDB:ODBC Pass-Through Statement"
"Jet OLEDB:Pass Through Query Connect String"

Why is there the ODBC prefix in one property and not the other?
Why is "Pass-Through" written with a hyphen once only?

So, even though these property names are consistent with the documentation, I think there is potential for a spelling error.

PS/EDIT: My suspicions where wrong. I copied and ran the Create PT-Query code with adapting the connection string but not changing anything else. - It worked right away.
This post has been edited by PhilS: Jan 25 2020, 03:38 AM

A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page
post Jan 27 2020, 05:17 PM

UtterAccess VIP
Posts: 2,952
Joined: 12-April 07
From: Edmonton, Alberta Canada

As noted, you can use ADO. However, if your "general" code now does not use ADO, then some risk occurs by introduction of a whole new OBJECT model into your database system. (and the rise of a broken reference(s) can also result).

And simple code like this can be effected:
dim   rst    as recordset    <---- is this now a ADO reocrdset, or a DAO one??????

As a result, you have to be careful of the "order" of references you have or do this:

dim   rst    as DAO.recordset

All in all, not the end of the world. If you have existing code using ADO, then it not really a big deal to use ADO for PT queries.

However, if most of your code is DAO?

Then I suggest you use this:

Base the report on the the PT query you create.

So, we then get this:

   With CurrentDb.QueryDefs("MyPass")
       .SQL = "exec sp_myProc"
   End With

   docmd.OpenRreport ......

Now of course the above is a example of a PT query calling + using a store proc for the source of the report, but we could use:

   With CurrentDb.QueryDefs("MyPass")
       .SQL = "SELECT from dbo.tblHotels where City = 'Edmonton'"
   End With

   docmd.OpenRreport ......

Once again, the reprort will use the PT query or whatever you stuff into the SQL for the PT query.

What is rather nice about the above?
Short, sweet, easy.
No need for connection strings in your code. You want to adopt a development approach in which you do NOT introduce all kinds of connection strings in your code.

So, simply add any PT query you your re-link code loop, and the connection string used for any linked table will be the SAME for the PT query.

I suppose, if you not yet included re-link code to include PT queries?

You could go:
   With CurrentDb.QueryDefs("MyPass")
      .connection = currentdb.tableDefs("some known linked table to SQL server").connection.
       .SQL = "SELECT from dbo.tblHotels where City = 'Edmonton'"
   End With

   docmd.OpenRreport ......

So, once again, no need to mess with connection strings in code.

And you don't need nor have to "set" the SQL for the report. You could always in the past, simple write out, or modify the SQL query that a report is based on, and THEN simply open the report as we are doing above.

So, create a PT query. In fact I have a system wide PT query that I use over and over in code say where I need a reocrd set as opposed to opening a report. I can thus go like this:

   dim rst  as DAO.recordset

   With CurrentDb.QueryDefs("MyPass")
      .connection = currentdb.tableDefs("some known linked table to SQL server").connection.
       .SQL = "SELECT from dbo.tblHotels where City = 'Edmonton'"
           set rst = .OpenRecordSet()
   End With
   ' do whatever you want with reocrdset - loop etc.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    16th February 2020 - 02:28 PM