By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Can you use an ADO recordset with an Access report?

P: 69
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
Share this Question
Share on Google+
5 Replies


nico5038
Expert 2.5K+
P: 3,072
Every query can be used as the recordsource for a report, what's the problem ?

Nic;o)
Oct 18 '07 #2

P: 69
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
Expert 2.5K+
P: 3,072
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

P: 69
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
Expert 5K+
P: 8,597
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.