Amazing use of User-written VBA functions in SQL

[mud2]

This is so IMPORTANT that I MUST report...perhaps it is known already.
You can write your own functions and use them in SQL statements. As example,
I have a form, with a control button taht sarts the process. A table is created (SQL)...an input value is requested (InputBox), this value is passed to a user-written function embedded in an INSERT INTO sql...

The function MUST(?) be in a separate MODULE1!

Private Sub Command7_Click()
Dim sqlCreate As String
Dim SqlCall As String
Dim ThisFunction As Integer
Dim ThisData As Integer
ThisData = InputBox("Enter an Integer")
sqlCreate = "Create Table MyFunction (Function_Return number);"
SqlCall = "Insert into MyFunction (Function_Return) Values (ThisFunction(" & ThisData & "));"
'SqlCall = "Insert into MyFunction (Function_Return) Values (ThisFunction(300));"
'DoCmd.RunSQL sqlCreate
DoCmd.RunSQL SqlCall
End Sub
' *************************************************************************** ********************
'In a separate MODULE:
Option Compare Database
Option Explicit
Public Function ThisFunction(Data As Integer) As Integer
ThisFunction = 5 * Data
End Function


The implications are enormous...

[matthewspatrick]

The implications are enormous...

Yes, and everyone in the Access user community has known about it for, like, forever :hi:

Sorry, couldn't help myself. Yes, it's exciting, but it's a well-known thing, and has been for a very long time.

[mud2]

I've never seen any mention of The use of user-written functions in access-SQL in any of my zillions ($$$) of access...SQL books!
So "Pop" went my baloon!

[Brandtrock]

I've never seen any mention of The use of user-written functions in access-SQL in any of my zillions ($$$) of access...SQL books!
So "Pop" went my baloon!

It's a vast right wing conspiracy begun during the Reagan administration in an effort to enrich the rich (who were given this tidbit of information) and keep the down trodden, well, more trodden. With the election of Bill Clinton in 1992, there was a window of opportunity but the right wingers recruited one M. Lewinsky to thwart Clinton's efforts to promulgate this knowledge among the masses.

How was this done?

We all know the public story, what is less well known is that one SirBabydum was enlisted by remnants of the Thatcher administration to recruit M. Lewinsky. When details of the deed became public, all of the lackeys (who were supposed to post the function functionality on internet newsgroups, newsletters, articles, books, and later on public web forums) were diverted to spin the scandal.

Ken Starr, a disgruntled user of M$ software, saw his chance to prolong the subversion of Clinton's plan to inform the masses by continuing to investigate the private affairs of the President. He dug and dug and dug, all the while relishing the thought that only the Reagan insiders would ever know the whole truth about UDF's and Access.

The entire Clinton administration was marred by this inability to spread the word about UDF functionality in SQL statements, but they persevered confident that the Gore administration would get the job done after Bill left office. Unfortunately, the Reagan forces had trained their minions well.

UDF's were written and snuck into the electronic voting machines in 49 key states in the 2000 election. They very nearly got cleanly away with their scheme. Only the antiquated voting process in Florida foiled their techno-sabotage.

So don't feel too bad, it could be worse ...