Solved

Access Pass Through Query

Posted on 2009-05-13
7
352 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

920 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

17 Experts available now in Live!

Get 1:1 Help Now