Calling Stored Procedure and have it return records for a report

[eighthman11]

Hello Everyone.

Using Access 2000 and SQL Server 2008.

Below is an example of code I use to call a Stored Procedure on the
Sql Server. The Stored Procedure updates a worktable with records
based on the parameters that are passed. The worktable is used for an
Access report in my Access application. This works fine but is there
a way I can modify the call to my Stored Procedure to just return
records and use that data source for my report instead of using a
worktable on the sequel server. Any Help appreciated Thanks Ray.

Sub ReqAlertReport1(Co1 As Integer, UserName1 As String, ToLoc1 As
String, FromLoc1 As Integer, ReqNum1 As Long, Code1 As Integer)

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strConnectString As String

Dim Co As Integer
Dim UserName As String
Dim ToLoc As String
Dim FromLoc As Integer
Dim ReqNum As Long
Dim Code As Integer

Co = Co1
UserName = "'" & UserName1 & "'"
ToLoc = "'" & ToLoc1 & "'"
FromLoc = "'" & FromLoc1 & "'"
ReqNum = ReqNum1
Code = Code1

Set dbs = CurrentDb

strConnectString = "ODBC" & _
";Database=Viewpoint" & _
";UID=ODBC" & _
";PWD=odbc" & _
";DSN=Viewpoint"


Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnectString


qdf.ReturnsRecords = False 'Mark false if you do not want to return
records to application

qdf.SQL = "dbo.uspINToolOrderReqAlert " & Co & "," & Template & "," &
MatlGroup & "," & FromLoc & "," & Code & "," & CopyTemplate & ""



qdf.Execute ' use execute if ReturnsRecords = False

Set qdf = Nothing


End Sub

 

[Albert D. Kallal]

why not just use a "where" clause of the openreprot?

You can go:

dim strSql as string



strSql = "> UserName = "'" & UserName1 & "'" & _
"ToLoc = "'" & ToLoc1 & "'" & _
"FromLoc = "'" & FromLoc1 & "'" & _
"ReqNum = " & ReqNum1

Docmd.OpenReport "reportName",acViewPreview,,strSql

I am really at a loss here as to why a stored procedure is going to be a
help here at all?

You not going to save anything in terms of performance.

If the Stored procedure does some types of agitate functions or has joins to
related tables in its sql, then in that case simply build a view on the sql
server side, and then link a table to that. You then use this view (linked
table) as the data source for the report. You thus again can use the "where"
clause.

If that where clause results in only ONE row being returned..then that is
all the data that is going to come down the network pipe. In fact, this case
is also true even if were not using sql server, but using a jet based back
end.

So, you not going to save any performance here by using a stored proc, but
you certainly are using up valuable programmer and developer time that
could be used elsewhere...

You can't bind a reocrdset to a report. You *can* change the datasource of a
report, and you can also change the sql of a pass-though query that the
report happens to be based on...but neither of these techniques are needed,
nor will either result in any performance gains over using a where clause as
per above....

So, in a reports on-open event, you can stuff sql into the reports
recordsource, but that is not going to help you.

And, you can also direct change the sql of a query (which can be sql
pass-though).

Hence, if the report is based on pass-though, you could go:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("q2")
qdf.SQL = "select * from tblAnswers where id = 6"
qdf.Close

DoCmd.OpenReport "tblAnswers", acViewPreview

You can even place the above code inside of the report on open event so that
the respective code is attached to stays with the reports actual code
module.

However, at the end of the day you gain absolutely nothing for all this
extra work, so this begs the question and hints that you might as well just
use a simple where clause to accomplish your task.

 

[eighthman11]

why not just use a "where" clause of the openreprot?

You can go:

dim strSql       as string

strSql = "> UserName = "'" & UserName1 & "'" & _
         "ToLoc = "'" & ToLoc1 & "'" & _
         "FromLoc = "'" & FromLoc1 & "'" & _
         "ReqNum = " & ReqNum1

Docmd.OpenReport "reportName",acViewPreview,,strSql

I am really at a loss here as to why a stored procedure is going to be a
help here at all?

You not going to save anything in terms of performance.

If the Stored procedure does some types of agitate functions or has joinsto
related tables in its sql, then in that case simply build a view on the sql
server side, and then link a table to that. You then use this view (linked
table) as the data source for the report. You thus again can use the "where"
clause.

