Error in Accessing - MSAccess module from VB

[altctldel]

I had two fields(Field1 , Field2) in query. I want to replace a specific character occurance (Let the character be @) in FIELD2 by value of FIELD1.

there is no Built-in REPLACE function in ACCESS 2000.

So i tried writing a module for
replacing( Module Name Rep()) and
successfully able to execute the query in Access 2000.

When I tried to execute same query from Visual Basic, It displayed an
ODBC ERROR: Undefined Function Rep().

 

[Guest_imported]

use the update SQL Statement....

Update TableName, set Field2 = Field1 Where (Blah Blah Blah)

 

[sunaj]

Hi,

Are you programming in VBA (in Access) or VB6 (Visual studio)?
This forum is dedicated to VB6 - and I don't know very much about VBA.
If you want to use a function in another module you need to declare it as 'Public', that is certainly true for VB6 and I think it also goes for Access VBA. So try
-----------------------------------------------
Public function MyFunc(MyInput as string) as string
'....
MyFunc = ...
End Function
-----------------------------------------------
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[altctldel]

Sunaj - Iam not able to execute query containing the module(public scope) in MSAccess from Visualbasic.

When i try to execute the query using ODBC, it throws an error.
Error Message: "Unknown Function"

 

[Norris68]

You will not be able to do it.

I assume that you have an Access query that contains a function. This works fine in Access but will not work if you try to run the same query in any other development system. Access does not always work in a SQL-compliant way.

The only way around it to try to use standard SQL functions to perform the functionality you require (which JET doesn't implement). If you were using SQL Server you could write a user-defined function that CAN be called in a query - but then you would find there are no useful functions that are equivalent to things like the IIF Access function. However, SQL SERVER does has a STUFF function that would do almost exactly what you want if you also used the PATINDEX function - again, JET does not implement these functions.

The only other way around it would be to manipulate the data in a temporary table/recordset.

 

[tedsmith]

If you are using Access2000 MDB, check you have your ADO reference set to 3.6 and not earlier.
If you havent got it, you need a service pack.