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

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

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

I dont think I understand the problem:

I thought you wanted to use your UDF in an access query to populate a
recordset (cannot be done AFAIK).

I dont understand why you want to add a field to a recordset (you can do
that for an updateable recordset, and you can use your UDF to update a
recordset), if you have already added the field in a maketable procedure.

To add a field to the recordset resulting from a query you just add a field
to the SELECT.

I thought you wanted to populate a recordset from a query that contained a
parameter that was input by the user:
if so just slot the parameter value into the sql string (something like
WHERE fieldname="Paramvalue")

if the parameter value is the result of using your function on user input
then you just call the function in your excel VBA and put the result in the
sql.

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

"Matt." wrote in message
.. .
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

OK, I'll try and explain the problem from beginning to end.

The user wants a chart showing the last 12 fiscal weeks of data (let's say
scrap). The user needs this data for multiple jobs, but only one job at a
time. The chart must be displayed in Excel. The data resides in Access.

Our fiscal calendar does not allow for a week 53, so format("ww",date) isn't
an option. I have copied and installed the MS function WeekNumber into
Access (and Excel), and have it working fine in a series of Access queries.

I have written a query in Access populating a temp table with the 12 weeks
of data, and this query uses the WeekNumber function. However this query
only works because the Job number is hardcoded into the query.

I have also written a function that executes the query, so it can be run
from an Access automation. And it does.

Now, what I would like is to be able to have the user, from Excel, enter a
job number, pass that value to the Access function or query, and either
populate a recordset or a temp table (if I populate the table, I can
populate a recordset using VBA later).

If I can't pass the Job number, then my only other option is to write a
query for each job, and a function for each query, and then a SELECT
statement to decide which function to run.

If I'm barking up the wrong tree, and there's another way to do this, please
let me know.

Thanks in advance for your time. I really do appreciate it.

cheers,
Matt.


"Charles Williams" wrote in message
...
I dont think I understand the problem:

I thought you wanted to use your UDF in an access query to populate a
recordset (cannot be done AFAIK).

I dont understand why you want to add a field to a recordset (you can do
that for an updateable recordset, and you can use your UDF to update a
recordset), if you have already added the field in a maketable procedure.

To add a field to the recordset resulting from a query you just add a
field
to the SELECT.

I thought you wanted to populate a recordset from a query that contained a
parameter that was input by the user:
if so just slot the parameter value into the sql string (something like
WHERE fieldname="Paramvalue")

if the parameter value is the result of using your function on user input
then you just call the function in your excel VBA and put the result in
the
sql.

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

"Matt." wrote in message
.. .
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

Hi Matt,

OK: here are some suggestions:

- use DAO/ADO to update a one col one row access table so that it contains
the value, and then use that in your access procedure.

or
- write your make-table automation procedure to create a table for all jobs,
then query the table using the value for the job you want.

or
- write sql that gets the data for the job the last 4 months (or long enough
to make sure that you get at least the last 12 fiscal weeks depending on
your rules for fiscal weeks), use DAO/ADO to create a recordset, then loop
down the recordset filtering out the last 12 fiscal weeks using the UDF and
pass the data to Chart.

The last approach is probably more efficient than the other two because you
dont have the overhead of starting access.
Given what you have already done the first approach is probably the
simplest.

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

"Matt." wrote in message
.. .
OK, I'll try and explain the problem from beginning to end.

The user wants a chart showing the last 12 fiscal weeks of data (let's say
scrap). The user needs this data for multiple jobs, but only one job at a
time. The chart must be displayed in Excel. The data resides in Access.

Our fiscal calendar does not allow for a week 53, so format("ww",date)
isn't
an option. I have copied and installed the MS function WeekNumber into
Access (and Excel), and have it working fine in a series of Access
queries.

I have written a query in Access populating a temp table with the 12 weeks
of data, and this query uses the WeekNumber function. However this query
only works because the Job number is hardcoded into the query.

I have also written a function that executes the query, so it can be run
from an Access automation. And it does.

Now, what I would like is to be able to have the user, from Excel, enter a
job number, pass that value to the Access function or query, and either
populate a recordset or a temp table (if I populate the table, I can
populate a recordset using VBA later).

If I can't pass the Job number, then my only other option is to write a
query for each job, and a function for each query, and then a SELECT
statement to decide which function to run.

If I'm barking up the wrong tree, and there's another way to do this,
please
let me know.

Thanks in advance for your time. I really do appreciate it.

cheers,
Matt.


"Charles Williams" wrote in message
...
I dont think I understand the problem:

I thought you wanted to use your UDF in an access query to populate a
recordset (cannot be done AFAIK).

I dont understand why you want to add a field to a recordset (you can do
that for an updateable recordset, and you can use your UDF to update a
recordset), if you have already added the field in a maketable
procedure.

To add a field to the recordset resulting from a query you just add a
field
to the SELECT.

I thought you wanted to populate a recordset from a query that contained
a
parameter that was input by the user:
if so just slot the parameter value into the sql string (something like
WHERE fieldname="Paramvalue")

if the parameter value is the result of using your function on user
input
then you just call the function in your excel VBA and put the result in
the
sql.

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

"Matt." wrote in message
.. .
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