[AccessD] Use module function in query from vb.net

>> But being able to get to an Access module function from ado.net seems
just as necessary as getting to user functions in SQL server.
But a SQL Server is "running", while an Access mdb just "sits there".  An
mdb is just a file/data store, it would have to be instantiated as an
application in order to run functions in its code modules.  

Hth
Steve


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Phil Jewett
Sent: Monday, December 26, 2005 5:12 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Use module function in query from vb.net

I am trying to retrieve an Access query using ado.net that includes a module
function for a column (actually several columns).  The query works fine from
within Access, but when called from ado.net it fails with the error
"Undefined function 'GetMyDesc' in expression".  
 
The access query:
 
MyQuery:  "select GetMyDesc([thecode]) as MyDescription.... from
[MasterTable]"
 
The function GetMyDesc is a public function in a module:
 
Public Function GetMyDesc(ByVal ID As Long) As String
    Dim HoldID As Long
    HoldID = Nz(ID, 0)
    GetMyDesc= Nz(DLookup("CodeDesc", "CodeTable", "ID = " & HoldID),
"")
End Function
 
This is my ado.net code:
 
cmd.CommandText = "MyQuery"
cmd.CommandType = CommandType.StoredProcedure Try FoundDR =
cmd.ExecuteReader(CommandBehavior.CloseConnection)
Catch e As Exception
MsgBox("Error: " & e.Message)
End Try
 
 I have tried using a command type of both stored procedure and plain text,
with the same error resulting.
 
As for the reason for doing this in the first place, the MasterTable has
14 or so foreign keys, and rather than join 14 references to the code lookup
table, I use the function to retrieve the code descriptions.  But being able
to get to an Access module function from ado.net seems just as necessary as
getting to user functions in SQL server.
 
Phil Jewett
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com