SQL 7 DTS cannot access an MS Access database on the network 
Author Message
 SQL 7 DTS cannot access an MS Access database on the network

We have a DTS package that needs to insert records into an Access table
located on the network.  It works fine when run from Enterprise Manager;
however when scheduled thru SQLAgent it bombs out with an "Jet engine error:
Cannot locate the database or another user has locked the database" error.
This error is bogus because the Access DB is available and has no locks on
it.

We tried the fully qualified servername-share name-path also, but still no
good.  Interestingly, if I copy the Access DB to a local disk and run the
job, it works just fine.

What am I missing here?  I hope this is not a limitation!

Thanks,
Arun



Wed, 15 Oct 2003 04:30:25 GMT
 SQL 7 DTS cannot access an MS Access database on the network

Hi!

Check what user account the SQL server agent uses. If it uses LocalSystem
which is default, it will have access to all local files but no remote ones.
You need to change it to a domain user with the correct permissions.

Hope that helps,
Kalle Dahlberg


Quote:
>We have a DTS package that needs to insert records into an Access table
>located on the network.  It works fine when run from Enterprise Manager;
>however when scheduled thru SQLAgent it bombs out with an "Jet engine
error:
>Cannot locate the database or another user has locked the database" error.
>This error is bogus because the Access DB is available and has no locks on
>it.

>We tried the fully qualified servername-share name-path also, but still no
>good.  Interestingly, if I copy the Access DB to a local disk and run the
>job, it works just fine.

>What am I missing here?  I hope this is not a limitation!

>Thanks,
>Arun



Wed, 15 Oct 2003 05:18:02 GMT
 SQL 7 DTS cannot access an MS Access database on the network
I would like to add my two cents worth to this...

I am fairly new to SQL, but have already encountered this problem as well.
What is very strange is that if I set the database location to a local drive
name (Drive F), the DTS package executes without a problem, but if I use the
server drive name(\\server3\Drive F\) , I get a message about not having
permissions to view the file's contents.  Same scenario as Arun previously
outlined.

I have verified that the network drive is shared and that the user account
for SQL Agent has proper domain rights, but I still have to locate the
Access database on the same server.  It is a HUGE risk to allow SQL to run
as a domain administrator, but may not have any more options.

I'd hate to transfer all Access databases to my SQL server and have to allow
public access to it.  Can anyone shed any light on a another work around or
solution?

TIA,

Moses{*filter*}ey


Quote:
> Hi!

> Check what user account the SQL server agent uses. If it uses LocalSystem
> which is default, it will have access to all local files but no remote
ones.
> You need to change it to a domain user with the correct permissions.

> Hope that helps,
> Kalle Dahlberg


> >We have a DTS package that needs to insert records into an Access table
> >located on the network.  It works fine when run from Enterprise Manager;
> >however when scheduled thru SQLAgent it bombs out with an "Jet engine
> error:
> >Cannot locate the database or another user has locked the database"
error.
> >This error is bogus because the Access DB is available and has no locks
on
> >it.

> >We tried the fully qualified servername-share name-path also, but still
no
> >good.  Interestingly, if I copy the Access DB to a local disk and run the
> >job, it works just fine.

> >What am I missing here?  I hope this is not a limitation!

> >Thanks,
> >Arun



Sun, 19 Oct 2003 00:57:41 GMT
 SQL 7 DTS cannot access an MS Access database on the network


Quote:
> I would like to add my two cents worth to this...

> I am fairly new to SQL, but have already encountered this problem as well.
> What is very strange is that if I set the database location to a local
drive
> name (Drive F), the DTS package executes without a problem, but if I use
the
> server drive name(\\server3\Drive F\) , I get a message about not having
> permissions to view the file's contents.  Same scenario as Arun previously
> outlined.

Do you have a share called \Drive F\?

The administrative share is usually F$.

You might want to try \\server3\F$ and see if that works.

Quote:
> I have verified that the network drive is shared and that the user account
> for SQL Agent has proper domain rights, but I still have to locate the
> Access database on the same server.  It is a HUGE risk to allow SQL to run
> as a domain administrator, but may not have any more options.

> I'd hate to transfer all Access databases to my SQL server and have to
allow
> public access to it.  Can anyone shed any light on a another work around
or
> solution?

> TIA,

> Moses{*filter*}ey



Sun, 19 Oct 2003 12:34:45 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. MS Access 7 Database on Network and VB5 Multi User Access Problem

2. SQL Server access on Novell network without Client for MS Networks

3. Cannot access SQL Server 6.5 SP4 databases via Access 97

4. Exporting a SQL Server table as a MS Access database from DTS

5. Cannot Access MS Access DB via Web

6. connect to the MS Access database without using the ODBC driver for MS Access

7. 23108-NY-NEW YORK-MS ACCESS-ORACLE-SYBASE-MS SQL Server-MS Office-Database Integ

8. 23108-NY-NEW YORK-MS ACCESS-ORACLE-SYBASE-MS SQL Server-MS Office-Database Integ

9. How to maintain MS Access database without MS Access

10. Regarding Accessing External database using MS-Access and MS-Visual Basic

11. Cannot access DTS package in SQL 7 after saving in SQL 2000

12. MS-Access 97 to SQL Server to MS-Access 97


 
Powered by phpBB® Forum Software