Use records retrieved from SQL server in an Access Report (1 Viewer)

NigelBishop

New member
Local time
Today, 11:09
Joined
Oct 4, 2019
Messages
14
I have been able to retrieve records from a SQL server and populate a continuous form with the following code

Code:
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Set conn = New ADODB.Connection

conn.Open "Provider=SQLOLEDB.1;Initial Catalog=IPTTrackerBE;Data Source=PHE-3J021434H;Integrated Security=SSPI;"

Set cmd = New ADODB.Command

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.CursorLocation = adUseClient
rs.Open "EXEC sp_Select_from_table T_IPTTrackerActionItems", conn
Set Me.Recordset = rs
[CODE]

:)

I have tried to use the same code in the on load event of a report but it does not return any results :confused:

Thank you in advance for any suggestions
 

Minty

AWF VIP
Local time
Today, 18:09
Joined
Jul 26, 2013
Messages
10,355
Unless that stored procedure is doing anything clever, why not simply create a SQL view and link that to your database, then use that for your report?
 

sonic8

AWF VIP
Local time
Today, 19:09
Joined
Oct 27, 2015
Messages
998
I think, you can only set the Recordset of a report in an ADP-Application.



Minty's suggestion is probably the best approach to solve this.
 

NigelBishop

New member
Local time
Today, 11:09
Joined
Oct 4, 2019
Messages
14
following your suggestion I have created a simple view as below
Code:
CREATE VIEW [dbo].[Select_from_tableView] AS

Select * from dbo.T_IPTTrackerIssues
[CODE]

I will eventually want to filter the view but I thought I'd keep it simple for now.

I need advice as to what I should enter into the On Load event of the report.

I'm currently trying 
[CODE]
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Set conn = New ADODB.Connection

conn.Open "Provider=SQLOLEDB.1;Initial Catalog=IPTTrackerBE;Data Source=PHE-3J021434H;Integrated Security=SSPI;"

Set cmd = New ADODB.Command
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient

rs.Open "EXEC Select_from_tableView", conn
Set Me.Recordset = rs
[CODE]

I'm guessing this is completely the wrong approach to running the view in Access?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:09
Joined
May 7, 2009
Messages
19,169
you cannot set any recordset on the Open/Load event of a report.
create a Linked table and use it in your report.
 

NigelBishop

New member
Local time
Today, 11:09
Joined
Oct 4, 2019
Messages
14
I was hoping that I would not need to create any linked tables are there any ways to do it without having to link tables?
 

Minty

AWF VIP
Local time
Today, 18:09
Joined
Jul 26, 2013
Messages
10,355
Use the view recordset to create a temporary local table?

I think it would help if you could explain why you appear to be using a sledgehammer to crack a small pistachio? ?
 

sonic8

AWF VIP
Local time
Today, 19:09
Joined
Oct 27, 2015
Messages
998
I was hoping that I would not need to create any linked tables are there any ways to do it without having to link tables?

Try this as Recordsource of your report:
Code:
SELECT * FROM [dbo.Select_from_tableView] IN ODBC [ODBC;Driver=SQL Server;Server=PHE-3J021434H;Trusted_Connection=Yes;DATABASE=IPTTrackerBE;];
If only need very view tables/views from SQL Server the above will do. If you need many, linked tables/views are probably more manageable.
 

NigelBishop

New member
Local time
Today, 11:09
Joined
Oct 4, 2019
Messages
14
I understand I am probably trying to make this far more complicated than it need be.
I have been asked to migrate my databases to SQL Server, a program I've only had access to for about a month and which I've never used. The long term plan will be to move to some form of web based frontend but that's a long way off at the moment.
I intend to continue to use Access as the frontend but to utilise as many features of SQL server and do as much coding as I can server side to develop my knowledge and abilities i.e. primarily as a learning opportunity. I could keep things simple with linked tables and then change the local queries to passthrough queries however I don't think that approach would necessarily stand me in good stead for our long term aims (I may be wrong and I stand to be corrected).
I hope that makes things a little clearer?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:09
Joined
Oct 29, 2018
Messages
21,358
I understand I am probably trying to make this far more complicated than it need be.
I have been asked to migrate my databases to SQL Server, a program I've only had access to for about a month and which I've never used. The long term plan will be to move to some form of web based frontend but that's a long way off at the moment.
I intend to continue to use Access as the frontend but to utilise as many features of SQL server and do as much coding as I can server side to develop my knowledge and abilities i.e. primarily as a learning opportunity. I could keep things simple with linked tables and then change the local queries to passthrough queries however I don't think that approach would necessarily stand me in good stead for our long term aims (I may be wrong and I stand to be corrected).
I hope that makes things a little clearer?
Hi. If you're saying you want to take advantage of SQL Server as much as possible including writing all the code server side, then why are you still trying to write this code on the Access side? Seems to me you could simply create a View on the Server for your report and then link your Access Front End to it and use the linked table/view for your report. At least, when it's time to dump Access for a new FE, you don't have to worry about any code you need to migrate because you still have the View on the Server and so all you have to do is use it in the new FE platform.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:09
Joined
Feb 19, 2002
Messages
42,971
@Nigel,
I think you have made this far more complicated than it needed to be especially if you're unfamiliar with SQL Server.

Swapping the Jet/ACE links for SQL Server links would almost certainly have gotten you 90% of the way there.
 

NigelBishop

New member
Local time
Today, 11:09
Joined
Oct 4, 2019
Messages
14
Hi. If you're saying you want to take advantage of SQL Server as much as possible including writing all the code server side, then why are you still trying to write this code on the Access side? Seems to me you could simply create a View on the Server for your report and then link your Access Front End to it and use the linked table/view for your report. At least, when it's time to dump Access for a new FE, you don't have to worry about any code you need to migrate because you still have the View on the Server and so all you have to do is use it in the new FE platform.

This is exactly what I'm trying to accomplish, the penny has just dropped as to what you're saying and the approach I should take. Thank you for your guidance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:09
Joined
Oct 29, 2018
Messages
21,358
This is exactly what I'm trying to accomplish, the penny has just dropped as to what you're saying and the approach I should take. Thank you for your guidance.

Hi Nigel. We were all happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom