Error setting VBA Recordset of form from ADODB.recordset

Question

I've created a function using VBA in MS Access 2010 to execute SQL server stored procedure and return value in ADODB.Recordset Object. However, I'm not able to set the MS Access form RecordSource or Recordset with the recordset that was return from ADODB connection.

Below's you'll find the code excerpt:

Dim objRs As ADODB.Recordset
Set objRs = call_proc("mySQLProc", "param")
Set Forms("form1").Recordset = objRs

Function header of call_proc:

Public Function call_proc(procName As String, procVal As String) As ADODB.Recordset

If I iterate through the objRS and do a Debug.Print I am able to see all the records. So I know the data is there. Just don't know how to fix the error of binding the data to the form. The line of code below returns error:

Set Forms("form1").Recordset = objRs

Any suggesting kindly accepted. Thank you in advance.

Just an FYI, Debug.Print TypeName(objRS) does return Recordset — codeBarer
See this resource on binding forms to ADO recordsets. It might involve the connection OLEDB/ODBC drivers and type of recordset (i.e., adLockOptimistic, adOpenDynamic). Please show your complete connection code. — Parfait

codeBarer codeBarer · Accepted Answer · 2016-02-28T16:36:05

Fixed it. The issue was in my call_proc function. When I opened the ADODB.Recordset I didn't set the cursor location. See code below where I added "' <---#####ADD THIS"

Public Function call_proc(procName As String, procVal As String) As ADODB.Recordset

    ' Initialize variables.
    Dim cn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objParm1 As New ADODB.Parameter
    Dim objRs As New ADODB.Recordset
    Dim ServerName As String, DatabaseName As String


   ServerName = "YourServerName"
   DatabaseName = "YourDatabaseName"

   ' Specify the OLE DB provider.
   cn.Provider = "sqloledb"

   ' Set SQLOLEDB connection properties.
   cn.Properties("Data Source").Value = ServerName
   cn.Properties("Initial Catalog").Value = DatabaseName
   cn.CursorLocation = adUseClient ' <---#####ADD THIS

   ' Windows authentication.
   cn.Properties("Integrated Security").Value = "SSPI"


  ' Set CommandText equal to the stored procedure name.
   objCmd.CommandText = procName 
   objCmd.CommandType = adCmdStoredProc

  ' Open the database.
  cn.Open

  objCmd.ActiveConnection = cn

  ' Automatically fill in parameter info from stored procedure.
  objCmd.Parameters.Refresh



  ' Set the param value.
   objCmd(1) = procVal


  Set call_proc = objCmd.Execute
End Function

Error setting VBA Recordset of form from ADODB.recordset

1 Answers