Use records retrieved from SQL server in an Access Report

[NigelBishop]

I have been able to retrieve records from a SQL server and populate a continuous form with the following 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]

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]

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]

following your suggestion I have created a simple view as below

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]

you cannot set any recordset on the Open/Load event of a report.
create a Linked table and use it in your report.

 

[NigelBishop]

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]

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]

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:

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]

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 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]

@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]

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]

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.