Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Office 2000: excel to access automation and parameters
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automation to Excel from Access | Excel Discussion (Misc queries) | |||
What replaces the Office 2000 add-in "Access Form" in Office 2003 | Excel Discussion (Misc queries) | |||
Automation from .pdb to excel and then to access | Excel Programming | |||
Access 2000 - Automation example please | Excel Programming | |||
Automation Excel & Access | Excel Programming |