If that where clause results in only ONE row being returned..then that is
all the data that is going to come down the network pipe. In fact, this case
is also true even if were not using sql server, but using a jet based back
end.

So, you not going to save any performance here by using a stored proc, but
you certainly are using up valuable  programmer  and developer time that
could be used elsewhere...

You can't bind a reocrdset to a report. You *can* change the datasource of a
report, and you can also change the sql of a pass-though query that the
report happens to be based on...but neither of these techniques  are needed,
nor will either result in any performance gains over using a where clauseas
per above....

So, in a reports on-open event, you can stuff sql into the reports
recordsource, but that is not going to help you.

And, you can also direct change the sql of a query (which can be sql
pass-though).

Hence, if the report is based on pass-though, you could go:

   Dim qdf As DAO.QueryDef
   Set qdf = CurrentDb.QueryDefs("q2")
   qdf.SQL = "select * from tblAnswers where id = 6"
   qdf.Close

   DoCmd.OpenReport "tblAnswers", acViewPreview

You can even place the above code inside of the report on open event so that
the respective code is attached to stays with the reports actual code
module.

However, at the end of the day you gain absolutely nothing for all this
extra work, so this begs the question and hints that you might as well just
use a simple where clause to accomplish your task.

Thanks for the reply,

I guess the error of my way was I thought if you added a "where
clause" on an OpenReport command it was more
like a filter in which the entire View would run and all the data
would come down the pipe and the "where
clause" would just filter out the records you didn't want to see on
the report. Was I wrong to assume this?

 

[Albert D. Kallal]

I guess the error of my way was I thought if you added a "where

clause" on an OpenReport command it was more
like a filter in which the entire View would run and all the data
would come down the pipe and the "where
clause" would just filter out the records you didn't want to see on
the report. Was I wrong to assume this?

Yes, the "where" clause is simply appended to the sql for the report, and
occurs at open time.

If you use the reports "filter" option, this your assuming was/is 100%
correct. The reports full datasoruce is loaded, and THEN the filter kicks
in. However, in the case of a 'where' clause, only the records meeting the
criteria come down the network pipe. so in this case using a store procedure
or a view or just simply a plain Jane access linked table will not result in
any additional performance gains.

The only consideration you want to keep in mind here is if your report is
based on some SQL that is result of several tables being joined, or perhaps
several sub queries. In this case sometimes the linked table performs very
poorly. Jet pulls too much data down the pipe to get those aggregate totals.
The simple solution in this case is to simply build a view on the SQL server
side, and then create a linked table to that view, and then use that view as
the source for your report. In this case any aggregate function or totaling
that could result in potentially additional records coming down the pipe is
thus done server side. And again with a view you are free to use the "where"
clause to restrcit reocrds comming down the network pipe.

So, if this is just a plain table on the sql server side, then a simple
linked table is just fine here and You likely do not even have to resort to
using the view suggestion.

 

[eighthman11]

clause" on an OpenReport command it was more
like a filter in which the entire View would run and all the data
would come down the pipe and the "where
clause" would just filter out the records you didn't want to see on
the report.  Was I wrong to assume this?

Yes, the "where" clause is simply appended to the sql for the report, and
occurs at open time.

If you use the reports "filter" option, this your assuming was/is 100%
correct. The reports full datasoruce is loaded, and THEN the filter kicks
in. However, in the case of a 'where' clause, only the records meeting the
criteria come down the network pipe. so in this case using a store procedure
or a view or just simply a plain Jane access linked table will not resultin
any additional performance gains.

The only consideration you want to keep in mind here is if your report is
based on some SQL that is result of several tables being joined, or perhaps
several sub queries. In this case sometimes the linked table performs very
poorly. Jet pulls too much data down the pipe to get those aggregate totals.
The simple solution in this case is to simply build a view on the SQL server
side, and then create a linked table to that view, and then use that viewas
the source for your report. In this case any aggregate function or totaling
that could result in potentially additional records coming down the pipe is
thus done server side. And again with a view you are free to use the "where"
clause to restrcit reocrds comming down the network pipe.

So, if this is just a plain table on the sql server side, then a simple
linked table is just fine here and You likely do not even have to resort to
using the view suggestion.

Thank you so much for your help. This information has already made my
life a lot easier.

Ray