?
Solved

Access Pass Through Query

Posted on 2009-05-13
7
Medium Priority
?
399 Views
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()
    
    MyRS.MoveFirst
    Debug.Print MyRS!BranchName
    
    MyQry.Close
    MyRS.Close
    MyDb.Close
 
End Sub

Open in new window

0
Comment
Question by:trademark16
  • 4
  • 3
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24377548
trademark16,

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';

JeffCoachman
0
 

Author Comment

by:trademark16
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.
0
 
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?

JeffCoachman
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:trademark16
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.
0
 
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?
0
 

Author Comment

by:trademark16
ID: 24378310
Using the following code:

    MyRS.MoveFirst
    Debug.Print MyRS!BranchName

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

Accepted Solution

by:
trademark16 earned 0 total points
ID: 24378770
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

830 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