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
spreadsheet. 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("DestinationObjectN ame").Value
= cdl.FileName

'Run the export
DoCmd.Hourglass True
dtsPackage.Execute
DoCmd.Hourglass False
....

Any help would be greatly appreciated.
Thanks,
Mike

Mike,

I have had problems before with the spaces in directory names, although its
been awhile since I've done that. Anymore I don't put spaces in my
directory names or filenames. You might try changing that and see how it
works.

HTH,

Raymond Lewallen

Removing the spaces didn't seem to help. Any other ideas?

The account you are running the DTS package under, or the account setup to
execute the job, is this a domain account with the correct privliges to
access that UNC share and directory?

"Mike Nygard" <nygard (AT) mr (DOT) net> wrote

I am running the ADP through a Citrix connection to the application
server. That login is a domain account with the correct privileges.
I have tried saving the file to a directory on the application server
as well as on the database server. Niether works. I also tried
changing the user ID that the SQL Server Service runs as to my own
domain account. After this I restarted the service but did not reboot
anything. Did not appear to make a difference. I think it must be an
account privileges issue, but I can't seem to put my finger on it.

Thanks for your responses, but I have abandon trying to get DTS to
work in this environment. I got it to work by simply using a
recordset, an Excel application object and "CopyFromRecordset".
Should have done it that way to begin with. It was much simpler.