Calling a UDF from a VB SQL statement

[Mark1]

I need to call a user-defined function from within a SQL statement in VBA. I
need it to go something like this, assuming my user-defined function is
called UDF :

strSQL = "SELECT NAME, UDF(ADDRESS)" & _
"FROM TABLE1 " & _
"WHERE.... "

If I take the UDF(ADDRESS) statement out of the double quotes, VBA seems to
recognize it, but I really need it to be part of the SELECT statement in my
strSQL. I get the error:

"Undefined Function" if I leave it in the double quotes.

Thanks!

 

[Alex Dybenko]

Hi,
Access can recognize UDF in queries only if you run it using Access, not
Jet. so docmd.runsql will work, but currentdb.execute - will not. so if you
need to run action query - you can use docmd.runsql, and if you need to open
recordset - remove UDF from select and run it in VBA

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Alex Dybenko]

Hi,
Access can recognize UDF in queries only if you run it using Access, not
Jet. so docmd.runsql will work, but currentdb.execute - will not. so if you
need to run action query - you can use docmd.runsql, and if you need to open
recordset - remove UDF from select and run it in VBA

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Mark1]

I tried the DoCmd.RunSQL strSQL, but its still giving me the error message.
I don't know what you mean by taking the UDF out of the SELECT. The whole
thing is already in VBA and UDF is the name of my function (in the example).
Let me paste my whole procedure. That will clarify, I guess... Here it is:

Private Sub Command2_Click()
Dim strSQL As String

DoCmd.SetWarnings False

strSQL = "INSERT INTO [Number] (Sound) " & _
"SELECT Soundex(occupation)" & _
"FROM Table1 " & _
"GROUP BY Table1.occupation; "
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True
DoCmd.Maximize
Exit_butNewRecord_Click:
Exit Sub

Err_butNewRecord_Click:
MsgBox Err.Description
Resume Exit_butNewRecord_Click

End Sub

Function Soundex(ByVal S As String) As String
S = UCase$(Trim$(S))
Dim Code As Integer: Code = 0
Dim Last As Integer: Last = 0
Dim R As String: R = ""
Dim i As Long: For i = 1 To Len(S)
Select Case Mid$(S, i, 1)
Case "B", "F", "P", "V"
Code = 1
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
Code = 2
Case "D", "T"
Code = 3
Case "L"
Code = 4
Case "M", "N"
Code = 5
Case "R"
Code = 6
Case Else
Code = 0
End Select
If (i = 1) Then
R = Mid$(S, 1, 1)
ElseIf (Code <> 0 And Code <> Last) Then
R = R & Code
End If
Last = Code
Next i
Soundex = Mid$(R & "0000", 1, 4)
End Function

Hi,
Access can recognize UDF in queries only if you run it using Access, not
Jet. so docmd.runsql will work, but currentdb.execute - will not. so if you
need to run action query - you can use docmd.runsql, and if you need to open
recordset - remove UDF from select and run it in VBA

I need to call a user-defined function from within a SQL statement in VBA.
I

[quoted text clipped - 14 lines]

Thanks!

 

[Dirk Goldgar]

Hi,
Access can recognize UDF in queries only if you run it using Access,
not Jet. so docmd.runsql will work, but currentdb.execute - will not.

Sorry, Alex, but you're mistaken. CurrentDb.Execute will recognize
user-defined VBA functions. As long as the query is executed within the
Access application -- as the use of CurrentDb implies -- the function
name can be resolved. However, if the query is executed from outside
Access -- using DAO or ADO from a VB app or a web page, for example --
then the UDF won't be recognized.

In Mark's case -- posted in multiple threads, alas -- the problem was
that the function was defined in the form's class module, not in a
standard module.

 

[Guest]

btw, I've seen it get pretty confusing when the user has VB6
code like:
dim CurrentDB as database
set CurrentDB = ws.OpenDatabase( ...

(david)

 

[Dirk Goldgar]

btw, I've seen it get pretty confusing when the user has VB6
code like:
dim CurrentDB as database
set CurrentDB = ws.OpenDatabase( ...

Yes, that would be confusing, all right!

 

[Alex Dybenko]

Hi Dirk,
yes, you right, get messed with VB6 issues
sorry for confusion

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Alex Dybenko]

Hi,
Soundex() should be in common module (not in form's class module), and your
whole project should be compiled. pls check this

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I tried the DoCmd.RunSQL strSQL, but its still giving me the error message.
I don't know what you mean by taking the UDF out of the SELECT. The whole
thing is already in VBA and UDF is the name of my function (in the
example).
Let me paste my whole procedure. That will clarify, I guess... Here it
is:

Private Sub Command2_Click()
Dim strSQL As String

DoCmd.SetWarnings False

strSQL = "INSERT INTO [Number] (Sound) " & _
"SELECT Soundex(occupation)" & _
"FROM Table1 " & _
"GROUP BY Table1.occupation; "
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True
DoCmd.Maximize
Exit_butNewRecord_Click:
Exit Sub

Err_butNewRecord_Click:
MsgBox Err.Description
Resume Exit_butNewRecord_Click

End Sub

Function Soundex(ByVal S As String) As String
S = UCase$(Trim$(S))
Dim Code As Integer: Code = 0
Dim Last As Integer: Last = 0
Dim R As String: R = ""
Dim i As Long: For i = 1 To Len(S)
Select Case Mid$(S, i, 1)
Case "B", "F", "P", "V"
Code = 1
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
Code = 2
Case "D", "T"
Code = 3
Case "L"
Code = 4
Case "M", "N"
Code = 5
Case "R"
Code = 6
Case Else
Code = 0
End Select
If (i = 1) Then
R = Mid$(S, 1, 1)
ElseIf (Code <> 0 And Code <> Last) Then
R = R & Code
End If
Last = Code
Next i
Soundex = Mid$(R & "0000", 1, 4)
End Function

Hi,
Access can recognize UDF in queries only if you run it using Access, not
Jet. so docmd.runsql will work, but currentdb.execute - will not. so if
you
need to run action query - you can use docmd.runsql, and if you need to
open
recordset - remove UDF from select and run it in VBA

I need to call a user-defined function from within a SQL statement in
VBA.
I

[quoted text clipped - 14 lines]

Thanks!