1. path to Access file is lost during scheduled DTS import
I have struggled with this one for a while.
I have a DTS task that imports an Access file into a table in SQL Server.
The Access file has security setup since it is the backend for a HR
application. (it uses a workgroup file)
I defined my Access file connection in DTS by setting the path the mdb file,
supplying user and password and under advanced properties I add the
workgroup file to JetOLEdb:System Database. Once set it works fine.
This is the scenario:
Access file is copied from another server nightly to the server running DTS
DTS task is scheduled and runs each night opening the file and importing
some of its data into SQL Server
Problem:
as soon as a new copy of the Access file is put on the SQL Server, the DTS
task fails, saying the file mdb file is already in use or the user set in
the connection properties is not valid. The only way I can get it back to
work is to open the connection properties, re-set the path (it does show the
right path, but when you 'Browse' to it, it ends up showing the My Documents
folder. Then I have to go into advanced properties, remove the workgroup
file, click OK, go back into advanced properties and add the workgroup file,
click OK and then it works again.
It is not a network/share permission etc. issue as outlined in MS Knowledge
Base Article: How to Run a DTS Package as a Scheduled Job
I think it is either related to the fact that the file is copied each night
(new update date?) or something with the workgroup file.
I added an ActiveX script to set the path before running the import, but it
doesn't matter.
Anyone seen this before? I am clueless and can't run this job
automatically...
AvV
2. Summary reports, Help
3. Setting Access DB file to finished prog during setup or initial run
4. VFPCOM.DLL + ORACLE
5. Runtime error 75 path/file access error
6. Error 80070003
7. Error 75: File/Path Access Error
8. DOMAIN_NAME, RAID1,5 , naming convention
9. --separate path for log files during sql 2000 server installation
10. path/file access error
11. Path/File Access Error
12. Path/File access error
by