Access/Jet SQL supports VBA functions.
Yes, Access enables VBA6 and UDF (plus its own) functions to be used
in Access/Jet SQL but such 'support' is not native to the engine. If
you use such functions in the SQL definition of a stored Query object
then you will cripple the VIEW/PROC when used outside of the Access
environment e.g. someone wanting to pull data into their Excel
workbook.
Does
this make those functions part of the SQL DML?
No because, as I said above, the engine has its own implementation of
expressions which, not coincidentally, have close equivalents in the
VBA5 libraries. Try using a VB6 function (e.g. Replace) outside of
Access e.g. run this from a standard module in an Excel workbook:
Option Explicit
Sub ThisWorks()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe1.mdb"
With .ActiveConnection
MsgBox .Execute("SELECT ISNULL(0);")(0)
End With
End With
End Sub
Sub AndThisDoesNot()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe2.mdb"
With .ActiveConnection
MsgBox .Execute("SELECT REPLACE('A', 'A', 'B');")(0)
End With
End With
End Sub
I'm using VBA so why does the second sub procedure fail? The answer is
because the expression library has no REPLACE function. There is no
direct causation between VBA and the non-existent missing REPLACE,
although it seems there once was a policy than Jet SQL expressions
should broadly mirror VBA scalar functions. Shame that MS never got
around to updating the expression library to take account of the
advancements of VBA6 (a REPLACE() expression would be nice), though on
the flip side it's a shame they could break with policy and include
useful things not found in VBA (Nz() would be nice even without a name
change to COALESCE said:
If I write my own function in VBA and use it in a query, is that function
then part of the SQL DML? I guess it is by your definition.
No, that's not my position. Consider the following classification with
examples:
Access/Jet SQL e.g. IIF()
VBA6 library e.g. Replace()
Access Object Library e.g. Nz()
Your application e.g. a UDF.
as far as I'm concerned they are VBA functions.
Good for you but in a public forum your classification could be
misleading others.
Jamie.
--