Avatar of developingprogrammer
developingprogrammer asked on

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?
Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

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...
ASKER
developingprogrammer

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
Jeffrey Coachman

< 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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
developingprogrammer

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
Jeffrey Coachman

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
ASKER
developingprogrammer

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
developingprogrammer

Superb answer Jeff!!!! = ))))
Jeffrey Coachman

Ok...
;-)