Re: Jet OLEDB: Database Locking Mode

From: Marc Doolittle (marcd_at_info-link.com)
Date: 01/25/05


Date: Tue, 25 Jan 2005 09:33:04 -0500

You can make sure your DTS is not locking by putting:

;Mode=Share Deny None

in your connection string.

The other modes are:
  a.. Read-Read only.
  ReadWrite-Read and write.
  Share Deny None-Neither read nor write access can be denied to others.
  Share Deny Read-Prevents others from opening in read mode.
  Share Deny Write-Prevents others from opening in write mode.
  Share Exclusive-Prevents others from opening in read/write mode.
  Write-Write only.

 The lock by the Access user is a local Access setting. My guess is that the
local user has Access set to open mdb files exclusively. You might try
running a DTS job that perpetually runs (and does nothing) and keeps the
database open with Share Exlusive. This would generate an error for any user
who tried to open the mdb file exlusively.

"Andy Willis" <andrewrwillis@blueyonder.co.uk> wrote in message
news:#VCOdpsAFHA.2552@TK2MSFTNGP09.phx.gbl...
> The Jet OLEDB: Database Locking Mode is set to 1 in the Advanced tab of
the
> Connection to my Access MDB on my import DTS Local Package.
> I have not been able to find what this setting is or any other for that
> matter
>
> My problem is that my DTS package runs fine as a scheduled job if noone is
> in the MDB database but fails if someone is in the MDB. This makes me
think
> that for some reason the SQL DTS job is trying to open the Access database
> exclusively before it does the data import.
>
> Also what should the Mode setting be as a number for shared access.
>
> I guess this could also be a problem amending the LDB if someone else has
> already created it, but I think that unlikely because if the LDB can be
> created (scheduled job runs fine of noone in MDB) then you would expect it
> to be able to be amended.
>
> Regards
> Andy Willis
>
>