Access Pass Through Query

Posted on 2009-05-13
Last Modified: 2013-11-28
I created a Pass Through query in Access that returns a result set in the open event of a report.  I believe the data is coming back correctly as it appears in my debug window but, now that i have the data i am not sure what to do with it.  How do i get the data from my querydef to my report recordsource?
Private Sub Report_Open(Cancel As Integer)
    Dim MyDb As DAO.Database, MyQry As QueryDef, MyRS As DAO.Recordset
    Set MyDb = CurrentDb()
    Set MyQry = MyDb.CreateQueryDef("")
    MyQry.Connect = "ODBC;DSN=DSNName;DATABASE=DBName"
    MyQry.SQL = "spApplicantFlow '05/01/2007', '04/30/2008'"
    MyQry.ReturnsRecords = True
    Set MyRS = MyQry.OpenRecordset()
    Debug.Print MyRS!BranchName
End Sub

Open in new window

Question by:trademark16
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24377548

Basically you need to set your "Records" as the "RecordSource for the report

The RecordSource can be something like:
SELECT CustID,CustName,CustCountry FROM tblCust WHERE Country='Spain';


Author Comment

ID: 24377763
Not sure i understand.  I see that my RecordSet has data in it, but it's not a table that i can call.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24377877
I can't really decipher what you code is doing.

Dose his have to be a pass through query?

Can you first see that if a standard query will work, *then* try this as a Pass through query?

Can youy simply create a linked table from this source and create a report from this linked table?

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 24377936
It has to be a pass through query because i need to use the "with (nolock)" option in my from cluase or the query will go to a SUSPENEDED status and never complete.  Cannot use nolock in a standard access query.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24378251
Specifically, how are you sure that the data is "Coming Back Correctly"?

Record Count, Record stream, SQL?

Author Comment

ID: 24378310
Using the following code:

    Debug.Print MyRS!BranchName

I moved to the first record and the Debug did display the BranchName that i was expecting.

Accepted Solution

trademark16 earned 0 total points
ID: 24378770

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

737 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