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
>
>



Relevant Pages

  • Jet OLEDB: Database Locking Mode
    ... Database Locking Mode is set to 1 in the Advanced tab of the ... Connection to my Access MDB on my import DTS Local Package. ...
    (microsoft.public.data.ado)
  • Re: DTS to update values on second pass?
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... www.SQLDTS.com - The site for all your DTS needs. ... >>>but our clients prefer mdb files for their purposes. ... >>>that employees ID number, first and last name, etc. but the employee's ...
    (microsoft.public.sqlserver.dts)
  • DTS Datasource stays open
    ... next-to-last dts step we zip the mdb resulting in a 25mb file. ... datasource (an access database) so I can in the same dts package delete ...
    (microsoft.public.sqlserver)
  • DTS
    ... in der MDB habe ich eine Spalte mit Datentyp Datum, ... In der Sql-Server Tabelle ist diese Spalte vom Typ smalldatetime. ... Der Import mittels DTS schlägt fehl, weil der Spalteninhalt so nicht kopiert ...
    (microsoft.public.de.sqlserver)