This is typically an issue regarding permissions for the SQL Server Agent account.

Hope this helps,

Alex

----signature below----
Majority rule don't work in mental institutions

My Crummy Web Page

I am running it as a SA account, is it regarding permissions issue in SQL Agent Account?

I tried to write the store procedure instead of using the DTS.  I have an error on it and it keeps saying the connection erro.  Can anyone helps?

Create Procedure [dbo],[spXX] As
truncate table [DatabaseName].[dbo].[TableName]
insert into [DatabaseName].[dbo].[TableName]
Select
Convert (Char(10),GetDate(),101) as as_of,
Field1,
Field2,
Field3
From OpenDataSource
('SQLOLEDB','Provider=Microsoft.Jet.OLEDB.4.0;Data Source = S:\FolderName\database\databaseName')
Go

Thanks.

Your S: drive is probably a mapped drive, which your SQL Agent Account is unaware of.  You might try using the UNC path to the MDB.

So, I will need to change this part?

From OpenDataSource
('SQLOLEDB','Provider=Microsoft.Jet.OLEDB.4.0;DataSource=UNCpath')

Thanks.

Yes, try using the format of \\YourServerName\ShareName\database\databaseName

Thank you very much. I will try to run the DTS tonight.  

I found the DTS is not having any problem, but somehow connecting to the Novell Network at night is causing the error.  

I tested it by copying the entire database from the Novell Network to the Local C Drive and schedule to run it again, it works great.  

I think my solution is either ask the Network guy to help me or create a step to copy the S drive Access.mdb to the C drive.  Before I bug the Network guy, I am wondering can I write a step to copy the S drive Access.mdb ot the C drive? Is it workable?  If so, can anyone guilde me how to do this?
Thanks a lot.