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

Phil,

It will work this way:

Imports System.Data.OleDb

Module myModule
    Sub Main()
        Dim strCnn As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source={{FullPathToYourMdbHere}};" & _
            "User Id=admin;" & _
            "Password="
        Dim strSql As String = _
        "SELECT (DLookup(""CodeDesc""" & _
        ",""CodeTable"",""ID = """ & _
        "& [theCode])) AS MyDescription " & _
        " FROM MasterTable "
        Try
            Dim cnn As New OleDbConnection(strCnn)
            Dim cmd As New OleDbCommand(strSql, cnn)
            cnn.Open()
            Dim rdr As OleDbDataReader = _
                cmd.ExecuteReader(CommandBehavior.CloseConnection)
            While rdr.Read()
                Console.WriteLine(rdr.GetString(0))
            End While
            rdr.Close()

        Catch e As Exception
            MsgBox("Error: " & e.Message)
        End Try
    End Sub
End Module

But anyway I'd use join instead of DLookUp - it should work quicker and it's
more generic way to get lookup values - if it ever happens you'll need to
upsize to SQL Server then you will not need to change your queries....

Shamil

----- Original Message ----- 
From: "Phil Jewett" <pjewett at bayplace.com>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, December 27, 2005 1:12 AM
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