471,111 Members | 1,653 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,111 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 12634
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,822 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

Post your reply

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

Similar topics

22 posts views Thread by Gerry Abbott | last post: by
3 posts views Thread by Gord | last post: by
5 posts views Thread by Matthew Wells | last post: by

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.