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
> Triple Sp Call, Any Versions    
 
   
LilAnnCC1
post Jun 2 2020, 11:20 AM
Post#1



Posts: 873
Joined: 31-May 04
From: Wisconsin, USA


Hello all! Using SQL 2017 and Access 365 as front end.

I have a stored procedure that works perfectly in SQL server, but when I try to run it in Access--it appears to run 3 times instead of once.

Here is my stored procedure:
CODE
Alter Procedure spJobTransfer
@ID int,
@Total decimal(10,2)

AS

SET NOCOUNT ON;

DECLARE @Return tinyint
--0 = Success
--1 = Record Exists

    If (SELECT Count(*) FROM tJobTransfer j WHERE j.JTRID=@ID) <> 0
        BEGIN
            SET @Return=1
            --Record exists--cannot do transfer                
        END

    ELSE    
        BEGIN
            Set @Return=0

            Insert INTO tJobTransfer(JTRID, FromJob, ToJob, Cost, Comment, eMail)
            SELECT r.ID, r.From_Job, r.To_Job, @Total, r.Reason, 1
            FROM tJobTransferRequest r
            WHERE r.ID=@ID
        END

SELECT @Return as Results



And this is my VBA code:
CODE
Private Sub cmdTransfer_Click()
On Error GoTo ErrorHandling_Error
'Gather information
    Dim Total As Double
    Dim JTR As Long
    Dim Employee As String
    Dim strTo As String
    Dim strCC As String
    Dim strSubject As String
    Dim strBody As String
    Dim x As Integer
    
    JTR = Me.ID
    Total = Me.sf_JobTransferRequestDetail!txtSumTotal.Value

    With CurrentDb.QueryDefs("qPassR")
        .SQL = "Exec spJobTransfer " & JTR & "," & Total
    End With
    
    x = DLookup("Results", "qPassR")
'Debug.Print x

    If x = 1 Then
        MsgBox "This transfer has already been done.", vbInformation, "Transfer exists"
        Exit Sub

    Else
'Send Email out for successful transfer
        strTo = EmailGroup(14)
        strCC = ""
        strSubject = "Job Transfer Completed in database. Job Request # " & JTR
        strBody = "A Job transfer has been processed from Job " & Me.From_Job.Column(1) & " to job " & Me.To_Job.Column(1) & " for $" & Total & " as of today."
'Debug.Print strSubject
'Debug.Print strBody

        Call SendEmail(strTo, strCC, strSubject, strBody)
        
        MsgBox "Transfer Complete!", vbInformation, "Success"
        
'Change Status
    Me.StatusID = 4
    Me.Requery
    
End If


I took out the error handling to make this post a little shorter.

I created a temp table in my procedure to determine what was going on (it is not included in the above SP).

Running it from either server or Access adds 1 record to the Job Transfer table--which is correct. It is the return code that I need to determine if an email has to be sent to accounting.

If I run it on the server, the temp table holds one record with a 0. If I run the code from Access, the temp table holds 3 records. The first record is 0 and the next 2 records are 1.

Can anyone one see what should be obvious to me?
This post has been edited by LilAnnCC1: Jun 2 2020, 11:22 AM

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
BruceM
post Jun 2 2020, 03:47 PM
Post#2


UtterAccess VIP
Posts: 8,134
Joined: 24-May 10
From: Downeast Maine


I believe you still need to execute the querydef:

CODE
With CurrentDb.QueryDefs("qPassR")
    .SQL = "Exec spJobTransfer " & JTR & "," & Total
    .ReturnsRecords = False
    .Execute
End With


I'm not sure quite what to do with a stored procedure that is supposed to do something and also execute SQL. I think I would either use an Access query to discover whether it is needed (returns 0, per the example), or get that information (1 or 0) from another stored procedure that is run first, or perhaps from a function. The Access query could be based on a SQL Server View, but views don't accept parameters directly, AFAIK.

Here is an earlier UA discussion I started on a related topic, with some good replies that may be helpful.
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 04:58 AM
Post#3


UtterAccess Moderator
Posts: 13,005
Joined: 6-December 03
From: Telegraph Hill


