Reference to a list of JET/ACE functions

[Galaxiom]

Does anyone have a reference with a definitive list of the Jet/ACE engine functions?

Following up from this thread it seems some developers don't realise the standard functions used in queries are NOT the VBA functions that have the same names, but are integral to the engine itself.

I tried to find a list and posted a link to a page that had been posted by someone having a similar discussion but it isn't right. (Shouldn't have taken it on face value.) I have looked again but I can't find anything.

I guess it could be determined by sending queries via ODBC and seeing what works but that seems a lot of effort.

 

[theDBguy]

Hi. All I could think of is to use the Object Browser in the VBE.

 

[jdraw]

I don't. But I did find this from M$oft via Google.

History of Jet/ACE

 

[The_Doc_Man]

Unfortunately, the list of functions from Microsoft includes the GUI/Development environment functions and VBA functions. I tried to find the functions using the Object Browser but there is no (obvious) way to focus on that information. I might, however, have another way. It involves using some old DOS tools, though, so I don't know if I can actually get to it.

 

[theDBguy]

I tried to find the functions using the Object Browser but there is no (obvious) way to focus on that information.

Did you try selecting VBA from the library dropdown?

 

[The_Doc_Man]

No, that tells you want is in the VBA library. I can't select the MS Access Database Objects Library because it doesn't show up separately.

Thinking about it, there is some thin ice here. If we can find an article online, well and good. But if we tried to reverse-engineer it to find the list, that would be a technical violation of the EULA for using Access. So we have to be careful about how we approach the problem.

 

[Galaxiom]

Did you try selecting VBA from the library dropdown?

I think you are misunderstanding the question. I'm looking to find which functions are native to the engine rather than coming from VBA.

I believe it is possible to connect to the engine without involving the project. The functions available would then be limited to the native functions of the engine itself. The JET engine was originally used by Windows, though has been replaced with ACE for Access and another separate one (whose name escapes me) for Windows itself.

Like other database engines, they would have many functions built into them. Obviously the engines would not rely on calling to VBA to do basic database tasks such as string manipulation.

 

[theDBguy]

I think you are misunderstanding the question. I'm looking to find which functions are native to the engine rather than coming from VBA.

I believe it is possible to connect to the engine without involving the project. The functions available would then be limited to the native functions of the engine itself. The JET engine was originally used by Windows, though has been replaced with ACE for Access and another separate one (whose name escapes me) for Windows itself.

Like other database engines, they would have many functions built into them. Obviously the engines would not rely on calling to VBA to do basic database tasks such as string manipulation.

Sorry for my confusion. I'll you know if I find out something else.

 

[Pat Hartman]

This is pretty old but it might be what you are looking for:

Microsoft Jet SQL Reference - Microsoft Jet SQL Reference Documentation

documentation.help

I couldn't find anything similar for ACE. I do remember that older versions of Access had significantly better help systems. If you have an installation of A97 hanging around, you'll be amazed at what you can learn from help.

 

[Galaxiom]

This is pretty old but it might be what you are looking for:

Microsoft Jet SQL Reference - Microsoft Jet SQL Reference Documentation

documentation.help

Bingo. This provides at least a partial list. All the ODBC Scalar Functions would have to have native equivalents in the engine. There may be others with no ODBC translation and quite likely more in later versions.

Most of them would be pretty obvious.

SUBSTRING = Mid()
LOCATE = Instr()

 

[Pat Hartman]

Glad to help