Report from SQL Server Recordset

[jpember]

Can anyone point me in the right direction to create a report based on a recordset built from a SQL Server Stored procedure? I am using Access 2000 ADO....

Thanks in advance,
Jacqui

 

[wtmckown]

As a static recordsource for a report an ADO recordset populated by calling a SQL stored procedure is no different from an ADO recordset populated by passing a SQL statement to the JET Engine. You just can't rely on the Access report wizard and must create your report manually, referring to the elements of you recordset to supply values to your report objects.

 

[jpember]

Yes, so I have read. However, when I create the recordset in the Report_Open event and then try and assign it to the reports recordset/recordsource property, I get errors such as invalid property or type mismatch etc.

The problem is that there is plenty of statements around to confirm that this can be done, but I have yet to see some working code.

Anyone who can provide me with a sample will get a squillion stars....

Thanks again.

 

[wtmckown]

I could be wrong but I don't think it is possible to set an Access report's recordsource property to an existing recordset object. You could set the recordsource property to an SQL call to the SQL server however. If you want to call the stored procedure instead and populate a recordset object you could then loop the recordset and insert records into a temporary local table and then set the report recordsource to the temp table.

 

[coco86]

You can create a passthrough query in access to execute the stored procedure. The SQL is essentially:

storedprocedurename parameter1, parameter2, etc...

if you need to pass parameters dynamically then do this:

Dim qdf As QueryDef
Dim strTemp as string

strTemp = "storedprocedurename " & "parameter1, parameter2"

currentdb.QueryDefs("PassThroughQueryName&quot
qdf.SQL = strTemp
qdf.Close

 

[jpember]

Thanks for the info guys. Basically because I'm prototyping in Access, i.e. front and back-end's are Access.mdb's, I can't use coc86's suggestion until I move the database into SQL Server - Access won't let me do a pass-through query to another Access database.

In the meantime I was doing what wtmckown has suggested with the temp table, but it just seemed like a bit of a messy solution. But if there is no other way, then so be it.

Thanks again for your input,
Jacqui

 

[GregGol]

Jacqui (and others),

I had the same problem and concluded that you can't use a recordset as the record source for a report. The temp table works and it was not too messy. My queries were extremely complicated, combining the user input off three lists (time period, company subsidiary and topic) and then applying the selected records to a variety of possible report recipients. I could not (I believe) simply link a query and a report because I found no way to do the complex query I had in a "Query" per se, even going into the SQL view.

Disclaimer -- you mentioned a server. It is not necessary in my work for me to appreciate the fine points of storage on a server. So, nothing in this posting covers that aspect.

In essence, I use the user input to construct the SQL SELECT statement to select data from the main table. Then, I open an ADODB recordset to hold the selected records. CAUTION - since I manipulate the data temporarily for the report, I opened the recordset Static not Dynamic. The latter pushes the changes through to the source table and I didn't want that at this stage. I made a template table, mostly a duplicate of the main table, sans some fields I knew I would not need. I open it and delete any data from the last report. Then, I use SQL INSERT to store the selected records in the temp table. Then, close the recordset, SELECT all the records into a second recordset (open Dynamically), and manipulate the data. The changes are made to the temp table, too, which is precisely what I want. The temp table has been defined to be the source for the report so I open the report. And the manipulated data is printed.

Here are the major steps of the code (note that the below statements are not complete syntax or logic):


Public strSQL As String
-----------------------

Public Sub Report_Open(Cancel As Integer)
Dim conADOConnection As Connection
Dim conADO As New Connection
Set conADOConnection = CurrentProject.Connection
conADO.ConnectionString =
CurrentProject.BaseConnectionString

On Error GoTo err_expt...

DoCmd.OpenForm "XYZSelection", acNormal, , , acFormAdd, acDialog
' XYZSelection is a separate procedure which uses a form
containing 2, 3, or 4 listboxes for picking criteria.
Which list boxes are visible depends on the type of
report, which the user has selected already in a
PrintReportDialog box.

' next, the program constructs the SQL
' I have a SELECT CASE set for each criterion preceded
by the list of fields which is common to all types and
followed by the ORDER BY clause to set the order,
again, common to all types

strSQL = "SELECT company, topic, etc FROM " & _
&quot;maintable WHERE close_open <> 'C'&quot;

' now get portion of SQL statement relating to company
Select Case Company
Case &quot;All&quot;
' nothing to do here - all companies chosen
Case &quot;Subs&quot;
strSQL = strSQL & &quot; AND company <> 'parent'&quot;
Case Else
If strCompany = &quot;parent&quot; Then
strSQL = strSQL & &quot; AND (company = '&quot; & Company & _
&quot;' OR company = 'All')&quot;
Else
strSQL = strSQL & &quot; AND (company = '&quot; & strCompany & _
&quot;' OR rep_company = 'All' OR company = 'Subs')&quot;
End If
End Select

Dim objRSetRpt As New ADODB.Recordset
objRSetRpt.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockOptimistic

DoCmd.OpenTable &quot;PrntTmp&quot;, acViewNormal, acAdd

Do Until objRSetRpt.EOF
strSQL = &quot;INSERT INTO PrntTmp (fields) &quot;
strSQL = strSQL & &quot;VALUES('&quot;
strSQL = strSQL & objRSetRpt.Fields.Item(0) & &quot;','&quot;
etc. through 9 more fields
DoCmd.RunSQL strSQL
objRSetRpt.MoveNext
Loop

strSQL = &quot;SELECT company, topic, etc. FROM PrntTmp&quot;

Dim obj2RSetRpt As New ADODB.Recordset
obj2RSetRpt.Open strSQL, CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

DoCmd.OpenReport &quot;report&quot;, acViewPreview

End Sub

Good luck. I'm new to Access in 2002 (have Access 2000) and I've really struggled with it. If there's an easier way than the above, it eluded me.

GregGol
greggol@prodigy.net