Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Failing to create a recordset from a stored procedure

Posted on 2014-10-09
5
111 Views
Last Modified: 2014-10-15
I have an Access system that is linked to an MS SQL server (2008).  I have created stored procedures on the server that I want to extract as read only recordset.
The code is not filling the recordset with any records.  The date is passed as '01-Sep-2014'.

Any suggestions?

Private Sub cmdDownLoad_Click()
' Extract the data with selection criteria then download
  Dim DateLow, DateHigh As Variant
  Dim wdate As Date
  Dim rst As DAO.Recordset
  Dim RecCount As Long
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
'  Dim DateLow As String
'  Dim DateHigh As String
  
  On Error GoTo ErrHandle
  
  DoCmd.Hourglass True
  
     
'    Call the stored procedures
     Set db = CurrentDb()
     Set qdf = db.QueryDefs("qspt_NZ_MiscFile")

     qdf.SQL = "EXEC sp_NZ_MiscFile " & _
               DateLow & "," & DateHigh

               
     Set rst = qdf.OpenRecordset()
  
  Debug.Print rst.RecordCount
  RecCount = rst.RecordCount

Open in new window

0
Comment
Question by:Nigel Keith-Walker
  • 4
5 Comments
 
LVL 40
ID: 40372225
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.
0
 

Author Comment

by:Nigel Keith-Walker
ID: 40372355
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

Open in new window

0
 

Author Comment

by:Nigel Keith-Walker
ID: 40372826
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

Open in new window

0
 

Accepted Solution

by:
Nigel Keith-Walker earned 0 total points
ID: 40374298
I have successfully extracted and filled a recordset from a stored procedure using ADODB commands.

As promised, here is the code:

Private Sub cmdDownLoad_Click()
' Simple test of running a pass through querydef to fill a recordset
'  Dim db As DAO.Database
  Dim rst As New ADODB.Recordset
  Dim cmd As New ADODB.Command
  Dim Parm As New ADODB.Parameter
'  Dim qdf As DAO.QueryDef
  Dim RecCount As Long
  Dim strSql As String
  Dim DateLow, DateHigh As String
  
  DateLow = "1-Sep-2014"
  DateHigh = "1-Sep-2014"

  cmd.ActiveConnection = "Provider=SQLNCLI10;Password=xxxxxxx;Persist Security Info=True;User ID=yyyyyyy;Initial Catalog=abcdef;Data Source=abc.def.ghi.au"
  cmd.ActiveConnection.CursorLocation = adUseClient
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "sp_NZ_MiscFile"  '--name of stored proc --
  cmd.Parameters.Refresh
  cmd(1) = DateLow
  cmd(2) = DateHigh
  
  Set rst = cmd.Execute
  RecCount = rst.RecordCount
  Debug.Print rst.RecordCount
  rst.MoveFirst
  Do Until rst.EOF
    Debug.Print rst.Fields.Item("IME_Ref")
    Debug.Print rst.Fields.Item("Send_ref")
    rst.MoveNext
  Loop
  rst.Close
  cmd.ActiveConnection.Close
  
Exit_Sub:
  Exit Sub
    
ErrHandle:

End Sub

Open in new window

0
 

Author Closing Comment

by:Nigel Keith-Walker
ID: 40381708
Having to explain the problem to others helps to clarify the issue.  Hope this helps someone else
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question