Solved

Access Pass Through Query

Posted on 2009-05-13
7
362 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 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