ADO to SQL Server for reporting 1

[LarrySteele]

Currently working on an Access project with clients connecting to SQL Server 2008.

The network is very slow, so need to minimize traffic to/from the server. Additionally, the system contains personally identifiable information, so data access control is critical. Another issue of concern is sql injection attacks. With these three concerns, I've chosen to use ADO to execute SP's, passing criteria as parameters.

This method has worked well for the forms - data access is efficient, sql injection is buffered, and no people data is stored on the client.

Yesterday I started working on reports and hit a wall as to how to create an unbound report w/ subreports using parametrized procs.

My current work around is to run the procs, and store the data in local usys tables. This works, but it violates my goal of not have any local tables w/ server data.

Is there a better method for getting the data from the server for my reports?

As always, thanks for any suggestions.

- Larry

 

[Remou]

Do recordsets not work?

http://support.microsoft.com/kb/281998/EN-US/

http://lessthandot.com

 

[dhookom]

I would probably use a little DAO code to change the SQL property of saved pass-through queries.

Duane
Hook'D on Access
MS Access MVP

 

[LarrySteele]

Remou - recordsets do not work on reports:

http://support.microsoft.com/kb/287437

Duane - I was considering attempting a PT query. The biggest problem I see reported is passing the parameters to the query. Is there a way to create a PT query in such a way that I can pass criteria as parameters from VBA that are sent to the server as parameters? If so, would you mind offering an example?

Thanks to both for your responses,
Larry

 

[dhookom]

Assuming you have a form with a couple text boxes for start and end dates that need to be passed to the stored procedure "spMySP" which is called from pass-through query
"qsptMyPT". Your code might look like:

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim strSQL as String
Set db = CurrentDb
Set qd = db.QueryDefs("qsptMyPT")
strSQL = "spMySP '" & Me.txtStartDate & "', '" & Me.txtEndDate & "'"
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing

I generally include a small function to change the SQL of a saved query that I pass in the query name and the SQL.

Sub ChangeSQL(pstrQuery As String, pstrSQL As String)
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(pstrQuery)
    qd.SQL = pstrSQL
    Set qd = Nothing
    Set db = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[LarrySteele]

Duane,

Thanks for the example. Am I reading correctly that the syntax for passing parameters to a proc is proc name, followed by a comma-delimited list of arguments? If so, then the querydef would look like this:

spMySP '2009-JAN-01', '2009-JUN-01'

If so, then I think that's a solution I can certainly live with. It's also very similar to a suggestion from MS:

http://support.microsoft.com/kb/131534

though their example was for VBA rather than a PT query.

Thanks again, looking forward to getting home and trying this out. Though I'm not looking forward to the ride home - some really heavy rain just started. Just in time for rush hour.

 

[LarrySteele]

Duane,

Just finished trying your syntax, and it worked as advertised. Sure wish I'd seen this method previously, it'd have save hours and hours and hours of coding.

One more item for my toolbox.

Thanks again,
Larry

 

[dhookom]

Glad to hear it worked for you.

Duane
Hook'D on Access
MS Access MVP

 

[LarrySteele]

The problem that just won't go away...

So after getting my parent reports to read from my parametrized procs, I started tackling the next step - getting my child reports to read from my parametrized procs.

Here's the error message that popped up:

"You can't use a pass-through query or a non-fixed column crosstab query as a record source for a subform or subreport" According to MSKB 288633, "This error occurs if you have LinkMasterFields and LinkChildFields defined for the subform or subreport." Well of course I have link fields defined - I'm trying to list the phone numbers and email addresses for people.

I spent some quality time with Google and haven't been able to find an acceptable work-around.

I'm a bit baffled why there would be such a restriction.

Have you seen a work-around? If not, I may have to resort to the usys tables, no matter how much I want to stay away.

Thanks again,
Larry

 

[dhookom]

Do you get the error when designing or opening the report? I'm not sure there is a good work around with a temp or linked table.

Duane
Hook'D on Access
MS Access MVP

 

[LarrySteele]

When opening the report.

 

[dhookom]

You may need to use one of the alternatives.

Duane
Hook'D on Access
MS Access MVP

 

[LarrySteele]

Thanks. Doing that right now. Not happy, but at least we're moving forward.