[AccessD] [Accessd] Record Source for Reports Using SQL Db Backend - best practice

Hi All,
I came across an interesting Technet article:
Optimizing Microsoft Office Access Applications Linked to SQL Server
http://technet.microsoft.com/en-us/library/bb188204(SQL.90).aspx

I am using .mdb Frontend against SQL Db backend.

One suggestion on this post subject was to use temporary local table as
record source for a report.

I ended up using Pass Through Queries as record source for reports.
Works great! ... That is, once you've figured out how to use them
- especially how to dynamicly change the input parameters.

Example:
I have a Stored Procedure on the SQL Server with a number of input
parameters.
It's called usp_ReportRecordSource

On the Frontend I have a Pass Through Query called
qpt_usp_ReportRecordSource

It consists of the following syntax:
EXEC usp_ReportRecordSource '2009-05-10', 2009, 19, 2

The procedure that opens the Report first gathers and changes the input
parameters like this:

Dim strSQL As String
'set the parameters of the Pass Through Query that functions as record
source for the Report
strSQL = "EXEC usp_ReportRecordSource " & CurrentstrDate & ", " &
CurrentlngYear & ", " & CurrentlngWeekNumber & ", " &
CurrentlngWeekDayNumber
If bshPassThroughFixup("qpt_usp_ReportRecordSource ", strSQL) = True Then
        DoCmd.OpenReport "rptStaffAvailableOnDay", acPreview
        DoCmd.OpenForm "frmSYSPrintFill"
Else
  MyMsgBox "Exception: Exception encountered when attempting to set
Parameters for the Pass Through Query of 'usp_ReportRecordSource'. Wait a
little and try again. If the exception persists, contact HelpDesk", vbOKOnly
End If

where the bshPassThroughFixup does the following (taken from the Technet
article):

Public Function bshPassThroughFixup( _
  QueryName As String, _
  Optional SQL As String, _
  Optional Connect As String, _
  Optional ReturnsRecords As Boolean = True) As Boolean
On Error GoTo EH
    ' Modifies pass-through query properties
    ' Inputs:
    '   QueryName: Name of the query
    '   SQL: Optional new SQL string
    '   Connect: Optional new connect string :
                'if set overrides the default connect string created by
function fncTestSQLConnection()
    '   ReturnsRecords: Optional setting for ReturnsRecords--
    '                   defaults to True (Yes)

    bshPassThroughFixup = False

    If fncTestSQLdbConnection() = True Then
      Dim db As DAO.Database
      Dim qdf As DAO.QueryDef

      Set db = CurrentDb
      Set qdf = db.QueryDefs(QueryName)
      If Len(SQL) > 0 Then
          qdf.SQL = SQL
      End If
      If Len(Connect) > 0 Then
          qdf.Connect = Connect
      Else
          qdf.Connect = mstrODBCConnect     'default global variable set by
fncTestSQLConnection
      End If
      qdf.ReturnsRecords = ReturnsRecords
      qdf.Close
      Set qdf = Nothing
      bshPassThroughFixup = True
    Else
      MyMsgBox "Exception: Could not connect to the SQL Db! Try again later.
If this exception persists then contact HelpDesk!", vbOKOnly
    End If
EX:
    Exit Function
EH:
    MyMsgBox "Exception in bshPassThroughFixup " & ": " & Err.Number & " " &
Err.Description
    Resume EX
End Function
(fncTestSQLdbConnection() tests the connection to the SQL Db based and also
sets the
(default) global variable ODBC Connection String for the Pass Through
Queries....
example of which could be:
ODBC;Description=[odbc dsn name];DRIVER=SQL
Server;SERVER=[YourServerName\SQL Instance Name];UID=[user id];PWD=[pass
word];DATABASE=[sql db name];NETWORK=DBMSLPCN;ADDRESS=[YourServerName\SQL
instance name,optional port number]

Hope this might be helpful to some...
Regards
borge



On Mon, May 11, 2009 at 4:07 PM, Anita Smith <anitatiedemann at gmail.com>wrote:

> Borge,
> Regarding linked tables for temporary data you can still use SQL Server
> tables for that. I also use that approach quite a lot. You will then have
> to
> add a user field or something so you don't overwrite or delete other users
> temporary data.
> cut ...