How to call a module function in Access 2000 database using VB query recordset?

Hi,
Here goes my code
-------------------------------------------------
Option Explicit
Dim Conn As New ADODB.Connection

Private Sub Form_Load()

    Conn.Provider = "Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\windows\desktop\db2.MDB"
    Conn.Open
   
    Dim adors As New ADODB.Recordset
   
    adors.Open "SELECT  empno from table1 where replace(ucase(dept),' ','')='SW'", Conn, adOpenForwardOnly, adLockReadOnly

End sub

I have written a function named "replace" as public function in Access database module. I get error as undefined 'replace' function. How to solve this problem?

Thanks in advance.
Moorthy.
ssaravanamoorthyAsked:
Who is Participating?
 
Alan WarrenApplications DeveloperCommented:
Hi ssaravanamoorthy,

As far as I am aware functions internal to a Jet database are only exposed if  the application is loaded, therefor they are not able to be referenced by a connection.

Alan
0
 
jobrienctCommented:
First of all are you aware that replace() is an internal function? try naming it something else and see if that clerars it up.

JOhn
0
 
Benjamin_LukCommented:
Please Delete 'Private' at the head of your function code, and add 'Public' at the begin of your code like:

Public Function Replace()
.........'Code
End Function

Regards

Ben
0
 
dom_cathCommented:
To open an Access Function you do the following:

VariableYouWantDataReturnedInto = FunctionName(DataYouArePassingToTheFunction)

eg

CurCostIncludingTax = CalcTax(CurCost)

If you are not expecting data back from the function then it is not a function but a sub.  Rename the Function to a sub and call it.

Call CalcTax(CurCost)

Dom
0
 
dom_cathCommented:
I know this is closed but if it help no you cannot ref functions externally but you can ref macros.  And a macro can run a function.

Dom
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.