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

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

    Hi!

    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
    .Open
    End With

    PROC_Exit:
    Exit Function
    PROC_Error:
    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
    Else
    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
    Else
    HandleError ERR_LOG_DETAIL_LONG Or ERR_TARGET_LOG, Err.Number, Err.Description, Err.Source, "basDb::PrepareConnection"
    End If
    Else
    HandleError ERR_LOG_DETAIL_LONG Or ERR_TARGET_LOG, Err.Number, Err.Description, Err.Source, "basDb::PrepareConnection"
    End If

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

    Stanislav

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

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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.

    izy

  4. #4
    Join Date
    Jun 2003
    Location
    Novosibirsk, Russia
    Posts
    2

    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,

    Stanislav

Posting Permissions

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