Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

open DAO recordset based on Stored Procedure

Posted on 2015-01-20
6
Medium Priority
?
82 Views
Last Modified: 2015-03-04
I am trying to open up a recordset based on a stored procedure, but get an ODBC call failed.

Function GetGP(crit_DateStart As Date, crit_DateEnd As Date, crit_Period As String, crit_Salesman As String)
If myErr.ErrorTrapping = True Then
   ' On Error GoTo updateerr
End If
DoCmd.Echo False


Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim sConnect As String



sConnect = "ODBC;Description=nt80;DRIVER=SQL Server Native Client 11.0;SERVER=NT80\SQLEXPRESS;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=zABC_v2;"

Set dbs = CurrentDb
'dbs.CreateQueryDef ("tempGPSum")
Set qdf = dbs.QueryDefs("tempGPSum")


   crit_Salesman = "Tom Jones"
   crit_DateStart = #1/1/2014#
   crit_DateEnd = #12/31/2014#
   crit_Period = "Q"
   
        
        'Eventually create code to parse the dates and period into this quarter, this year, this month...
    

  
sSQL = "exec dbo.sp_GP_BySalesman @crit_Salesman = '" & crit_Salesman & "', @crit_DateStart = " & crit_DateStart & ", @crit_DateEnd = " & crit_DateEnd & " '"

qdf.ReturnsRecords = True
qdf.Connect = sConnect


qdf.sql = sSQL


   Debug.Print sSQL

'it fails on the next line.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
rst.MoveFirst
Debug.Print rst!Salesman
rst.Close
Set rst = Nothing


    

DoCmd.Echo True
Exit Function
updateerr:
    DoCmd.Echo True
    DispError "Get GP", "Get GP"
End Function

Open in new window


I searched around, but there are so many variations on the syntax that i cannot put it together.
0
Comment
Question by:pressMac
[X]
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
6 Comments
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 1500 total points
ID: 40559985
The SP should return records. It does?
Also, can you post here the result from Debug.Print sSQL?
0
 
LVL 85
ID: 40560002
ODBC Call Failed generally means you can't connect to the server for some reason. Are you certain your connect string is correct?
0
 
LVL 14

Expert Comment

by:Mark Bullock
ID: 40560107
Does the user have permission to call the stored procedure?
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 58
ID: 40560417
Make sure you enumerate the errors collection to get all the errors the ODBC driver may be providing:

How To Get More Information on the ODBC Call Failed Error
http://support.microsoft.com/kb/161288

Jim.
0
 

Author Comment

by:pressMac
ID: 40560511
Yes, connection strings are good.

I had several problems.

First, i was passing all four of the passed variables all the way to the stored procedure, but it only takes 3 parameters.

The crit_Period is to be used to figure out what date range to actually send.

Second, my passed parameters were in the wrong order as compared to the stored procedure.

Third, one my access variable names were not the same as my store procedure parameters.

So, it was  sloppy start.

To debug it, i pasted the debug.print of the sql to passed.  Then i pasted that into studio manager, it was pretty obvious that point what was wrong.

Function GetGP(crit_Salesman As String, crit_DateStart As Date, crit_DateEnd As Date, crit_Period As String) As Currency
If BUS.ErrorTrapping = True Then
   ' On Error GoTo updateerr
End If
DoCmd.Echo False


Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim sConnect As String

' bak sConnect = "ODBC;Description=nt80;DRIVER=SQL Server Native Client 11.0;SERVER=NT80\SQLEXPRESS;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=zABC_v2;"

sConnect = "ODBC;DSN=NT80_v2;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=zABC_v2;"
            'ODBC;DSN=NT80_v2;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=zABC_v2
Set dbs = CurrentDb
'dbs.CreateQueryDef ("tempGPSum")
Set qdf = dbs.QueryDefs("tempGPSum")

  ' these are temporary overrides.
   'crit_Salesman = "Tom Jones"
   crit_DateStart = #1/1/2014#
   crit_DateEnd = #12/31/2014#
   crit_Period = "Q"  ' never passed to sql server
   
        
        'Eventually create code to parse the dates and period into this quarter, this year, this month...
    

  
sSQL = "exec dbo.sp_GP_BySalesman @DateStart =  '" & crit_DateStart & "', @DateEnd = '" & crit_DateEnd & "', @Crit_Salesman = '" & crit_Salesman & "'"

qdf.ReturnsRecords = True
qdf.Connect = sConnect


qdf.sql = sSQL

    Debug.Print qdf.Name
    
   Debug.Print sSQL


Set rst = qdf.OpenRecordset(dbOpenSnapshot)
If Not rst.EOF Then

    rst.MoveFirst
        Debug.Print rst!Salesman, rst!sumgp
        GetGP = rst!sumgp
   
End If

     rst.Close
    Set rst = Nothing
    

DoCmd.Echo True
Exit Function
updateerr:
    DoCmd.Echo True
    DispError "Get GP", "Get GP"
End Function

Open in new window


the debug.print now looks like  this:

xec dbo.sp_GP_BySalesman @DateStart =  '1/1/2014', @DateEnd = '12/31/2014', @Crit_Salesman = 'Tom Jones'

I guess i figure this one out, but i will leave it open for a response on how to derive a current quarter or current month from the dates and crit_Period sent.  

For example, GetGP("1/1/2013#, "12/31/2014#, "tom Jones", "Q")

figure what the date range of  the Quarter is that ends in 12/31/2014
0
 
LVL 58
ID: 40560552
Something which may be of a little help in the future.  It's written with ADO, but it's a nice way to check what's being seen for a stored procedure from the Access side.  Note that you'll need to change the connection object.

FWIW
Jim.

Sub GetSPParameters(strSPName As String)

          ' Return the attributes of the parameters of a stored procedure.
          ' From the debug window:
          ' Call GetSPParameters("qrySMGetPeriod")
          '
          Dim strConnect As String
          Dim cnn As ADODB.Connection
          Dim cmd As ADODB.Command
          Dim prm As ADODB.Parameter

10        Set cnn = New ADODB.Connection
20        cnn.Open "DSN=SYS"

30        Set cmd = New ADODB.Command
40        cmd.ActiveConnection = cnn
50        cmd.CommandText = strSPName
60        cmd.CommandType = adCmdStoredProc

70        cmd.Parameters.Refresh
80        For i = 0 To cmd.Parameters.Count - 1
90            Debug.Print "Parameter: " & i
100           Debug.Print "     Name: " & cmd.Parameters(i).Name
110           Debug.Print "     Type: " & cmd.Parameters(i).Type
120           Debug.Print "Direction: " & cmd.Parameters(i).Direction
130           Debug.Print "     Size: " & cmd.Parameters(i).size
140           Debug.Print "   Attrib: " & cmd.Parameters(i).Attributes
150           Debug.Print "    Value: " & cmd.Parameters(i).Value
160           Debug.Print ""
170       Next i

180       cnn.Close
190       Set cnn = Nothing

End Sub

Open in new window

0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 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