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

Posted on 2013-09-03
Medium Priority
Last Modified: 2013-09-07
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?
Question by:developingprogrammer
  • 5
  • 4
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 39462672
for 1), there is no such thing , but this page should help:

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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39462821
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...

Author Comment

ID: 39465836
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39466160
< nz() is a VBA function but still works in SQL >

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.

Author Comment

ID: 39466667
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
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39468430
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.


Author Comment

ID: 39472517
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?
LVL 74

Accepted Solution

Jeffrey Coachman earned 1600 total points
ID: 39473251
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


Author Comment

ID: 39473275
Superb answer Jeff!!!! = ))))
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39473279

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question