472,989 Members | 2,930 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,989 software developers and data experts.

Can you use an ADO recordset with an Access report?

All, How do you use an ADO recordset with an Access 2002 report? The ADO recordset was created by querying an external SYBASE database. Once I get the data, how to I get my Access report to use the recordset?

Thanks for your help,
sphinney
Oct 18 '07 #1
5 13187
nico5038
3,080 Expert 2GB
Every query can be used as the recordsource for a report, what's the problem ?

Nic;o)
Oct 18 '07 #2
Every query can be used as the recordsource for a report, what's the problem ?

Nic;o)

Nico,

You're thinking of queries that are made in Access in design view or with the wizzard. Because the data I need is on my company's SYBASE server, my query must be done using code. Please see my sample code below:

Expand|Select|Wrap|Line Numbers
  1. Dim SQLstr As String
  2. Dim con As ADODB.Connection
  3. Dim RS As ADODB.Recordset
  4.  
  5.     SQLstr = "select XXXXX from XXXXX where XXXXX"
  6.     Set con = CreateObject("ADODB.Connection")
  7.     con.ConnectionString = "DSN=XXXXXXXX;uid=XXXXXXXX;pwd=XXXXXXXX"
  8.     con.Open
  9.     Set RS = con.Execute(SQLstr)
  10.  
  11.      'Some how run a report on the ADO recordset (RS) here!
  12.  
  13.     Set RS = Nothing
  14.     Set con = Nothing
Given the above code, how to I run an Access report on the ADO recordset (called "RS" in the code above)? Am I going about this the wrong way?

BTW: I tried linking to the table(s) from my .MDB, but the table on the SYBASE server has too many indexes, so Access wont link to it.

Thanks for your help,
Scott
Oct 19 '07 #3
nico5038
3,080 Expert 2GB
The Microsoft documentation you can download from this link:
http://support.microsoft.com/kb/q301987/
shows how to deal with ADO in combination with reports for the NorthwindCS.adp sample databse:

1. Open the sample project NorthwindCS.adp.
2. Open the Customer Labels report in Design view.
3. Clear the RecordSource property so that the report is unbound.
4. Add the following code to the report's Open event procedure:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.    Dim cn As ADODB.Connection
  3.    Dim rs As ADODB.Recordset
  4.  
  5.    'Use the ADO connection that Access uses
  6.    Set cn = CurrentProject.AccessConnection
  7.  
  8.    'Create an instance of the ADO Recordset class and open it
  9.    Set rs = New ADODB.Recordset
  10.    rs.Open "SELECT * FROM Customers WHERE CustomerID LIKE 'a%'", cn
  11.  
  12.    'Set the report's Recordset property to the ADO recordset
  13.    Set Me.Recordset = rs
  14.    Set rs = Nothing
  15.    Set cn = Nothing
  16. End Sub
  17.  
5. Press ALT+F11 to return to Microsoft Access.
6. Save the report, and then close it.
7. Print preview the Customer Labels report.

There's however also another solution by creating a so-called "Pass through" query. Such a query needs to be in the original database's SQL syntax, but offers the advantage that only the SQL is passed to the database and only the selected rows will be returned. The ADO connection will grab all rows and Access will do the selection afterwards. Certainly advisable to look into the "Pass through" (See also the F1 helpfile) when you're working in a networking environment.

Nic;o)
Oct 19 '07 #4
The Microsoft documentation you can download from this link:
http://support.microsoft.com/kb/q301987/
shows how to deal with ADO in combination with reports for the NorthwindCS.adp sample databse:

1. Open the sample project NorthwindCS.adp.
2. Open the Customer Labels report in Design view.
3. Clear the RecordSource property so that the report is unbound.
4. Add the following code to the report's Open event procedure:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.    Dim cn As ADODB.Connection
  3.    Dim rs As ADODB.Recordset
  4.  
  5.    'Use the ADO connection that Access uses
  6.    Set cn = CurrentProject.AccessConnection
  7.  
  8.    'Create an instance of the ADO Recordset class and open it
  9.    Set rs = New ADODB.Recordset
  10.    rs.Open "SELECT * FROM Customers WHERE CustomerID LIKE 'a%'", cn
  11.  
  12.    'Set the report's Recordset property to the ADO recordset
  13.    Set Me.Recordset = rs
  14.    Set rs = Nothing
  15.    Set cn = Nothing
  16. End Sub
  17.  
5. Press ALT+F11 to return to Microsoft Access.
6. Save the report, and then close it.
7. Print preview the Customer Labels report.

