UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba Code To Activate Stored Procedure, Access 2016    
 
   
azizrasul
post Oct 22 2019, 08:57 AM
Post#1



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


I have a stored procedure called "ListLeaveapplication".

I have copied the following code from the internet and trying to get my call to the stored procedure to work. I am getting an error on line as indicated below. Anyone have ideas why I'm getting this and whether the code overall will do what I want? The purpose of the stored procedure return value is to produce the recordsource of a form. So I also need to work that out as well.

CODE
Dim cmd As ADODB.Command
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strConnectionString As String, strSQL As String
    Dim StrSproc As String
    Dim Rs As New ADODB.Recordset
    
    strConnectionString = "Provider=SQLOLEDB;Data Source=SQL2008CLS\LEW;Initial Catalog=MedicalLeave;Trusted_Connection=Yes;"
    
    With cnn
        .CommandTimeout = 900
        .ConnectionString = strConnectionString
        .Open
    End With
    
    With cmd
        .ActiveConnection = cnn                    'ERROR HERE (91:Object variable or With block variable not set)
        .CommandType = adCmdStoredProc
        .CommandText = "ListLeaveapplication"
        .Parameters.Append .CreateParameter("@parameter1", adInteger, adParamInput, , LTrim(Str(intCurrentConsultant)))
        .Parameters.Append .CreateParameter("@parameter2", adInteger, adParamInput, , 1)
        .Parameters.Append .CreateParameter("@parameter3", adInteger, adParamInput, , "'" & strDateFrom & "'")
        .Parameters.Append .CreateParameter("@parameter4", adInteger, adParamInput, , "'" & strDateTo & "'")
    End With
    
    With Rs
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmd
    End With
'    Application.StatusBar = "Running stored procedure..."
    Set rst = cmd.Execute


The original line of code in a MS Access 2002 adp database, which I am trying to migrate to MS Access 2016 is

CODE
Me.RecordSource = "Execute dbo.ListLeaveapplications " & LTrim(str(intCurrentConsultant)) & ", 1, '" & strDateFrom & "', '" & strDateTo & "'"



--------------------
Aziz
Go to the top of the page
 
arnelgp
post Oct 22 2019, 09:15 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


you forget to initialize cmd

Dim cmd As New ADODB.Command

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
azizrasul
post Oct 22 2019, 09:43 AM
Post#3



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Great. Changed the declaration to, which made it work.

CODE
Dim cmd As New ADODB.Command

I now get an error on line

CODE
.Parameters.Append .CreateParameter("@parameter3", adInteger, adParamInput, , "'" & strDateFrom & "'")

where the error is 3421:Application uses a value of the wrong type for the current operation.

strDateFrom is 2019-01-01

The stored procedure is

CODE
Me.RecordSource = "Execute dbo.ListLeaveapplications " & LTrim(str(intCurrentConsultant)) & ", 1, '" & strDateFrom & "', '" & strDateTo & "'"
CREATE PROCEDURE [dbo].[ListLeaveapplications] @ConsultantId Int, @LeaveTypeToBeShown int, @Startdate datetime, @Enddate datetime AS BEGIN
SET               NOCOUNT ON DECLARE @ConsultantIdLOCAL INT = @ConsultantId, @StartdateLOCAL DATETIME = @Startdate,
                        @EnddateLOCAL DATETIME = @Enddate, @LeaveTypeToBeShownLOCAL INT = @LeaveTypeToBeShown
                            SELECT      @LeaveTypeToBeShownLOCAL = ISNULL(@LeaveTypeToBeShownLOCAL, 1), @StartdateLOCAL = ISNULL(@StartdateLOCAL,
                                                     '2007/01/01'), @EnddateLOCAL = ISNULL(@EnddateLOCAL, '2017/12/31') IF @ConsultantId IS NOT NULL BEGIN
                                                         SELECT      LeaveApplicationId, StartDate, EndDate, NumberOfDays, B.LeaveTypeDescription, ApplicationTimeStamp,
                                                                                  C.StatusDescription, A.AppliedLate, A.StatusCode
                                                          FROM           tblLeaveApplicationRequests A LEFT JOIN
                                                                                  tblLeaveTypes B ON A.LeaveType = B.LeaveTypeId LEFT JOIN
                                                                                  tblApplicationstatusCodes C ON A.StatusCode = C.StatusCodeid
                                                          WHERE       (@LeaveTypeToBeShownLOCAL = 1 AND ConsultantId = @ConsultantIdLOCAL AND ((Startdate >= @StartdateLOCAL AND
                                                                                  Startdate <= @EnddateLOCAL) OR
                                                                                  (Startdate < @StartdateLOCAL AND Enddate >= @StartdateLOCAL))) OR
                                                                                  (@LeaveTypeToBeShownLOCAL = 2 AND ConsultantId = @ConsultantIdLOCAL AND LeaveType = 1 AND
                                                                                  ((Startdate >= @StartdateLOCAL AND Startdate <= @EnddateLOCAL) OR
                                                                                  (Startdate < @StartdateLOCAL AND Enddate >= @StartdateLOCAL))) OR
                                                                                  (@LeaveTypeToBeShownLOCAL = 3 AND ConsultantId = @ConsultantIdLOCAL AND LeaveType = 3 AND
                                                                                  ((Startdate >= @StartdateLOCAL AND Startdate <= @EnddateLOCAL) OR
                                                                                  (Startdate < @StartdateLOCAL AND Enddate >= @StartdateLOCAL))) OR
                                                                                  (@LeaveTypeToBeShownLOCAL = 4 AND ConsultantId = @ConsultantIdLOCAL AND LeaveType = 4 AND
                                                                                  ((Startdate >= @StartdateLOCAL AND Startdate <= @EnddateLOCAL) OR
                                                                                  (Startdate < @StartdateLOCAL AND Enddate >= @StartdateLOCAL))) OR
                                                                                  (@LeaveTypeToBeShownLOCAL = 5 AND ConsultantId = @ConsultantIdLOCAL AND LeaveType <> 1 AND
                                                                                  LeaveType <> 3 AND LeaveType <> 4 AND ((Startdate >= @StartdateLOCAL AND Startdate <= @EnddateLOCAL) OR
                                                                                  (Startdate < @StartdateLOCAL AND Enddate >= @StartdateLOCAL)))
                                                          ORDER BY StartDate END END

