How to set RecordSource from ADO RecordSet?

[Steve Dinse]

How do I set a form or report's RecordSource property(Me.RecordSource) to
the RecordSet that I get when using ADO to open a RecordSet from a stored
procedure in SQL Server 7? So far my code looks like this:

Sub Report_Open(Cancel As Integer)

Dim cnn As ADODB.CONNECTION
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnStr As String
Dim prm As ADODB.Parameter
Dim prmstr As String

Set cnn = New ADODB.CONNECTION
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
prmstr = Forms![frmImport]![cboCurPer]

cnStr = "driver={SQL Server};server=ACSPA07;database=Actuals;UID=;PWD="
cnn.ConnectionString = cnStr
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandText = "spUnMappedLabr"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("Period", adInteger, adParamInput, , prmstr)
cmd.Parameters.Append prm

rst.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

Now that the RecordSet is open, how do I set Me.RecordSource = rst? Seems
that this should be simple but the simple "Me.RecordSource = rst" gives a
"Type mismatch" compile error.

BTW, this is in Access 2000 mdb file.

Any clues appreciated!
Steve

[Turtle]

You may be able to do this with
Set Me.recordset=rst
But you should be aware that if you bind an Access form to an ADO recordset,
it will not be updatable.

HTH
- Turtle

"Steve Dinse" <din...@mail.northgrum.com> wrote in message
news:GBnDIJ.4I%mot...@herald.northgrum.com...

[Steve]

Thanks, but RecordSet is not an option - only RecordSource(which won't
work). One other dead end I have heard of was to use Me.RecordSource =
rst.Name. The problem here is that there is no "Name" property available in
this instance.

Any more ideas?

Steve

"Turtle" <macde...@mindspring.com> wrote in message
news:9b2n87$b68$1...@nntp9.atl.mindspring.net...

[Turtle]

I suggest you read the entry for "Recordset Property" in the Access 2000
Help file.

In Access 97, the Recordset property of a form was not exposed; in Access
2000, it is available through VBA only.

Perhaps you would like to explain why it is "not an option"?
- Turtle

"Steve" <din...@mail.northgrum.com> wrote in message
news:GBopzJ.Jtn%mot...@herald.northgrum.com...

[Steve]

Recordset isn't an option when typing rst. and then you get the dropdown box
showing all of the options for rst. If I use Recordset anyway, compile
seems to accept it but when I load the form, I get Run-time error '2465'
Application-defined or object-defined error.

Also, in the Access 2000 help file, under Recordset property, you'll notice
that when you click on "Applies To", it only lists "Form Object". I need to
get this recordset into a Report.

I'm thinking that this may be easier under DAO, but there are complications
in running a stored procedure with input parameters. From what I've found
on DAO, it doesn't really support stored procedure execution, but there may
be some work arounds.

Thanks for the suggestions.

Still lookin',

Steve
"Turtle" <macde...@mindspring.com> wrote in message

news:9b5h49$e68$1...@nntp9.atl.mindspring.net...

[Turtle]

Sorry!

You originally said a form or report.

AFAIK you are correct - a report doesn't have an exposed recordset property.

You can create a query without parameters and pass your criteria in the
DoCmd.OpenReport statement.

HTH
- Turtle

"Steve" <din...@mail.northgrum.com> wrote in message

news:GBqtxF.319%mot...@herald.northgrum.com...