Failing to create a recordset from a stored procedure

Are the dates defined as date or text fields in the database?

If they are defined as date fields, then you need to pass your parameters with date variables.

If they are defined as text fields, then you need to pass your parameters in the format in which they are recorded in the database.

Perhaps I should ensure that I have the basics set up before worrying about the parameters.

I have just setup a new stored proc, a simple select of two fields from a small table.  It returns about 100 recs.

I have set up a querydef in the MSAccess 2010 app.  Tis lists the 100 records.

I now want to run VBA to run the querydef and to put the resultant list in a recordset. but still cannot get this to work.

Private Sub cmdDownLoad_Click()
' Simple test of running a pass through querydef to fill a recordset
  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim qdf As DAO.QueryDef
  Dim RecCount As Long
  Dim strsql As String
  
  Set db = CurrentDb()
  Set qdf = db.QueryDefs("qspt_test4")
  qdf.SQL = "EXEC sp_test4"
  Set rst = qdf.OpenRecordset(dbOpenSnapshot)
  
  Debug.Print rst.RecordCount
  RecCount = rst.RecordCount
  rst.MoveFirst
  
Exit_Sub:
  Exit Sub
    
ErrHandle:
    DoCmd.Hourglass False
    msg = "NZ MYOB Download Problem" & vbCrLf & msg2 & vbCrLf & Err.Description
    MsgBox msg, vbExclamation, "Form NZ MYOB"


End Sub

Select allOpen in new window

I have created coding to read a simple stored procedure.  It uses the ADODB command call.  It may not be as efficient as using DAO, but it does work.

Tomorrow I will code the call to the stored procedure that will involve date ranges.  If anybody has a better solution in the next 12 hours, do let me know.  I will post the final coding result.

Private Sub cmdDownLoad_Click()
' Simple test of running a pass through querydef to fill a recordset
  Dim db As DAO.Database
  Dim rst As ADODB.Recordset
  Dim qdf As DAO.QueryDef
  Dim RecCount As Long
  Dim strSql As String
  Dim cmd As New ADODB.Command

  cmd.ActiveConnection = "Provider=SQLNCLI10;Password=xxxxxxx;Persist Security Info=True;User ID=yyyyyy;Initial Catalog=zzzzzzz;Data Source=abc.def.ghi.au"
  cmd.ActiveConnection.CursorLocation = adUseClient
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "sp_test4"  '--name of stored proc -- not passing params for this sample
  Set rst = cmd.Execute
  RecCount = rst.RecordCount
  Debug.Print rst.RecordCount
  rst.MoveFirst
  Do Until rst.EOF
    Debug.Print rst.Fields.Item("agentid")
    Debug.Print rst.Fields.Item("agent")
    rst.MoveNext
  Loop
  rst.Close
  cmd.ActiveConnection.Close
  
Exit_Sub:
  Exit Sub
    
ErrHandle:

End Sub

Select allOpen in new window

Having to explain the problem to others helps to clarify the issue.  Hope this helps someone else