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
> SQL Server & Access Tempvars, Any Versions    
 
   
tykra
post Nov 3 2017, 04:48 PM
Post#1



Posts: 61
Joined: 14-January 17



I have recently started migrating my BE to SQL Server. My previous access application utilized a front end and the standard access flat file backend; I used tempvars to assist in querying - just add them in the criteria and I could easily retrieve filtered data.

Well, I started replacing my access queries with SQL Views so that I could get as much of the workload as possible done by SQL server. I thought it sure would be great if I could place those tempvars in the filter, but unfortunately its not that easy; placing [tempvars]![myvariable] in the filter box doesn't work. frown.gif

Question I have is does SQL offer any way to call those tempvars that I create in access or will I have to keep all queries that reference [tempvars] inside the access frontend?

Thanks for the feedback!
Go to the top of the page
 
MadPiet
post Nov 3 2017, 05:35 PM
Post#2



Posts: 2,264
Joined: 27-February 09



If you're passing parameterized values to your queries, that's the same as a stored procedure in SQL Server. Something like this (show me all the Sales for a specific StoreID - yes, I know it's a terrible stored procedure and using * in a stored procedure is bad practice!)

CODE
CREATE PROC ReturnFilteredDataSet
     @StoreID INT
AS
SELECT *
FROM Sales
WHERE StoreID = @StoreID;


Then you can call it by doing something like this:

CODE
EXEC ReturnFilteredDataSet @StoreID = 12;

or
CODE
EXEC ReturnFilteredDataSet 12;
Go to the top of the page
 
tykra
post Nov 3 2017, 06:28 PM
Post#3



Posts: 61
Joined: 14-January 17



So would/could I use my existing tempvars and do something like ...

CODE
EXEC ReturnFilteredDataSet @empID = [tempvars]![employID];


or do I need to go eat some dinner so that I can think more clearly.

(Not a SQL Server pro and only started using Access in the last year.)
Go to the top of the page
 
MadPiet
post Nov 3 2017, 08:25 PM
Post#4



Posts: 2,264
Joined: 27-February 09



SQL Server doesn't have TempVars the way you're describing. You can create variables in your stored procedures to your heart's content.

CREATE PROC MyProc
@InputParam VARCHAR(10)
AS

DECLARE @Counter TINYINT; -- you could declare a BUNCH here, if you wanted.
-- silly loop.
WHILE @Counter <=5
BEGIN
PRINT @InputParam;
SET @Counter=@Counter + 1;
END

and then use it all over the place inside your stored procedure. If you wanted to pass the value to another stored procedure, you'd have to call it inside the first stored procedure. Not having a solid background in databases might make SQL Server a bit overwhelming. I used Access for a long time before starting on SQL Server, and the differences are significant. Don't mean to dissuade you in any way, but be aware that the two are very different.

In this rather ridiculous stored procedure, @Counter is essentially a temporary variable - accessible only inside the body of the procedure. (Analogous to declaring a private variable inside a sub or function in VBA). While you can create global variables (accessible from anywhere in your code), it's rarely a good idea.
Go to the top of the page
 
MadPiet
post Nov 3 2017, 08:26 PM
Post#5



Posts: 2,264
Joined: 27-February 09



Come to think if it, where did you intend to call that?

If you glued the bits together and then used a pass-through query to SQL Server, you could do it.
Go to the top of the page
 
GroverParkGeorge
post Nov 4 2017, 09:18 AM
Post#6


UA Admin
Posts: 31,243
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

You're getting good advice, but I can't resist the chance to toss in a couple of pennie's worth of background.

In Access, pretty much all queries are called, well, queries. However, there are different flavors: Select and Action queries.

Select queries do just that, select a set of records from one or more tables in order to return that record set for further use.

Action queries, on the other hand, modify records in one or more tables.

It's easy to see the need for parameters in both types of queries; you want to return only a subset of all possible records in the Select query, or update or delete only a subset of all possible records in the action query.

On the SQL Server side, the same sort of situation exists, but it's a bit more complicated.

Views are static. They are not directly parameterizable. You can HARDCODE parameters in them, using the WHERE or HAVING clause, but those parameters can't be directly altered at run-time. And, of course, they are of the Select variety.

To apply parameters, or to perform actions, in SQL Server, you need their cousins, stored procedures. Stored procedures can take parameters, and they can perform updates or deletes, among other, much more complex actions. (You'd use Functions and Subs for those more complex actions in Access.)

So, in that context, passing the value of a TempVar calls for a Stored Procedure. And that's what Pieter is explaining to you.

From the Access side you can execute SQL Server parameterizable stored procs (as they are often called) in more that one way. One commonly used approach is use a Passthru query, which executes the stored proc and gives it the current parameter values. That's accomplished by dynamically rewriting the SQL in the Passthru.

Here's a very simple example.

CODE
Private Function GetPerson()
Dim db as DAO.Database
Dim qdef As DAO.QueryDef

Set db = CurrentDB
Set qdef = db.QueryDefs("qsptGetPerson")
qdef.SQL = "exec spGetPerson " & Nz(tempVars!CurrentPersonID,0)
End Function


After this function is called and executed, the SQL string in the local Passthru query will have the parameterized stored procedure that will return one record when it is opened elsewhere in your database.

See Pieter's explanation of the syntax for stored procs.

--------------------
Go to the top of the page
 
tykra
post Nov 4 2017, 10:23 AM
Post#7



Posts: 61
Joined: 14-January 17



I was thinking I might be able to call (execute) that in a pass-thru query from the access side.

CODE
EXEC ReturnFilteredDataSet @empID = [tempvars]![employID];


Grover I don't need to modify tables/rows at run-time; I just want to use the parameters to filter the returned results.
Go to the top of the page
 
GroverParkGeorge
post Nov 4 2017, 10:44 AM
Post#8


UA Admin
Posts: 31,243
Joined: 20-June 02
From: Newcastle, WA


No, you can't pass the tempvars directly to SQL Server. It has no meaning there.

Evaluate it to return its local actual value, as has been illustrated and pass that.

Passthrus sent to SQL Server know nothing of the Access environment. They are sent, as is, to SQL Server. So, sending that line shown would simply confuse SQL Server because [tempvars]![employID] has no meaning there.

You must convert [tempvars]![employID] to its current value, e.g. 12 or 974 or whatever before giving it to the Passthru to execute.

Think of it this way. Passthrus are, essentially, a "black box" which you pack and send to a remote customer. The remote customer opens and unpacks it. The customer finds this bit of information that meant something to you when you put it into the box, but which the customer has no way to resolve. Heck, the customer doesn't even recognize the term [tempvars]![employID], let alone know what to do with it.

However, the customer CAN know that you want something for record 12, or 974, or 123456, or whatever, because that's how the customer finds things in their location.

Both you and the customer have to agree that 12, or 974, or 123456, or 23DF means the same thing to both of you, of course. Otherwise it would get really messy.

So, dynamically rewrite the SQL in the Passthru, or adopt one of the other methods for running stored procedures using ADO. They're equally useful if a bit more complicated. I've done both in different environments and lean towards the former because it's better suited to my toolset.

--------------------
Go to the top of the page
 
tykra
post Nov 4 2017, 08:12 PM
Post#9



Posts: 61
Joined: 14-January 17



So I can not create a stored procedure in SQL Server as Piet described ...

CODE
CREATE PROC ReturnFilteredDataSet
     @StoreID INT
AS
SELECT *
FROM Sales
WHERE StoreID = @StoreID


And then within Access call the pass-thru query as such ...

CODE
EXEC ReturnFilteredDataSet @StoreID = [Tempvars]![SelectedStoreID];


Seems like that might work, but as a newbie I will defer to your comments.
Go to the top of the page
 
GroverParkGeorge
post Nov 4 2017, 10:16 PM
Post#10


UA Admin
Posts: 31,243
Joined: 20-June 02
From: Newcastle, WA


I guess my explanation wasn't as clear as I'd hoped.

If you pass this: EXEC ReturnFilteredDataSet @StoreID = [Tempvars]![SelectedStoreID];

in a Passthru query, SQL Server will receive this:

EXEC ReturnFilteredDataSet @StoreID = [Tempvars]![SelectedStoreID];

And SQL Server can not know what to do with it; it is received ONLY as a string. The actual value of the Tempvar is NOT passed, only the name is passed.

You must replace the phrase [Tempvars]![SelectedStoreID] with the actual value itself.

EXEC ReturnFilteredDataSet @StoreID = 2222;

or

EXEC ReturnFilteredDataSet @StoreID =3;

and so on.

Remember SQL Server doesn't reach into Access to poke around looking for variables. You must give those values to SQL Server yourself in the Passthru.

--------------------
Go to the top of the page
 
tykra
post Nov 5 2017, 10:47 AM
Post#11



Posts: 61
Joined: 14-January 17



No you did fine, I was just looking for further clarification. I had thought that access would fill in the actual tempvar value before presenting the query to the server ... iwth & & . I reckon I need to look into getting the tempvars value into the pass thru.

Thanks for the guidance, much appreciated.
Go to the top of the page
 
GroverParkGeorge
post Nov 5 2017, 01:32 PM
Post#12


UA Admin
Posts: 31,243
Joined: 20-June 02
From: Newcastle, WA


By definition, a Passthru query sends its SQL string verbatim to SQL Server. The term "Passthru" refers to that fact. Access doesn't even try to look at what's in the SQL string; it just passes it straight through to the server where it is evaluated, and, if possible, executed. It's possible, therefore, to send absolute gibberish to SQL Server in a Passthru without having Access bat an eye.

--------------------
Go to the top of the page
 
tykra
post Nov 5 2017, 06:08 PM
Post#13



Posts: 61
Joined: 14-January 17



Understood, guess I need to come up with a replacement for the tempvars Ive been setting in access.

Thanks.
Go to the top of the page
 
MadPiet
post Nov 5 2017, 06:38 PM
Post#14



Posts: 2,264
Joined: 27-February 09



The only way I can think of doing this with Tempvars is to build the whole SQL statement in VBA and then pass it to SQL Server using a pass-through query.

strSQL = "EXEC dbo.MyStoredProc " & parametervalue1 & ", " & parametervalue2 & ";"

then execute that as a passthrough.

Don't know about using Tempvars. Never used them.
Go to the top of the page
 
tykra
post Nov 5 2017, 06:57 PM
Post#15



Posts: 61
Joined: 14-January 17



I haven't totally given up on the idea yet as it would be nice to find a quick easy fix vs going through the entire application and changing out all the places I use tempvars.

I've been looking for more info on others who use tempvars and am seeing others talk about using querydefs to pass the access objects to the pass through then use an eval to get the value.

QUOTE
WHERE variable = Eval("TempVars!MyVariable")


I'll play around with it tomorrow when I get into the office.

Again thanks for your feedback.
Go to the top of the page
 
GroverParkGeorge
post Nov 5 2017, 09:09 PM
Post#16


UA Admin
Posts: 31,243
Joined: 20-June 02
From: Newcastle, WA


You should not give up Tempvars. They work very well.

You will need to rewrite the SQL of the Passthrus to use the values they produce.

pseudo code:

CODE
Public Function RewriteandExecutePassthru()
Dim qdef as DAO.QueryDef
Dim db as DAO.Database

Set db = Currentdb
Set qdef = db.QueryDefs("qryYourPassthruQueryNameGoesHere")
With qdef
   .SQL = "Exec spYourStoredProcNameGoesHere " & tempVars!SelectedStoreID
End With

db.Execute "qryYourPassthruQueryNameGoesHere", dbSeeChanges + dbFailOnError

End Function

--------------------
Go to the top of the page
 
BruceM
post Nov 6 2017, 10:59 AM
Post#17


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


It seems a single passthrough query could be used for whenever the QueryDef SQL property is being set in code as you describe, George. Am I overlooking anything?
Go to the top of the page
 
tykra
post Nov 6 2017, 05:42 PM
Post#18



Posts: 61
Joined: 14-January 17



Well here is what I did; extremely easy ....

Set up a pass through query within Access ...

CODE
SELECT * FROM mytable


Then on the form I did this in the On Load

CODE
CurrentDb.QueryDefs("Q_GetMyStore").SQL = _
  "SELECT * FROM mytable WHERE storeID = '" & TempVars!selectedStoreID & "'"
    
  Me.RecordSource = "Q_GetMyStore"


Sure seems to work like a charm and extremely easy.
Go to the top of the page
 
GroverParkGeorge
post Nov 6 2017, 06:28 PM
Post#19


UA Admin
Posts: 31,243
Joined: 20-June 02
From: Newcastle, WA


Yup, that's another variation of the same process. wink.gif

Congratulations on resolving the question.

Continued success with the project.

--------------------
Go to the top of the page
 
AlbertKallal
post Nov 6 2017, 08:36 PM
Post#20


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


Actually if you saying you used tempvars for the forms filter, then why not continue to do so?

If you open a form with a where clause, or set a filter on a form, then Access will do a fine job of filtering and restricting the records pulled from SQL server.

Now of course if you mean the user of tempvars right in the SQL, well that was a “less” than ideal approach and in general should be avoid (for both access and SQL back ends).

So if you have a report that is some “complex” SQL and joins then simply convert that to a view and use that as the data source for the report.

Your existing “where” clause that opens the form/report can continue to use your existing VBA and even those tempvars in the filter, or open report command.

So it sounds like you were not just using tempvars to filter forms, but actually placing tempvar references right into the SQL – and that is an approach recommend you avoid.

One great benefit is that your SQL can now be used for a form, a report, or whatever, but it not tied to some tempvars.

So to filter a form, or report – simply use the “where” clause of the open form/report. The result is great performance and in general you find Access only pulls down the records that meet the criteria.

In fact what the above means, that if you code reports (and forms) using the simple built in “where” clause, then such VBA filtering code will work equally well with access back ends, or SQL back ends. And even better is such code in general requires zero modifying to work for both types of back ends.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 12:43 PM