473,320 Members | 1,951 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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 13276
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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.