Answer: User Defined Function in Query called from outside Access 
Author Message
 Answer: User Defined Function in Query called from outside Access

I have a query in an Access database that I wanted to pull into Excel 97
using some VBA code in Excel.  The method of using DAO functions to
retrieve the query (see MS Knowlegebase Article Q147739) fails because I
have a user defined function (UDF) in an Access VBA module as one of the
fields in the query.  After searching the Access newsgroups, it appeared
that the only solution was to rewrite the query such that the necessary
fields were pulled into Excel, paste the UDF into an Excel module, and
have the calculations run in Excel.

There was one mention that I found saying something about using
automation, but was very vauge.  From my understanding DAO equates to
automation, but that's just me.  Anyway, I decided to dink around and
have fortunately come across the solution to my problem.

For the benefit of others, I am posting my solution.
Note:  This should apply equally well to other COM applications (e.g.,
VB).

-Chris

I have a Access database with a query, one of whose fields is "Expr1:
AddTwo([MyVal1],[MyVal2])".  This calls my UDF:

Public Function AddTwo(v1 As Variant, v2 As Variant) As Variant
    AddTwo = v1 + v2
End Function

To pull the data into Excel I can use the following procedure.

Sub GetQueryDefWithFcn()
    'Excel 97 VBA procedure
    'Set reference to Access 8.0 Object Library
    'Set reference to DAO 3.5 Library (may not need this, but I have it
set)

    Dim acApp As Access.Application
    Dim Db As Database
    Dim Qd As QueryDef
    Dim Rs As Recordset
    Dim Ws As Worksheet
    Dim i As Long, j As Long
    Dim dbPath As String

    Set acApp = CreateObject("Access.Application.8")

    'get Access database file name and open it
    dbPath = Application.GetOpenFilename("All Files (*.*), *.*", , _
             "Choose DB", "Open", False)
    acApp.OpenCurrentDatabase dbPath
    Set Db = acApp.CurrentDb
    Set Qd = Db.QueryDefs("liquids")
    Set Rs = Qd.OpenRecordset()

    Set Ws = ActiveSheet

    'Add headings to first row of Excel worksheet
    For i = 0 To Rs.Fields.Count - 1
        Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
    Next

    'Make headings bold
    Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold =
True

    'go to the end and back to the start of _
            the recordset so you get the correct record count
    Rs.MoveLast
    Rs.MoveFirst

    'Copy the data
    Ws.Range("A2").CopyFromRecordset Rs

'    'Alternatively, loop through the records to fill in the data
'    For i = 1 To Rs.RecordCount
'        For j = 0 To Rs.Fields.Count - 1
'            Ws.Cells(i + 1, j + 1).Value = Rs.Fields(j).Value
'        Next j
'        Rs.MoveNext
'    Next i

    Ws.Columns.AutoFit
    Range("A1").Select

    'Clean up
    Rs.Close
    Qd.Close
    Db.Close
    Set acApp = Nothing
    Set Ws = Nothing
    Set Rs = Nothing
    Set Qd = Nothing
    Set Db = Nothing
End Sub



Sun, 11 Nov 2001 03:00:00 GMT  
 Answer: User Defined Function in Query called from outside Access
Dear Chris:

If you're ever in Portsmouth, VA, I owe you a beer! This solves a big
problem for me. Thanks,

--
Lisa Z. Morgan
Lairhaven Enterprises
MS Office Development



Sun, 11 Nov 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. calling access querydef that calls a user defined access function

2. Accessing a Microsoft Access Query with a user defined function in VB.

3. User-defined Function Procedure in Access Query

4. executing Access query containing a user-defined function through VB

5. Executing Access query containing a user-defined function through VB

6. Calling a User-Defined Funtion ina query

7. Access97: User defined variant function treated as string in query

8. Calling a Function in an MS Access Class/Form/Module from an outside application

9. My user-defined functions stopped working in a query

10. HELP: User defined function inside query

11. user defined function in query

12. Use of User Defined Function in a query

 

 
Powered by phpBB® Forum Software