There's however also another solution by creating a so-called "Pass through" query. Such a query needs to be in the original database's SQL syntax, but offers the advantage that only the SQL is passed to the database and only the selected rows will be returned. The ADO connection will grab all rows and Access will do the selection afterwards. Certainly advisable to look into the "Pass through" (See also the F1 helpfile) when you're working in a networking environment.

Nic;o)
Nico,

Thanks for the help. The pass-through query seems to be the most promising solution. I'll look into that some more.

Thanks, again!
Scott
Oct 19 '07 #5
ADezii
8,834 Expert 8TB
Nico,

You're thinking of queries that are made in Access in design view or with the wizzard. Because the data I need is on my company's SYBASE server, my query must be done using code. Please see my sample code below:

Expand|Select|Wrap|Line Numbers
  1. Dim SQLstr As String
  2. Dim con As ADODB.Connection
  3. Dim RS As ADODB.Recordset
  4.  
  5.     SQLstr = "select XXXXX from XXXXX where XXXXX"
  6.     Set con = CreateObject("ADODB.Connection")
  7.     con.ConnectionString = "DSN=XXXXXXXX;uid=XXXXXXXX;pwd=XXXXXXXX"
  8.     con.Open
  9.     Set RS = con.Execute(SQLstr)
  10.  
  11.      'Some how run a report on the ADO recordset (RS) here!
  12.  
  13.     Set RS = Nothing
  14.     Set con = Nothing
Given the above code, how to I run an Access report on the ADO recordset (called "RS" in the code above)? Am I going about this the wrong way?

BTW: I tried linking to the table(s) from my .MDB, but the table on the SYBASE server has too many indexes, so Access wont link to it.

Thanks for your help,
Scott
In your Form's Open() Event, place the following code:
Expand|Select|Wrap|Line Numbers
  1. Dim SQLstr As String
  2. Dim con As ADODB.Connection
  3.  
  4. 'Consider making RS a Report Module Level Variable (Private RS As ADODB.Recordset) or a Global (Public RS As ADODB.Recordset) in a Standard Code Module.
  5. Dim RS As ADODB.Recordset
  6.  
  7. SQLstr = "select XXXXX from XXXXX where XXXXX"
  8. Set con = CreateObject("ADODB.Connection")
  9.  
  10. con.ConnectionString = "DSN=XXXXXXXX;uid=XXXXXXXX;pwd=XXXXXXXX"
  11. con.Open
  12.  
  13. Set RS = con.Execute(SQLstr)
  14.  
  15. 'Some how run a report on the ADO recordset (RS) here!
  16. Set Me.Recordset = RS
  17.  
  18. 'Probably not a good idea to Close the Recordset and release the resources assigned to it before the Report becomes visible.
  19. Set RS = Nothing
  20. Set con = Nothing
Oct 19 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Kevin R | last post by:
Question: I have a situation where I would like to prepare a Recordset object within Visual Basic (6.0), and then use the MS Access COM API's to Show a report built off of the data in my...
22
by: Gerry Abbott | last post by:
Hi all, I having some confusing effects with recordsets in a recent project. I created several recordsets, each set with the same number of records, and related with an index value. I create...
1
by: Robert Davis | last post by:
I would like to be able to create a recordset in MS Access 2000 that allows a macro to run and create a report for each ID that appears in the recordset. So I thought that I would use a scheduling...
8
by: lauren quantrell | last post by:
When I open an Access form I can have no recordset specified, then in the form's OnOpen event I can do something like: Me.paramaters = "@SomeColumn = 22)" Me.recordsource = "dbo.sproc123" But I...
1
by: Zlatko Matić | last post by:
Hello. I have a MS Access front end/PostgreSQL back-end combination. There is a report with nested graph (linked master/child fields). I used saved pass-through queries for both Record Source of...
6
by: Oko | last post by:
I'm currently developing an MS Access Data Project (.adp) in MS Access 2002. One of the reports within the DB uses data that is Dynamic and cannot be stored on the SQL Server. To resolve this, I...
10
by: nspader | last post by:
I want to start out saying I am a novice code writer. I am trying to send a report via email based on each supplier. The code below is what I am using. I need to base recordset on Form, report...
3
by: Gord | last post by:
Me again, I'm new to Access and am self teaching from a couple of books, so bear with me. (I've got a little experience with Visual Basic) As I understand so far, if I want to perform a bunch...
5
by: Matthew Wells | last post by:
Is there any way at all in Access 2003 to bind a report to an ADO recordset? The closest I've come so far is to use a pass through query that gets a table based UDF- but that's still not optimal...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
3
SueHopson
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.