Error attempting to execute a DTS package from an ADP

From: Mike Nygard (nygard_at_mr.net)
Date: 04/07/04 

Date: 7 Apr 2004 11:46:30 -0700

Hope someone knows the answer to this. I have looked at several other
similar postings as well as the Microsoft web site and I'm at my wits
end. I'm simply trying to transfer data from a SQL table to an Excel
spread***. Here is the error:
"The Microsoft Jet database engine could not find the object '<my UNC
file name>'. Make sure the object exists and that you spell its name
and the path name correctly. (-2147217865)"

Other pertinent facts:
-SQL Server 2000 is installed on a Windows 2000 machine
-Client ADP app is installed on a Windows 2000 Server with Citrix
-ADP access application (not sure why the error message is from Jet)
-I registered the DTS DLL and support files on the client machine per
Microsoft web site
-I'm using UNC file names
-I installed the SQL Server client tools on the app client machine and
I can run the package fine from there.
-I tried restarting the SQL Server agent using the same user ID as the
one running the client app. No change.
-It runs fine when I'm at my home office where SQL Server and the
client app are on the same machine.

Here is the execution log:
The execution of the following DTS Package failed:
Error Source: Microsoft JET Database Engine
Error Description:The Microsoft Jet database engine could not find the
object '\\appd05\Profiles Data\ABC Campus_Air.xls'. Make sure the
object exists and that you spell its name and the path name correctly.
Error code: 80040E37
\Error Help File:
Error Help Context ID:5003011
Package Name: ENV_AIR_EXPORT
Package Description: (null)
Package ID: {6A4BB951-6DDC-4D22-BEDC-ECAAF6BCE317}
Package Version: {40C9DF3D-20AC-4C83-9185-DDD7BE37D119}
Package Execution Lineage: {8DEDE092-7B85-4581-91AF-00BADDD5E451}
Executed On: AE02
Executed By: nygarm1
Execution Started: 4/7/2004 1:19:59 PM
Execution Completed: 4/7/2004 1:19:59 PM
Total Execution Time: 0.125 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft JET Database Engine
Step Error Description:The Microsoft Jet database engine could not
find the object '\\appd05\Profiles Data\ABC Campus_Air.xls'. Make
sure the object exists and that you spell its name and the path name
correctly.
Step Error code: 80040E37
Step Error Help File:
Step Error Help Context ID:5003011
Step Execution Started: 4/7/2004 1:19:59 PM
Step Execution Completed: 4/7/2004 1:19:59 PM
Total Step Execution Time: 0.109 seconds
Progress count in Step: 0

Here is a segment of my code:
...
       Set dtsPackage = New DTS.Package
       dtsPackage.FailOnError = True
       'Load Data Transformation Serverce (DTS)package definition from
SQL Server
       dtsPackage.LoadFromSQLServer
CurrentProject.Connection.Properties("Data Source"), "<userID>",
"<password>", DTSSQLStgFlag_Default, , , , dtsPackageName, Nothing
       dtsPackage.FailOnError = True
       
       'Avoid threading issues
       intStepCount = dtsPackage.Steps.Count
       For s = 1 To intStepCount
          dtsPackage.Steps(s).ExecuteInMainThread = True
       Next s

       'Pass the profile ID parameter
       dtsPackage.GlobalVariables("gProfileID").Value = strProfileID
       
       'Get the path for the blank Excel template
       Set objCode = New Codes
       strExcelTemplatePath = objCode.getCodeDesc("PATH",
CurrentProject.Connection.Properties("Data Source"))
       'Create the new File from a blank template
       FileCopy strExcelTemplatePath + "BLANK_" + strExportType +
".xls", cdl.FileName
       
       'Tell the DTS package what file name to use
       ' "Microsoft Excel 97-2000" being the name of my connection
object
       ' "cdl.FileName" Being the dynamic file name
       For Each dtsConnection In dtsPackage.Connections
         If dtsConnection.Name = "Microsoft Excel 97-2000" Then
           dtsConnection.DataSource = cdl.FileName
         End If
       Next
       
       ' Now I need to change the source file for the Transformation
Object
         dtsPackage.Tasks(1).Properties("DestinationObjectName").Value
= cdl.FileName
       
       'Run the export
       DoCmd.Hourglass True
       dtsPackage.Execute
       DoCmd.Hourglass False
...

Any help would be greatly appreciated.
Thanks,
Mike