HOW TO CALL FUNCTION WITH SQL STATEMENT IN VISUAL BASIC

[gthandle]

This call is from VB project NOT "MS Access Database file".
It work only in MS Access not in VB. WHY ??????? and how to make it works.

"UPDATE

set field1 = myfunction()"

Here is my function code:

public function myfunction() as variant
myfunction = str(rnd(999))
end function

 

[aiyoyo]

cos your function, myfunction, is a VB function, not a database's function.
in the case of MS Access, the function is stored in the access file, so when you execute the SQL, the function can be found.
to make it work, change your SQL to:
"UPDATE

set field1 = '" & myfunction() & "'"

 

[gthandle]

First of all Thanks for all your help.

It doesn't works. It calls only once. That means if the first number is 9 it will always use the 9 to all the records processed.

If I want to pass a field to this function. HOW ?. I tryed and it didn't work.

"UPDATE

set field1 = '" & myfunction([field1]) & "'"

Here is my function code:

public function myfunction(byval xfield as variant) as variant
dim dummy
dim g

for g = 1 to len(xfield)
if isnumeric(mid(xfield,g,1)) then
dummy = dummy & mid(xfield,gt,1)
endif
next g
myfunction = dummy

end function

 

[BSeefelt]

I'm not sure, but maybe you need to pass field1.Value to the function and not field1.

 

[aiyoyo]

Hi, gthandle,
Try to write your myfunction in access mdb file, then use the SQL:
"UPDATE

set field1 = myfunction()"

if you want to pass a field, the SQL should be:
"UPDATE

set field1 = myfunction([field1])"

 

[gthandle]

It works in MS Access mdb BUT I want to use within my application in VB. This application has to open dBase,Excel,MS Access or flat file. So that is why I have to find a way to make it work in VB. "SQL statement".

 

[aiyoyo]

Oh, I see what you want to do finally.
if u want to work with so many kinds of data destination, the only way is to generate data one by one in a loop.

 

[temuchin]

check this code:

Private Sub Command1_Click()
Dim mcnn As New ADODB.Connection
Dim mstrSQL As String, mdblData As Double
mcnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\WINDOWS\Desktop\getme.mdb;Persist Security Info=False"
mcnn.Open
mdblData = Val(Me.Text1.Text)
Me.Text1.Text = CStr(GetMe(mdblData))
mstrSQL = "update table1 set ngetme = " & GetMe(mdblData)
mcnn.Execute mstrSQL
Set mcnn = Nothing

End Sub
Public Function GetMe(ByVal tdbl As Double) As Double
Dim mstrRV As String, mintI As Integer
For mintI = 1 To 6
tdbl = tdbl + (tdbl * mintI)
Next mintI
GetMe = tdbl
Exit Function
End Function

 

[gthandle]

As I indicated, It repeat the same over and over and all the records has the same value, Not what I want.

Here is what I mean.

Private Sub Command1_Click()
Dim mcnn As New ADODB.Connection
Dim mstrSQL As String, mdblData As Double
mcnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\WINDOWS\Desktop\getme.mdb;Persist Security Info=False"
mcnn.Open
mstrSQL = "update table1 set ngetme = " & GetMe([a field in table1])
mcnn.Execute mstrSQL
Set mcnn = Nothing

End Sub
Public Function GetMe(ByVal tdbl As variant) As variant
randomize
dim r

r = rnd(len(trim(tdbl)))
GetMe = left(tdbl, r)

End Function