This post has been edited by azizrasul: Oct 22 2019, 09:56 AM

--------------------
Aziz
Go to the top of the page
 
arnelgp
post Oct 22 2019, 10:26 AM
Post#4



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


parameters 3 and 4 are declared as DateTime on your stored proc.



.Parameters.Append .CreateParameter("@parameter3", adDBTimeStamp, adParamInput, , CDate(strDateFrom) + Time(Now()))

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
AlbertKallal
post Oct 22 2019, 11:05 AM
Post#5


UtterAccess VIP
Posts: 2,871
Joined: 12-April 07
From: Edmonton, Alberta Canada


The following should work:
CODE
Sub test5553434()

   Dim strSQL  As String
   strSQL = LTrim(str(intCurrentConsultant)) & "," & _
            "1," & _
            qudateSQL(strDateFrom) & "," & _
            qudateSQL(strDateTo)
  
   With CurrentDb.QueryDefs("qryPass")
      .SQL = "exec ListLeaveapplication " & strSQL
      .ReturnsRecords = False
      .Execute
   End With
  
End Sub



Or, if you wanted to actually specify the paramters and thus not have to worry about “order” of the params, you could use this:

CODE
Sub test5553435()


   Dim strSQL  As String
   strSQL = "@ConsultantId = " & LTrim(str(intCurrentConsultant)) & "," & _
            "@LeaveTypeToBeShown = 1," & _
            "StartDate = " & qudateSQL(strDateFrom) & "," & _
            "EndDate = " & qudateSQL(strDateTo)
  
   With CurrentDb.QueryDefs("qryPass")
      .SQL = "exec ListLeaveapplication " & strSQL
      .ReturnsRecords = False
      .Execute
   End With
  
End Sub


A few notes and points about the above:

No connection string in code – nice.

No having to setup an ado command object – nice.

And second example allows out of order for your params.

And while we are using string concat for the paramets, you STILL get SQL injection and parmater protection.

In fact, when you use t-SQL to call another store proc you be usings exec sp_name “params here”

The other important aspect here is the use of a PT query. My re-link code includes any PT query during the re-link process, so you avoid having any hard coded strings in your application. You want to allow poiting your database to different ones with ease.

And for dates? Well I always built a date formatter, since for JET/ACE you have to use USA format, and for SQL server, then of course “ansi” date format.

So, in all my apps I have qu(), qudate() (for jet)

And

QuSQL(), qudateSQL() for SQL server.

The function qudateSQL() is thus this:
CODE
Public Function qudateSQL(MyDate As Variant) As String

   ' returns a formatted string of date,
   ' format is yyyy-mm-dd
  
   If IsNull(MyDate) = True Then
      qudateSQL = ""
   Else
      ' USE iso date format
      qudateSQL = "'" & Format(MyDate, "yyyy-mm-dd") & "'"
   End If
  
End Function


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
azizrasul
post Oct 23 2019, 04:37 AM
Post#6



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Thanks AlbertKallal, looks pretty good.

Just been diverted to something else. It's always the way just as you are on the cusp of finishing something, you are directed away. Will return to this asap and repost if I have further issues.

--------------------
Aziz
Go to the top of the page
 
AlbertKallal
post Oct 23 2019, 04:28 PM
Post#7


UtterAccess VIP
Posts: 2,871
Joined: 12-April 07
From: Edmonton, Alberta Canada


No worries. As noted, the other suggestions (ADO) are also a fine approach here.

I quite much adopted the above approach and do find it the least effort.
However there are many different approaches that can work.

Good luck!

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Go to the top of the page
 
