Thanks Charles!

I have managed to fire a UDF referenced in an Access Module from an Access
automation session triggered from Excel. Here's the code I use:

Dim acApp As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase
("\\hserver01\hworking\pcntrl\FORECAST\xAmcan2003. mdb")
acApp.Run ("WeeklyUtilization") 'procedure found in Module 1 of the
Access database
acApp.Quit
Set acApp = Nothing

The procedure WeeklyUtilization calls the function WeekNumber which is a
UDF. The procedure deletes and then repopulates a table.

It has been suggested to me previously that I cannot add a field to a
recordset. If this is so, then how do I create the field I need? For
example, if the value of ProDate is 2003/12/01, I want a YearWeek text field
that looks like 2003-49. An example (even aircode) would be much
appreciated.

I'll investigate the querydef option, as I've never used it before.

Thanks again,
Matt.


"Charles Williams" wrote in message
...
Hi Matt,

I would use DAO/ADO to create/revise a Querydef that you pass the sql to,
(you dont need to start access via automation etc), and then create a
recordset from the querydef.

However you will have a problem with the user-defined function, because I
do
not think it is possible to get Dao/Ado/Automation to utilise a
user-defined
function built into the query, even if stored in both Access or Excel. I
spent two days recently trying to do this and concluded that it was not
possible (other good authority also thought so).

The alternative way I finally used is to loop through the recordset and
execute the function for each record.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm


"Matt." wrote in message
.. .
Sorry for the repost, but I'm hoping the question is difficult, rather
than
everybody's ignoring me..... ;-)

Hi all!

I'm really stuck on this. The problem I'm trying to solve is two fold.
The
first is that I need to pass a value entered by a user in Excel to a
query
residing in Access. Then I need to populate a recordset with the result
for
later processing. The second problem is the query MUST use a User
Defined
Function (WeekNumber). My non-working code is below.

I'm using automation to resolve the UDF problem. The problem is
acApp.Run
expects the name of a module (or function within), and I want to run the
SQL
statement I've built. I could write the query into an Access module if
I
knew a way to pass the user entry from Excel to Access. A day's worth
of
Google searching and testing has failed to provide me with an answer.

Any help greatly appreciated.

cheers,
Matt.
========================================

Sub GetQuality()
On Error GoTo ErrorHandler
Dim strJob As String
Dim strSQL As String
Dim acApp As Object
Dim rsJobData As ADODB.Recordset
strJob = "1240" 'User entry at a later date

strSQL = "SELECT [Job #], Year(ProDate) & '-' & WeekNumber(ProDate) as
YearWeek, " _
& "SUM([Cast Shots]) AS CastShots " _
& "FROM Production " _
& "WHERE LEFT([Job #],4) = " & strJob & " " _
& "AND WeekNumber(ProDate) = WeekNumber(Date())-11 " _
& "AND Year(ProDate) = Year(Date()) " _
& "GROUP BY [Job #], Year(ProDate) & '-' & WeekNumber(ProDate)"

MsgBox strSQL

Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase
("\\hserver01\hworking\pcntrl\FORECAST\xAmcan2003. mdb")
'Set rsJobData = acApp.Run(strSQL)
'While Not rsJobData.EOF
' MsgBox "Job: " & rsJobData("Job #") & "YearWeek: " &
rsJobData("YearWeek")
' rsJobData.MoveNext
'Wend

SubQuit:
acApp.Quit
Set acApp = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Description, vbCritical
GoTo SubQuit

End Sub