Solved

Access Pass Through Query

Posted on 2009-05-13
7
339 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now