Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-09-2019, 02:59 AM   #1
NigelBishop
Newly Registered User
 
Join Date: Oct 2019
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
NigelBishop is on a distinguished road
Use records retrieved from SQL server in an Access Report

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

Thank you in advance for any suggestions

NigelBishop is offline   Reply With Quote
Old 10-09-2019, 03:25 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,404
Thanks: 162
Thanked 1,729 Times in 1,699 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Use records retrieved from SQL server in an Access Report

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?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 10-09-2019, 04:24 AM   #3
sonic8
AWF VIP
 
Join Date: Oct 2015
Posts: 257
Thanks: 39
Thanked 74 Times in 71 Posts
sonic8 is on a distinguished road
Re: Use records retrieved from SQL server in an Access Report

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.

__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
Old 10-09-2019, 06:35 AM   #4
NigelBishop
Newly Registered User
 
Join Date: Oct 2019
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
NigelBishop is on a distinguished road
Re: Use records retrieved from SQL server in an Access Report

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?
NigelBishop is offline   Reply With Quote
Old 10-09-2019, 06:43 AM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,383
Thanks: 68
Thanked 2,698 Times in 2,583 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Use records retrieved from SQL server in an Access Report

you cannot set any recordset on the Open/Load event of a report.
create a Linked table and use it in your report.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-09-2019, 06:48 AM   #6
NigelBishop
Newly Registered User
 
Join Date: Oct 2019
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
NigelBishop is on a distinguished road
Re: Use records retrieved from SQL server in an Access Report

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?
NigelBishop is offline   Reply With Quote
Old 10-09-2019, 08:08 AM   #7
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,404
Thanks: 162
Thanked 1,729 Times in 1,699 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Use records retrieved from SQL server in an Access Report

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

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
NigelBishop (10-10-2019)
Old 10-09-2019, 08:09 AM   #8
sonic8
AWF VIP
 
Join Date: Oct 2015
Posts: 257
Thanks: 39
Thanked 74 Times in 71 Posts
sonic8 is on a distinguished road
Re: Use records retrieved from SQL server in an Access Report

Quote:
Originally Posted by NigelBishop View Post
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.
__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
The Following User Says Thank You to sonic8 For This Useful Post:
NigelBishop (10-10-2019)
Old 10-09-2019, 10:25 AM   #9
NigelBishop
Newly Registered User
 
Join Date: Oct 2019
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
NigelBishop is on a distinguished road
Re: Use records retrieved from SQL server in an Access Report

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?
NigelBishop is offline   Reply With Quote
Old 10-09-2019, 10:34 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,047 Times in 1,028 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Use records retrieved from SQL server in an Access Report

Quote:
Originally Posted by NigelBishop View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
NigelBishop (10-10-2019)
Old 10-09-2019, 12:11 PM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Use records retrieved from SQL server in an Access Report

@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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
NigelBishop (10-10-2019)
Old 10-10-2019, 01:33 AM   #12
NigelBishop
Newly Registered User
 
Join Date: Oct 2019
Posts: 9
Thanks: 5
Thanked 0 Times in 0 Posts
NigelBishop is on a distinguished road
Thumbs up Re: Use records retrieved from SQL server in an Access Report

Quote:
Originally Posted by theDBguy View Post
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.
NigelBishop is offline   Reply With Quote
Old 10-10-2019, 07:01 AM   #13
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,047 Times in 1,028 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Use records retrieved from SQL server in an Access Report

Quote:
Originally Posted by NigelBishop View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filtering records retrieved from SQL server NigelBishop Modules & VBA 7 10-04-2019 03:47 AM
Best approach to make sure retrieved online data goes into correct field in Access peskywinnets Modules & VBA 18 09-18-2016 08:56 AM
Wrong previous records retrieved Uni03 Forms 31 08-12-2011 11:29 AM
Access search form needs to be able to write retrieved data charlie442 Modules & VBA 1 07-21-2011 03:04 PM
Limiting records retrieved to 10... with a catch. antoniofh Queries 3 07-16-2007 10:59 PM




All times are GMT -8. The time now is 11:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World