I agree with Bruce that you need to execute the query, but you also want to ReturnRecords to collect the value of Results:
CODE
' ...
    JTR = Me.ID
    Total = Me.sf_JobTransferRequestDetail!txtSumTotal.Value

    With CurrentDb.QueryDefs("qPassR")
        .SQL = "Exec spJobTransfer " & JTR & "," & Total
        .ReturnsRecords = True
        With .OpenRecordset
            x = .Fields("Results")
            .Close
        End With
    End With

    If x = 1 Then
        MsgBox "This transfer has already been done.", vbInformation, "Transfer exists"
        Exit Sub
    Else
' ...


hth,

d

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


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 3 2020, 05:14 AM
Post#4


UtterAccess Moderator
Posts: 13,005
Joined: 6-December 03
From: Telegraph Hill


If you are regularly executing PT queries in your code, I would suggest creating a couple of wrapper functions to streamline the repeated code.

See this thread

hth,

d

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


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post Jun 4 2020, 02:12 AM
Post#5


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


A few posts here seem to be suggesting that some execute of the store proc needs to occur.

The user ***is*** doing this with this line of code:

CODE
x = DLookup("Results", "qPassR")


Now, I never actually thought of doing a call that way. But, then you did not have to declare a recordset, and why bother with all that code to load up a whole recordset when ALL you want is the one return value from the PT query with your above "smart" idea and reduction of code.

