Form with Recordset from SQL SP

[jobu171980]

I am trying to set a form's recordset to a recordset returned from a stored procedure in SQL. And I am getting the error "The object you entered is not a valid Recordset property."

Any help will be gladly appreciated.
I am using Access 2010.

Private Sub Form_Open(Cancel As Integer)

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim stSQL As String

stSQL = "EXEC StoredProcedure " & Forms("Form1").Text0

Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Database;" & _
"Data Source=SQLServer"

Set con = New ADODB.Connection

With con
.Open stADO
.CommandTimeout = 0
Set rs = .Execute(stSQL)
End With

Set Me.Recordset = rs

Exit_FormOpen:
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
Exit Sub

Error_FormOpen:
MsgBox Err.Description, vbCritical
Resume Exit_FormOpen

End Sub

 

[PHV]

Shouldn't a Form's recordset be a DAO.Recordset object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vbajock]

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'Create a new ADO Connection object
Set cn = New ADODB.Connection

'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "MySQLServer"
.Properties("User ID").Value = "sa"
.Properties("Password").Value = ""
.Properties("Initial Catalog").Value = "NorthwindCS"
.Open
End With

'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM Customers"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End Sub

Add the following code to the UnLoad event of the form:
Private Sub Form_Unload(Cancel As Integer)
'Close the ADO connection we opened
Dim cn As ADODB.Connection
Set cn = Me.Recordset.ActiveConnection
cn.Close
Set cn = Nothing
End Sub

 

[jobu171980]

Thanks for the help.