ADO problem with Access Report

[Mo]

Using Access 2003 ADP, with unbound forms/reports and stored procedures
on SQL Server.

I'm getting various errors trying to open a report an setting it's
recordset property. The error occurs when I use 'Set Me.Report.Recordset
= rst'. I've tried various different variations of this line
(recordsource instead of recordset etc, etc) and can't seem to get
beyond this line.

The stored procedure is tested and works fine. The same code (or
something very similar) will work with no problems in a form. So what is
it about using a report that raises an error? Anyone have any ideas?

Just to add some more information:

I'm beginning to think that this is not possible with ADO as whatever I
try (e.g. 'Set Me.Report.Recordsource = rst.Source', etc, etc), I get an
error message.

Strangely enough, it works if you use DAO and then
'Me.Report.RecordSource = strSQL'.

Has anyone come across anything like this with reports? Any suggestions?

TIA for any help.


<code>
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim paramSY As ADODB.Parameter

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

With cmd

..CommandType = adCmdStoredProc
..CommandText = "SPMissingData"
..ActiveConnection = cnn

End With

Set paramSY = cmd.CreateParameter("@sy", adSmallInt, adParamInput)
paramSY.NAME = "@sy"
paramSY.Direction = adParamInput
If IsNull(Forms!frmMissingDataAll!txtYear) _
Then paramSY.Value = Null Else paramSY.Value =
Forms!frmMissingDataAll!txtYear
cmd.Parameters.Append paramSY

rst.Open cmd, , adOpenStatic, adLockOptimistic

If rst.RecordCount <> 0 Then

Set Me.Report.Recordset = rst

End If

rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
</code>

 

[Sylvain Lafontaine]

To my knowledge, you cannot set the record source of a report to an ADO
recordset. You can do that with forms but not for reports. This is a
design limitation of Access.

Also, I'm not sure what you mean when you say that you can make it works
with DAO.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Mo]

To my knowledge, you cannot set the record source of a report to an ADO
recordset. You can do that with forms but not for reports. This is a
design limitation of Access.

Also, I'm not sure what you mean when you say that you can make it works
with DAO.

Thanks for the reply. Sorry about the confusion.

I mean that if I use bound controls on the report as oppossed to unbound
ones, I am able to do the following:


<code>
Dim db As DAO.Database
Dim rst As Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT KASE, PrintTag, REFNO, LAB, TRISOMY21, TRISOMY13, "
strSQL = strSQL & "TRISOMY18, CSNM, KARY, LD, LM, LY, ED, EM, EY, PFULL,
PCR, "

strSQL = strSQL & "FROM tblscreening "

strSQL = strSQL & "WHERE SY= " & Forms!frmmissingdataall!txtYear & " "

etc...

Me.Report.RecordSource = strSQL

etc...
</code>

 

[Sylvain Lafontaine]

The easiest way to use a parameterised stored procedure (SP) with an ADP's
report is first to set the Record Source to the name of your stored
procedure (in design mode), set the Record Source Qualifier to dbo and set
the Input Parameters to point toward controls (or public variables) of a
opened form (which could be hidden if you need so):

@sy datetime = Forms!frmMissingDataAll!txtYear,
@SecondParameter datetime = Forms!frmMissingDataAll!txtSecondParameter

These values must be set before the report is opened. Also notice that the
order of the parameters must be identical to the order of the parameters as
declared in the SP and that the separator between each parameter is not
necessary the comma but could be also the semi-comma (; depending on the
regional settings of your computer.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)