Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Novosibirsk, Russia

    Unanswered: Shared access to an Access 2000 database in a network directory


    My application (a VB executable) uses a Microsoft Access database to store various information entered by the user. The application and the database are located on the network so many users can access it simultaneously. Just recently, I implemented persistent connections so the database connection remains open for the entire life of the application (which speeds things up considerably compared to connecting every time something needs to be done). Since then, only one user seems to be able to connect at any single time. As far as I know, Access allows about 20-30 simultaneous user connections without
    a problem (and can do much more), so why does it lock the database exclusively? Here's the code I use to connect:


    Set m_DbConnection = New ADODB.Connection
    With m_DbConnection
    sDSN = "Provider=Microsoft.Jet.OLEDB.4.0;"
    sDSN = sDSN & "Data Source=" & sDb & ";"
    sDSN = sDSN & "Persist Security Info=True"

    .ConnectionString = sDSN
    .CommandTimeout = 8 'wait 8 secs before exiting commands
    .ConnectionTimeout = 15
    .CursorLocation = adUseClient
    .IsolationLevel = 4096

    .Mode = adModeShareDenyNone Or adModeReadWrite
    End With

    Exit Function
    If Err.Number = -2147467259 Then
    Dim n As Long
    Dim uword As Long, lword As Long
    Dim sign As Long
    Dim vErr As ADODB.Error
    If Not (m_DbConnection Is Nothing) Then
    If m_DbConnection.Errors.Count > 0 Then
    For Each vErr In m_DbConnection.Errors
    n = vErr.NativeError

    If (n < 0) Then
    sign = -1
    sign = 1
    End If

    lword = (Abs(n) And &HFFFF&) * sign
    uword = (Abs((n / 2 ^ 16 - 1)) And &HFFFF&) * sign

    HandleError ERR_LOG_DETAIL_LONG Or ERR_TARGET_LOG, vErr.Number, vErr.Description & " (native error: " & n & ", minor error code: " & uword & ", major error code: " & lword & ", Jet IDA Number (DAO Error): " & vErr.SQLState & ")", vErr.Source, "basDb::PrepareConnection"
    Next vErr
    HandleError ERR_LOG_DETAIL_LONG Or ERR_TARGET_LOG, Err.Number, Err.Description, Err.Source, "basDb::PrepareConnection"
    End If
    HandleError ERR_LOG_DETAIL_LONG Or ERR_TARGET_LOG, Err.Number, Err.Description, Err.Source, "basDb::PrepareConnection"
    End If

    DisplayDetailedError Err.Number, Err.Description, Err.Source, "basDb::PrepareConnection"
    End If
    Resume PROC_Exit


    If another user already connected to the db, I get this error:

    Error #-2147467259: Unable to use ''; file is already in use. (native error: -67044352, minor error code: -1024, major error code: -1024, Jet IDA Number (DAO Error): 3045) [source Microsoft JET Database Engine, procedure basDb::PrepareConnection]

    (the first error is an ADO error, the native error is what the data provider returns, and other things are just what I got after doing a few searches on MSDN).

    I first thought that the problem is about the Mode property setting of the connection object, but MSDN describes adModeShareDenyNone constant as the less restrictive, full database sharing mode, and combining it with adModeReadWrite seems the most logical solution to allow others to read/write data.

    I tried to change the CursorLocation property to adUseServer, but it had no effect.

    Have any of you had the same problem or knows the solution? I will appreciate any help I can get!

    Thanks a lot in advance!


  2. #2
    Join Date
    Mar 2003
    have you set the default open mode in the access db to shared?

  3. #3
    Join Date
    Dec 2002
    Préverenges, Switzerland
    a backend A-db easily handles multiple frontend clients. you seem to be aiming for multi-user access to a shared frontend.

    this risks to be ...problematic!

    the multi-user stuff is juggled by the frontend.


  4. #4
    Join Date
    Jun 2003
    Novosibirsk, Russia

    I got it

    Hi and thanks to all those who replied!

    izyrider, although the frontend is located in a network directory, it doesn't act as a COM server or anything like it, it's just an executable file which is loaded by each user. For this reason, no resources are shated between different clients.

    After a few days of racking my brains and searching high and low for the solution, I accidentally bumped into an MSDN article which mentioned how Access manages multiple (shared) access to the database. It creates a temporary lock file in the same directory as the database. Since my database was on the network, I checked directory access rights and bingo, it was readonly! Thus, the lock file was never created, and for this reason Access could only establish one connection because the database file was in use while there was no lock file to check for shared access permissions. I changed folder permissions for all users to read-write and it now works perfectly.

    I believe this issue could be touched upon in MSDN, but it seems they are taking it for granted.

    Thanks again, I hope this helps someone,


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts