pressMac
asked on
open DAO recordset based on Stored Procedure
I am trying to open up a recordset based on a stored procedure, but get an ODBC call failed.
I searched around, but there are so many variations on the syntax that i cannot put it together.
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
I searched around, but there are so many variations on the syntax that i cannot put it together.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ODBC Call Failed generally means you can't connect to the server for some reason. Are you certain your connect string is correct?
Does the user have permission to call the stored procedure?
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.
How To Get More Information on the ODBC Call Failed Error
http://support.microsoft.com/kb/161288
Jim.
ASKER
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.
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
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
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
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.
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