Office 2000: excel to access automation and parameters

[Matt.]

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