Having stated that the above is a rather cute solution? (and I LOVE those trail blazers that chew out all additional code - 100% bare bones that way to call a store proc!!!! First time in 20 years of Access I seen someone do this!!! - So, I certainly tip my hat to you!!!

However, while I have a smile on my face? My spider sense suggests that the dlookup() is the issue. (and I WILL post a replacement one-liner for you!).

I am thus speculating if the dlookup() function might well do some additional "dance" on the SQL server side.

After all? dlookup() behind the scenes DOES create a reocrdset and the SQL - and that will be sent to SQL server.

It also possible that dlookup() fires and does something against that query to "try" and only pull the first record. That trying part may WELL result in Access touching the PT query MORE then one time in its quest to get ONE row.

Let me test the above theory!!!
hang on---

YES – my spider sense was correct. Using dlookup() fires the procedure 3 times.

I don’t know why it does – but that is what occurs.
(just fire up the SQL profiler to see what dlookup() sends to the SQL server – it fires the exec command 3 times.

So, in the interest of a replacement one-liner?

In the interest of the hot rod mechanics that stay up late at Friday night pulling as many parts out to lighten up their hot rod for the Saturday night races? (to go faster)

In the interest of all those aircraft designers that seek to reduce everything down to a min?

And to quote:
QUOTE
Perfection is attained, not when no more can be added, but when no more can be removed.

Antoine de Saint-Exupery
French writer, poet, aristocrat, journalist and pioneering aviator, and writer of children’s books


In this grand spirt?

You can use this:

CODE

x =   CurrentDb.OpenRecordset("qPassR")(0)
(above gets first column, but you can if you wish use  the named value like this:
  
x =   CurrentDb.OpenRecordset("qPassR")("Results")


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
BruceM
post Jun 4 2020, 08:08 AM
Post#6


UtterAccess VIP
Posts: 8,134
Joined: 24-May 10
From: Downeast Maine


Albert, there's something I'm trying to understand from your reply. The pass-through query qPassR is dynamic, the result established by way of variables passed via the VBA procedure. DLookup works on a domain (qPassR), but how is the domain established in the first place?
Go to the top of the page
 
LilAnnCC1
post Jun 4 2020, 10:07 AM
Post#7



Posts: 873
Joined: 31-May 04
From: Wisconsin, USA


Thank you all for your comments and suggestions! There is no better place to learn as UA!

I decided to use access to look lookup and see if the record exists. If it doesn't then I'm sending over my request to SQL to insert the record into the Job transfer table. I changed my procedure to just to the insert and not return any results. I then use Access to lookup to see if the new record exists and if it does, send out the email. This all seems to work as intended.

Again, thank you all!

And Albert--thank you so much for the compliment--but it all goes to you. You are the one who taught me about qPass and qPassR and I use it all over my project!
This post has been edited by LilAnnCC1: Jun 4 2020, 10:17 AM

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
AlbertKallal
post Jun 4 2020, 05:53 PM
Post#8


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
but how is the domain established in the first place?

Dlookup() was never really any different then creating a recordset with the SQL.

You have Dlookup("some column name", "some table name or query")

So, the "domain" part has always JUST been a simple table or query.

In this example, the user sets up a existing query. You can shove any SQL into that saved query.
The only difference here is that query is a pass-through query - but Access don't care.

So, dlookup(), or VBA recordset + open recordset simply works on that "query" thing.

If after the user were to run his code, then even from the access UI, you can directly click on the query - even the PT query, and it will just spit out a reocrdset and let you view it like any other table.

I never really did grasp where the term "domain" came from??? It just any old table or query that you always been able to specify in dlookup(). And a PT query also can be used.

However, unfortunately, in this case the store proc is "sensitive" to the number of times it is called. If you call it one time, it walks the dog does all kinds of things around the block, and THEN returns standard data like any other query. However, in 99% of cases, the post code would work JUST FINE.

we would have never known that a simple dlookup() touches the table more then one time (I did not know this - but I was a rather good guess on my part).

So, if the store proce did not have some kind of "counter" tracking and return different results for each call? It would have run for years - but actually been firing 3 times. And in most cases that would not matter, and we would not be having this discussion.
But, the store proc is based on design that the routine does some house keeping, and the user EXPECTED the call to only occur one time.

So, Access does not care if you use a recordset against a saved query (or pt query), and it don't care if you use dlookup() against that same query.
but in this case, we did not know or expect that dlookup() seems to fire the call more then one time.

Remember, to use a PT query that is based on a store proc, but use it for say a report?

You can do this:

CODE
CurrentDB.QueryDefs("qryPassR").SQL = "EXEC GetSales  'John'"

' now open the report
docmd.OpenReport  "rptSales",acViewPrevew


So all we are doing here is modifying the query "SQL source", and THEN launching a report based on the query called qryPass.

In the above air code? Well, it would be that we have a SQL store proc, and we want all sales for a given sales rep (in this case John).

So, we pre-render and pre-setup the SQL we want - but we NOT yet run the query - just set the SQL.

So we can shove any SQL into that access query BEFORE we launch the report (or before create a VBA recordset on the query, or
even use dlookup() against that access query).

This idea is NOT limited to SQL server, or even a PT query. You can modify and shove SQL into a existing query as per above, and then launch the report or form or dlookup() based on that existing and saved query.

The poster simply did not want to create a VBA recordset, and the "query" always only returns one row of data with one value.

so, why write all that code when you can just use one line of dlookup().

But, unfortunately a un-known bug/quirk exists with dlookup() - and dlookup() seems to touch the base query/table MORE then one time - in fact it was/is 3 times. I not had time to explore why this occurs - but we now know it does.

R
Albert
Go to the top of the page
 
cheekybuddha
post Jun 4 2020, 07:28 PM
Post#9


UtterAccess Moderator
Posts: 13,005
Joined: 6-December 03
From: Telegraph Hill


>> we would have never known that a simple dlookup() touches the table more then one time <<

I wonder whether this may contribute to the cause that repeated calls to DLookup() is perceived as slow?

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


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post Jun 4 2020, 08:37 PM
Post#10


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
this may contribute to the cause that repeated calls to DLookup() is perceived as slow?


Indeed. I dare say that I been on record quite a bit on here (UA) that there is no difference in performance.

eg:
Use a simple dlookup()
or
Use a recordset to pull the same value.

In fact I don't know if this issue is limited to a SQL server back end, or a PT query, or even a JET/ACE back end experiences this issue.

However, in my benchmarks and testing? Dlookup() is no slower, and actually comes out slightly ahead of using a VBA recordset to do the same thing.

And you did use the term "perceived"! The performance of dlookup() is in general fine, but this case was dlookup() against a PT query, and furthermore it was not only a PT query, but one that called a store proc. Right now I am comfortable with stating and claiming that dlookup() is just as fast as a reocrdset, but this post has shown that dlookup() can in some cases call/pull the data 3 times as opposed to 1 time.

So, I still quite much of the position that dlookup() is just as fast as a VBA + recordset. And re-pulling the row that already cached/pulled likely will not effect performance much. But, with linked tables to SQL server? I'm not so sure anymore. I have some "large" applications, and they do have quite a few dlookups(), and they ARE against SQL server linked tables. So, I have to do a bit of testing to see if this "3 times" pull occurs with a non PT query to SQL server.

I guess this means I don't know everything about Access (yet!!! ;-) ).

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
cheekybuddha
post Jun 4 2020, 08:48 PM
Post#11


UtterAccess Moderator
Posts: 13,005
Joined: 6-December 03
From: Telegraph Hill


I said 'perceived' because I drew the same conclusions as you after some [similar] testing!

But the reputation has come from somewhere, and it's curious that this leftfield use of DLookup() revealed it hit the server 3 times - I just wondered whether it did something similar within Access too.

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


Regards,

David Marten
Go to the top of the page
 
BruceM
post Jun 8 2020, 07:55 AM
Post#12


UtterAccess VIP
Posts: 8,134
Joined: 24-May 10
From: Downeast Maine


Albert, I almost aways pick up some information from reading what you write. I did in this case, but I did not ask the question very well in the first place. You offered, as a simplified version:

CurrentDb.OpenRecordset("qPassR")("Results")

My question was how qPassR is established in the first place. I think I understand now that your shorthand simply omitted that step. The PT query qPassR will remain as whatever it was until it is changed again by running the stored procedure (or editing it directly, but that option won't typically be available to users).

As for DLookup having a reputation for being slow, I think that comes into play when DLookup, etc. is run on every row of a query. I use DLookup when I need to asign an incrementing number, and have not noticed slowness even when the recordset is rather large, although I have not done any specific testing. However, DLookup, or a UDF using a recordset, is likely to be slow when run on every row of a query with many rows. The good news is that a join often eliminates the need.

BTW, with the incrementing number I make the field unique, and make provisions to prevent duplication, and manage it if it occurs.
Go to the top of the page
 
AlbertKallal
post Jun 8 2020, 07:39 PM
Post#13


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
the PT query qPassR will remain as whatever it was until it is changed again by running the stored procedure (or editing it directly,


Correct. But it only a single command we are sending to SQL server.

We only are modifying the local access client side SQL text. We not touching anything server side.

So, the only requirement here is that you create a pass-though query in Access.

What you type into that PT query does not even have to be SQL but can be any value SQL - even t-SQL management commands (say to back up a database).

In "most" cases, we simply over-write the SQL.

So, we could do this:

CODE
CurrentDb.QueryDefs("qryPass").SQL = "select HotelName where ID = 5")

debug.print dlookup("HotelName","qryPass")

CurrentDb.QueryDefs("qryPass").SQL = "exec MyStoreProcName"
debug.print dlookup("Result","qryPass")


Now of course the 2nd lookup in above assumes it returns a column named "result".

So, we not writing a SQL server procedure here - we just executing any legal t-SQL command that you can type at the SQL command prompt.

In fact, how a Access pass-through query works is VERY similar to that of the shell() command in regards to execute of a command line prompt.

So, the PT query will pass in raw format whatever is contained inside of the PT query right on "un-touched" to SQL server.

So, we are free to stuff any text into that query.

And remember, a PT query works like a linked table.
So, just like each linked table has a "connection" string saved and built in as part of the table link?
A PT query is the same! The connection string (to SQL server) is saved with that PT query.

So, in theory my table re-link code will also check/loop the querydefs and look for any PT query, and if found, then I set the connection string the same as what I am using for linked tables.

If you using a NON PT query?

Well, you can thus STILL directly modify the "text SQL" part of that query, but if it not a PT query, then it not going to be sent to SQL server in raw format, but would have to be any legal access SQL command or text. (select query, or update query etc.).

The magic part, or trick is that a PT query is like a linked table - it has a saved connection string, and in this case that connection points to SQL server.

This means that you can on the fly change the SQL text, and grab/change the table that the PT query operates on - even if those 10 or 100 tables were NOT linked to Access. You can specify any legal server side T-SQL command in that PT query. That includes different store procedures, or any SQL server side table. Including ones that return a list of table, or even columns from a table

in fact, in my applications, I usually create two PT queries.

qryPass (set to not return records - I use this for update, or calling store procs that do NOT return data)
qryPassR (set to return records). - I use this one for any select, or store proc that would/does return records.

So, qryPass could not be used for with dlookup.

To execute (use) the PT query without returning reocords? Well, you can stuff the results into a reocrdset, (or use dlookup), so you use it like this:
CODE
Currentdb.querydefs("qryPass").SQL = "exec UpdateInvoices"
Currentdb.querydefs("qryPass").Execute


So, in above, I wanted to call/run a store proc called UpdateInvoices. But this was a routine that does NOT return records.
(so can't use dlookup() to run it in this case).

However, you can't use dlookup() on a plain jane "update" query you saved in access anyway.

And if I need to call a few more store procs, then I just do the above. So, in your VBA code you are free to re-use the PT query over and over. Of course you have to be careful in that the code you are running is a few lines of VBA together.

So, if you are basing a report on that PT query, and a few lines later you need to call some store proc, then I would likely create a new PT query for that report, and not re-use the PT query over and over like I normally do. (becuase the report might still be open, and needs/wants to use that query).

Edit:
Yes, you create the PT query one time, and once created, then you are free to re-use it over and over. I used the Access GUI to create the PT query.


R
Albert
Go to the top of the page
 
BruceM
post Jun 9 2020, 08:49 AM
Post#14


UtterAccess VIP
Posts: 8,134
Joined: 24-May 10
From: Downeast Maine


Thanks again, Albert. Since my early days of SQL Server (not so very long ago) I have been using your PT query technique to accomplish things such as Insert and Delete tasks. It was from a posting in another forum where you were arguing for simplicity in coding as a value-added service to clients. I couldn't find it again, and the details may elude me a little now.

I use PT queries built on the fly for returning records too, but at this point I use it more to accomplish tasks. My most frequent use of PT queries is for combo box row sources that involve several tables, but those queries don't tend to change.

This thread has brought to my attention the following: x = CurrentDb.OpenRecordset("qPassR")(0), or with the field name. I can remember wanting to return a value from a recordset consisting of one record, but I'm pretty sure I took a longer route to get there.
Go to the top of the page
 
AlbertKallal
post Jun 12 2020, 02:20 PM
Post#15


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
My most frequent use of PT queries is for combo box row sources that involve several tables


Careful!!!!

Remember
PT query = fastest way to pull data.

But, and this is BEYOND critical:
Access client can NOT filter a PT query. NEVER CAN!

So, if you filling a comb box? Great!

But, if that combo box is bound in a form? Well, now you have a big problem. Say the combo box is filled with 5,000 rows.

If you navigate to a new record?
Well, Access is smart, and will ONLY pull the one current row for the comb box (99% of the time it combo stores the PK, but the 2nd column for display - say description or part name etc. is also displayed).

What will occur is now Access will attempt to fill/get/grab/display the ONE row for the combo box. But as I noted, you can't filter a PT query!! (NEVER).

So, Access has to crunch on the 5,000 rows to get the ONE row for display.

In this case? A plane jane linked table is MUCH faster. If the query (as you noted) is a complex join, or involves multiple tables? Then you want to use a view and not a PT query. With a linked table (or linked view), access can pull the ONE row out of 5,000. With a PT query that is not possible.

So, for a report that has a filter, or a sub form? Do not use a PT query if filtering on that data is to occur. If the PT query is pre-filtered, then fine.

Just keep in mind that a PT query is only of use if you NEVER filter the results of that PT query. If you need this ability, then a linked view will out perform the PT query by a country mile.

R
Albert

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th July 2020 - 01:23 AM