azizrasul
post Oct 24 2019, 03:31 AM
Post#8



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


How would I amend the code to include a connection? My connection string is

CODE
strConnectionString = "Provider=SQLOLEDB;Data Source=SQL2008CLS\LEW;Initial Catalog=MedicalLeave;Trusted_Connection=Yes;"


When I get to the

CODE
.Execute


it directs me to the SQL server connection. When I select the dsn, it says it's failed.

--------------------
Aziz
Go to the top of the page
 
cheekybuddha
post Oct 24 2019, 05:32 AM
Post#9


UtterAccess VIP
Posts: 11,663
Joined: 6-December 03
From: Telegraph Hill


A quick note, Aziz:

Albert's method is using a "pass-through" query. At the moment it is set not to return any records (.ReturnsRecords = False), which should be set for action queries/procedures carrying out action queries (eg INSERT, UPDATE, DELETE).

Your stored proc appears to select records and so you must set .ReturnsRecords = True

However, not that all recordsets returned via a "pass-through" query will be read-only. You will need code to write any updates back to the server.

Depending on settings, your recordset returned via ADO might be updateable.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
azizrasul
post Oct 24 2019, 06:43 AM
Post#10



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


I'm not very familiar with pass thru queries. I have set the ReturnRecords to True. However I still get an error on the .Execute line even though I have open "qryPass" in designed view and pressed the ODBC link in properties to point to the DSN.
Here is what I now have.

CODE
    Dim cmd As New ADODB.Command
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strConnectionString As String
    Dim Rs As New ADODB.Recordset
    Dim strDateFrom As String
    Dim strDateTo As String
    Dim strSQL  As String
    Dim intCurrentConsultant As Integer
    
    strDateFrom = "01/01/2019"
    strDateTo = "01/01/2021"
    intCurrentConsultant = 1593
    
    strSQL = LTrim(Str(intCurrentConsultant)) & "," & "1," & qudateSQL(strDateFrom) & "," & qudateSQL(strDateTo)
    
    With CurrentDb.QueryDefs("qryPass")
       .SQL = "exec ListLeaveapplications " & strSQL
       .ReturnsRecords = True
       .Execute
    End With

I get error 3065 (Cannot execute a select query).

In the original MS Access 2002 file, the result of the select query became the recordsource of a form, which is what I want in the MS Access 2016 database. Hope that makes sense.

When I run "qryPass" by double clicking, then it produces records.
This post has been edited by azizrasul: Oct 24 2019, 06:45 AM

--------------------
Aziz
Go to the top of the page
 
cheekybuddha
post Oct 24 2019, 07:03 AM
Post#11


UtterAccess VIP
Posts: 11,663
Joined: 6-December 03
From: Telegraph Hill


Since you are trying to get a recordset, use .OpenRecordset instead of .Execute

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
azizrasul
post Oct 24 2019, 08:11 AM
Post#12



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Ah now I understand, the penny has dropped. Many thanks.

--------------------
Aziz
Go to the top of the page
 
AlbertKallal
post Oct 31 2019, 08:42 PM
Post#13


UtterAccess VIP
Posts: 2,871
Joined: 12-April 07
From: Edmonton, Alberta Canada


You code should now look like:
CODE
    Dim strDateFrom As String
    Dim strDateTo As String
    Dim strSQL  As String
    Dim intCurrentConsultant As Integer
    
    strDateFrom = "01/01/2019"
    strDateTo = "01/01/2021"
    intCurrentConsultant = 1593
    
    strSQL = LTrim(Str(intCurrentConsultant)) & "," & "1," & qudateSQL(strDateFrom) & "," & qudateSQL(strDateTo)

    dim rst   as DAO.RecordSet
    
    With CurrentDb.QueryDefs("qryPass")
       .SQL = "exec ListLeaveapplications " & strSQL
       .ReturnsRecords = True
       set rst = .OpenRecordSet()
    End With


I don't have a ADO example, but hopefully your only reason to use ADO was the desire to use/call store procs. I tend to still use DAO even with SQL server.

so, for example, say we wanted to launch a report based on above?
Then:
CODE
    With CurrentDb.QueryDefs("qryPass")
       .SQL = "exec ListLeaveapplications " & strSQL
       .ReturnsRecords = True
    End With

    docmd.OpenReport "some report",acViewPreview

In above it would be assumed that the report is based on qryPass.

As note how the posted code does not need to declare any connection strings etc. It is assumed that you set the connection string in the PT query. So, in my table re-link code, I also re-link any PT query also. As a result, you really never have to deal with connection strings or even a connection object in code. This also means you don't have to introduce ADO into your application. If you already have been using ADO, then not a big deal and don't worry. However, I tend to prefer not having to introduce ADO to "just" use some PT queries if that is the only reason for using ADO.

R
Albert


Go to the top of the page
 
azizrasul
post Nov 1 2019, 04:28 AM
Post#14



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Thanks Albert.

--------------------
Aziz
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th November 2019 - 04:52 PM