Solved

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

Posted on 2003-11-27
5
2,195 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:ssaravanamoorthy
5 Comments
 
LVL 10

Expert Comment

by:jobrienct
ID: 9833057
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
 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9834594
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
 
LVL 2

Expert Comment

by:dom_cath
ID: 9834686
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
 
LVL 26

Accepted Solution

by:
Alan Warren earned 50 total points
ID: 9835729
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
 
LVL 2

Expert Comment

by:dom_cath
ID: 9967111
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question