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?
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?
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
< 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.
..."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.
ASKER
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
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
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
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
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Superb answer Jeff!!!! = ))))
Ok...
;-)
;-)
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...