how to know if function is Access based or SQL based?

hey guys,

i've asked this question before in some shape and form before, but now with a clearer understand i want to approach it again if yall don't mind (don't be mad at me!!) = ))

i'm writing some SQL queries and need to use functions

1) vba has an object browser. what's the equivalent for SQL?

2) if i know the answer for 1), i don't need to ask this. but i don't think there is an object / function browser for SQL.

so how do if the function i'm called is a SQL or VBA function? let's say i use function xyz and function xyz only exists in VBA but i THOUGHT it exists in SQL too - but it doesn't. how can i find out that VBA is the one running this function? is it through looking at the execution path - will execution path tell me this?

3) if i use any function that has the same name in SQL and VBA, the SQL one will always be called first right?
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
for 1), there is no such thing , but this page should help:
http://www.techonthenet.com/access/functions/

2) if the function doesn't work in your SQL, it's because it isn't available there, but only in VBA.

3) inside the SQL only the SQL function will be used.
VBA might have the same function (read same name), but behind the scenes, it's still 2 distinct functions. they eventually even do the same thing.
0
Jeffrey CoachmanMIS LiasonCommented:
Also note that there are at least 4 "flavors" of SQL, (Jet/Access/T-SQL, SQL Server, Oracle, MySQL)
Each may have the basic SQL Functions, but they may also include functions unique to their specific platform.

So unfortunately you will have to search the documentation for each platform...
...and find out how to list the functions....

You can go here for the T-SQL functions: http://technet.microsoft.com/en-us/library/ms174318.aspx

I am still a little hazy on your question...
0
developingprogrammerAuthor Commented:
whao cool! thanks so much boag2000 and angelIII!!

angelIII the VBA function still works in SQL query doesn't it? e.g. nz() is a VBA function but still works in SQL

hrmm maybe if you're talking about execution plan then the vba will be executed first before running the SQL is that what you mean? but to a end developer it "looks" like the VBA function works as well

thanks boag2000! whao so many flavours of SQL, so many choices just like Ben and Jerrys!! haha. basically i'm just trying to learn how to use SQL better to optimise it instead of using VBA functions which will slow it down
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jeffrey CoachmanMIS LiasonCommented:
< nz() is a VBA function but still works in SQL >
..."kinda"...

If you write a query in Access (Jet/T-SQL) you can use NZ(), because it is an Access function. (Even if the data being queued is on a SQL Server)
This is because Access is actually processing the query

If you write a query in SQL Server NZ() will not be available.
0
developingprogrammerAuthor Commented:
I see thanks boag2000! Hrmm I'm coming from the point of performance that's why I'm trying to only use SQL functions. So is abs() an ONLY Access function? I'm thinking of using iif(amount<0,amount*-1) --> one result possible cause iif is the SQL flavour.

Hrmm I guess I'm coming from a purist stand point cause I want to train myself now at least to use only SQL functions and only using Access functions when I've got no choice ha = P
0
Jeffrey CoachmanMIS LiasonCommented:
Because Access is a 'RAD" platform (Rapid Application Development), it usually has more functions designed to make your like easier like NZ()

But to answer your question , both Abs() and the IIF() functions both exists in Access as well as SQL Server...

So hear again, you will have to investigate the links w posted to see what functions are unique to Access and the SQL variants.

<Hrmm I guess I'm coming from a purist stand point cause I want to train myself now at least to use only SQL functions and only using Access functions when I've got no choice ha = P >
...good thinking, a firm grasp of SQL will extend your skill-set beyond the realm of just Access.

JeffCoachman
0
developingprogrammerAuthor Commented:
thanks Jeff!! = ))

ok guys so let me try and sum everything up.

1) http://www.techonthenet.com/access/functions/ 
VBA and Access SQL functions - but it doesn't tell us if a function is only VBA, only Access SQL, or both.

2) http://technet.microsoft.com/en-us/library/ms174318.aspx
T-SQL function - this is USUALLY similar to Access SQL functions but not always

3) there is no object browser for Access SQL and also no Access SQL specific documentation.

Conclusion: there is no way we can tell definitively whether a function in Access is a VBA only function, SQL only function, or both VBA and SQL.

is this conclusion correct?
0
Jeffrey CoachmanMIS LiasonCommented:
1. Those are Access functions only (you can use them when you make queries in Access, (as the link states)
There is no such thing as an Access "SQL Only" function, ...but here we get into the fine distinctions between Jet and T-SQL
But those are Access function, you can use then in VBA, Expressions and in Access queries

2. Again, there is no such thing as a "Access SQL only function"
These are T-SQL function, and some cannot be used in Access created queries 'directly' (some can be used when you use a Pass-through query though)

But is your goal is to learn these "SQL ONLY Functions", then know that some will not work in Access.  
But here again, T-QSL is only one flavor of SQL.
MySQL and Oracle each have there own unique Functions as well.

3. No, because there is no such thing as "Access SQL", There is plenty of info available on the "T-SQL", which Access uses.
You can use the expression builder in Access to list all of the Access functions
(You can also use the object browser in VBA to see functions, ...but this opens a whole new can of worms...)

You can use the function browser in SQL Server to list all the functions

On your 'Conclusion"
It depends on where these functions appear....

The bottom line is this...  
You stated that the purpose of this Q was:
    "i'm just trying to learn how to use SQL better to optimise it instead of using VBA functions"

Then just learn T-SQL, and forget about the distinctions between Access SQL, and VBA functions.
90% of the time the distinction will be irreverent (when writing queries).

But your assertion that: "VBA functions which will slow it down"
...is not fully qualified, ...so this cannot be a definitive statement

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
developingprogrammerAuthor Commented:
Superb answer Jeff!!!! = ))))
0
Jeffrey CoachmanMIS LiasonCommented:
Ok...
;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.