.net – MS Access (MDB) concurrency

concurrencydatabasems-accessms-jet-acenet

For a small project I need to utilize a simple database with very light requirements: few tables, no more than few thousands of records in total, 2 or 3 users. I am working in .NET environment.

As a database server (even those Express editions) seems like a huge overkill in this case, a very simple MDB database could do for most of the requirements. I am however, concerned about concurrency. My idea is to place the .mdb file on a network share and let users access this file from their .NET-based clients. The db is mostly aimed at read-only operations but users will occasionally need to update/delete records as well. If this will not be possible at the time (due to the db being locked or whatever), I can hold the updates on the client and process them at a later time.

The question itself goes along these points:

How are concurrent reads handled in MDB?
How are concurrent updates/deletes handled in MDB?
Is there a concept of locks and how can I leverage it in a .NET app?
Is placing the MDB file on a network share good or horrible idea?

As I am working in .NET, I would also love to know how can I detect any concurrency problems and take appropriate action. I.e., which exception should I catch and what action would you recommend to take?

EDIT: It may be my bad description of the problem, but most answers seem to advise going for a full blown DB server. I do understand the differences and benefits of having a server installation and have in fact implemented a fair number of projects on MSSQL and Oracle. In this question, however, I am only concerned with Access and its concurrency issues, so please do not suggest a db server.

Thanks for your help.

Best Answer

This is an old question, but nobody has ever actually answered it. Here are the questions:

How are concurrent reads handled in MDB?
How are concurrent updates/deletes handled in MDB?
Is there a concept of locks and how can I leverage it in a .NET app?
Is placing the MDB file on a network share good or horrible idea?

The first two questions can basically be answered with one explanation. One key caveat here: the answers I'm giving here are specific to Jet MDBs (and their variants) and do not completely apply to the new file format introduced starting with A2007, i.e., ACCDB format. I have not fully explored the implications of the removal of Jet ULS from the ACE and some of the comments below may assume Jet ULS below the hood. For a lot of things, though, you can substitute "LACCDB file" for "LDB file" and the results will be the same.

1-2) Concurrent reads/updates/deletes

The Jet database engine is often referred to as a "file server" database in that there is no server-side demon managing I/O with the data files on the server. What this means is that all clients using a Jet MDB are reading the file directly.

That is, of course, a recipe for disaster if there's not some mechanism built in for handling concurrent access to the file.

Jet uses a record-locking file, where if your MDB is "MyFile.MDB" the record locking file will be in the same folder and called "MyFile.LDB". The LDB file records what Jet ULS users have the MDB file open, what workstation that user is connected from, and all the information necessary for negotiating concurrency issues.

Now, to those who cut their teeth on client/server database engines, this may seem primitive and dangerous, but at the time the Jet database engine was developed, its purpose was to be used as a desktop database engine for small workgroups, and it was competing with other desktop db engines like xBase and Paradox, both of which used analogous locking files to manage concurrent use of data files from multiple clients.

Within a Jet database file, locks are applied either on data pages (which in Jet 4 were increased to 4K, whereas in Jet 3.x and before, they were 2K), or at the record level if the data table was originally created to use record-level locking. In the early days of Jet 4, record-level locking was found by many to be quite slow, particularly when using pessimistic locking, so a lot of Access developers never used anything but page-level locking (@David Fenton raises hand!).

In fact, when using optimistic locking, you avoid most of the concurrency issues that would come with pessimistic locking.

Some caveats:

from DAO, record-level locking is unavailable, and you only ever get page-level locking.
from DAO, there are a number of options for controlling optimistic/pessimistic locking, in particular the LockEdits argument of the OpenRecordset method, but that also interacts with certain of the setting specified in the OpenRecordset Options argument (e.g., Option dbReadOnly cannot be used with LockEdits). In addition to locking, there are also options for consistent/inconsistent updates, and all of this can interact with transactions (e.g., changes within an uncomitted transaction are not going to be visible to other users and thus will not conflict with them, but it can put read-only locks on the tables involved).

From ADO/OLEDB, these Jet concurrency control structures are going to be mapped onto the relevant functions and arguments found in ADO/OLEDB. Since I use Jet only from Access, I interact with it only via DAO, so I can't advise on how you control these with ADO/OLEDB, but the point is that the Jet database engine offers control of your record locking when accessing it programmatically (as opposed to through the Access UI) -- it's just more complicated.

3) Locks and .NET

I can't offer any advice here, other than that you'd likely use OLEDB as your data interface, but the point is that the locking functionality/control is there in the db engine itself, so there's likely a way to control it via OLEDB. It may not be pretty, though, as it seems to me that OLEDB is designed around client/server architectures, and Jet's file-based locking may not map onto that in an elegant way.

4) MDB on a network share

Jet is very sensitive to the slightest hiccup in any network connection. Because of that, low-bandwidth networks can increase the vulnerability of Jet databases open across a slow connection.

This is because major chunks of the database file have to be pulled across the wire to the local computer's RAM for processing. Now, many people erroneously claim that the entire MDB file is pulled across the wire, or that whole tables are pulled across the wire. This is not true. Instead, Jet first requests the indexes (and requests no more than necessary to fulfill the query) and then from that result determines exactly which data pages are needed and then pulls only those pages. This is surprisingly efficient and fast.

Also, Jet does some very intelligent caching that can mean that a first data request can take a while, but subsequent requests for the same data happen nearly instantaneously because of caching.

Now, if you haven't indexed your tables well, you may end up pulling the whole table and doing a full table scan. Likewise, if you base criteria on client-side functions that are not part of Jet's SQL dialect, you could end up pulling a full table (sorting on, say, Replace(MyField, "A", "Z") is likely to cause a full table scan). But that kind of thing is going to be inefficient with a client/server architecture, too, so it's just common-sense schema design to index things properly and be careful with using UDFs or non-Jet-compatible functions. In general, the same things that are efficient with client/server are going to be efficient with Jet (the major difference being that with Jet you're better off with a persistent connection in order to avoid the overhead of recreating the LDB file, which is significant).

The other thing to avoid is trying to use Jet data across a WiFi connection. We all know how unreliable WiFi is, and it's just asking for trouble trying to work with Jet data across a WiFi connection.

The bottom line:

If you're using an MDB as a data store to serve data from a web server, you should put the data as close to the web server's RAM as possible. That means that where possible, on a disk volume that is attached to the physical web server. Where that's not possible, you want a fast, reliable LAN connection. GB LANs in data centers are pretty common these days and I'd be very comfortable working with Jet data across that kind of connection.

For shared use, e.g., multiple client workstations running a VB.NET desktop app sharing a single Jet MDB as data store, it's pretty safe to have the data file on a reliable file server. Where possible, it's a good idea to put your Jet MDB files on machines that aren't serving multiple purposes (e.g., your domain controller that is running Exchange, SQL Server and acting as file server and print server may not be the best location). Apps like Exchange can badly interfere with file server functionality, and I'd usually recommend never putting MDB files on a server that is multi-tasking as an Exchange server unless it's extremely low volume.

Other considerations:

never try to distribute an MDB on a replicated file system, unless all users are using the same replica. That is, if you have two servers replicating files between them, don't even think about editing the MDB file from both servers. This will corrupt the file almost immediately.
I would recommend against storing any MDB on anything other than a native Windows file system served via native Microsoft SMB networking. This means no Novell, no Linux, no SAMBA. The key reason for this is that there are apparently low-level hooks from Jet into some low-level locking functionality in the Windows file system that are not 100% replicated on other file systsm. Now, I'm very conservative on this, and many competent Access developers have reported excellent results with properly-configured Novell file servers (often there need to be some record-locking adjustments, though that may be less relevant these days -- I don't even know if Novell exists any more!), and blazing performance with Linux-based file servers running SAMBA. I'm cautious on this and would recommend any client against it (this includes various SAN devices, as well, since not a lot of them are Windows-based).
I would never run them on any virtualized file system for the same reasons. However, I've got a client who has been running her single-user Access app under Parallels on a Mac Air for several years now without a single problem. But it's single-user, so the locking issues are going to be relatively minor.

I don't know if that answers your questions or not. It's all based on my 13 years of regular use of Jet as an Access developer and study of the only published book on Jet, the Jet Database Engine Programmers Guide (for Jet 3.5 only). I haven't provided any real citations, but if anybody needs some details on anything I've said, I'll do the research if I can.

Related Solutions

R – MS Access Data Access Limitations

There should no problem. Problems can occur only on concurrent write operations. The locking from MS Access based on file locks in the ldb file. The locks occur only on pages and not on the completely file. Because the locks are in the ldb file and not in the mdb file that there are no problems with parallel reading.

Ms-access – How to use version control with Access development

We wrote our own script in VBScript, that uses the undocumented Application.SaveAsText() in Access to export all code, form, macro and report modules. Here it is, it should give you some pointers. (Beware: some of the messages are in german, but you can easily change that.)

EDIT: To summarize various comments below: ~~Our Project assumes an .adp-file. In order to get this work with .mdb/.accdb, you have to change OpenAccessProject() to OpenCurrentDatabase()~~. (Updated to use OpenAccessProject() if it sees a .adp extension, else use OpenCurrentDatabase().)

decompose.vbs:

' Usage:
'  CScript decompose.vbs <input file> <path>

' Converts all modules, classes, forms and macros from an Access Project file (.adp) <input file> to
' text and saves the results in separate files to <path>.  Requires Microsoft Access.
'

Option Explicit

const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3

' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

dim sADPFilename
If (WScript.Arguments.Count = 0) then
    MsgBox "Bitte den Dateinamen angeben!", vbExclamation, "Error"
    Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))

Dim sExportpath
If (WScript.Arguments.Count = 1) then
    sExportpath = ""
else
    sExportpath = WScript.Arguments(1)
End If


exportModulesTxt sADPFilename, sExportpath

If (Err <> 0) and (Err.Description <> NULL) Then
    MsgBox Err.Description, vbExclamation, "Error"
    Err.Clear
End If

Function exportModulesTxt(sADPFilename, sExportpath)
    Dim myComponent
    Dim sModuleType
    Dim sTempname
    Dim sOutstring

    dim myType, myName, myPath, sStubADPFilename
    myType = fso.GetExtensionName(sADPFilename)
    myName = fso.GetBaseName(sADPFilename)
    myPath = fso.GetParentFolderName(sADPFilename)

    If (sExportpath = "") then
        sExportpath = myPath & "\Source\"
    End If
    sStubADPFilename = sExportpath & myName & "_stub." & myType

    WScript.Echo "copy stub to " & sStubADPFilename & "..."
    On Error Resume Next
        fso.CreateFolder(sExportpath)
    On Error Goto 0
    fso.CopyFile sADPFilename, sStubADPFilename

    WScript.Echo "starting Access..."
    Dim oApplication
    Set oApplication = CreateObject("Access.Application")
    WScript.Echo "opening " & sStubADPFilename & " ..."
    If (Right(sStubADPFilename,4) = ".adp") Then
        oApplication.OpenAccessProject sStubADPFilename
    Else
        oApplication.OpenCurrentDatabase sStubADPFilename
    End If

    oApplication.Visible = false

    dim dctDelete
    Set dctDelete = CreateObject("Scripting.Dictionary")
    WScript.Echo "exporting..."
    Dim myObj
    For Each myObj In oApplication.CurrentProject.AllForms
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acForm, myObj.fullname, sExportpath & "\" & myObj.fullname & ".form"
        oApplication.DoCmd.Close acForm, myObj.fullname
        dctDelete.Add "FO" & myObj.fullname, acForm
    Next
    For Each myObj In oApplication.CurrentProject.AllModules
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acModule, myObj.fullname, sExportpath & "\" & myObj.fullname & ".bas"
        dctDelete.Add "MO" & myObj.fullname, acModule
    Next
    For Each myObj In oApplication.CurrentProject.AllMacros
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acMacro, myObj.fullname, sExportpath & "\" & myObj.fullname & ".mac"
        dctDelete.Add "MA" & myObj.fullname, acMacro
    Next
    For Each myObj In oApplication.CurrentProject.AllReports
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acReport, myObj.fullname, sExportpath & "\" & myObj.fullname & ".report"
        dctDelete.Add "RE" & myObj.fullname, acReport
    Next

    WScript.Echo "deleting..."
    dim sObjectname
    For Each sObjectname In dctDelete
        WScript.Echo "  " & Mid(sObjectname, 3)
        oApplication.DoCmd.DeleteObject dctDelete(sObjectname), Mid(sObjectname, 3)
    Next

    oApplication.CloseCurrentDatabase
    oApplication.CompactRepair sStubADPFilename, sStubADPFilename & "_"
    oApplication.Quit

    fso.CopyFile sStubADPFilename & "_", sStubADPFilename
    fso.DeleteFile sStubADPFilename & "_"


End Function

Public Function getErr()
    Dim strError
    strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
               "From " & Err.source & ":" & vbCrLf & _
               "    Description: " & Err.Description & vbCrLf & _
               "    Code: " & Err.Number & vbCrLf
    getErr = strError
End Function

If you need a clickable Command, instead of using the command line, create a file named "decompose.cmd" with

cscript decompose.vbs youraccessapplication.adp

By default, all exported files go into a "Scripts" subfolder of your Access-application. The .adp/mdb file is also copied to this location (with a "stub" suffix) and stripped of all the exported modules, making it really small.

You MUST checkin this stub with the source-files, because most access settings and custom menu-bars cannot be exported any other way. Just be sure to commit changes to this file only, if you really changed some setting or menu.

Note: If you have any Autoexec-Makros defined in your Application, you may have to hold the Shift-key when you invoke the decompose to prevent it from executing and interfering with the export!

Of course, there is also the reverse script, to build the Application from the "Source"-Directory:

compose.vbs:

' Usage:
'  WScript compose.vbs <file> <path>

' Converts all modules, classes, forms and macros in a directory created by "decompose.vbs"
' and composes then into an Access Project file (.adp). This overwrites any existing Modules with the
' same names without warning!!!
' Requires Microsoft Access.

Option Explicit

const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3

Const acCmdCompileAndSaveAllModules = &H7E

' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

dim sADPFilename
If (WScript.Arguments.Count = 0) then
    MsgBox "Please enter the file name!", vbExclamation, "Error"
    Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))

Dim sPath
If (WScript.Arguments.Count = 1) then
    sPath = ""
else
    sPath = WScript.Arguments(1)
End If


importModulesTxt sADPFilename, sPath

If (Err <> 0) and (Err.Description <> NULL) Then
    MsgBox Err.Description, vbExclamation, "Error"
    Err.Clear
End If

Function importModulesTxt(sADPFilename, sImportpath)
    Dim myComponent
    Dim sModuleType
    Dim sTempname
    Dim sOutstring

    ' Build file and pathnames
    dim myType, myName, myPath, sStubADPFilename
    myType = fso.GetExtensionName(sADPFilename)
    myName = fso.GetBaseName(sADPFilename)
    myPath = fso.GetParentFolderName(sADPFilename)

    ' if no path was given as argument, use a relative directory
    If (sImportpath = "") then
        sImportpath = myPath & "\Source\"
    End If
    sStubADPFilename = sImportpath & myName & "_stub." & myType

    ' check for existing file and ask to overwrite with the stub
    if (fso.FileExists(sADPFilename)) Then
        WScript.StdOut.Write sADPFilename & " exists. Overwrite? (y/n) "
        dim sInput
        sInput = WScript.StdIn.Read(1)
        if (sInput <> "y") Then
            WScript.Quit
        end if

        fso.CopyFile sADPFilename, sADPFilename & ".bak"
    end if

    fso.CopyFile sStubADPFilename, sADPFilename

    ' launch MSAccess
    WScript.Echo "starting Access..."
    Dim oApplication
    Set oApplication = CreateObject("Access.Application")
    WScript.Echo "opening " & sADPFilename & " ..."
    If (Right(sStubADPFilename,4) = ".adp") Then
        oApplication.OpenAccessProject sADPFilename
    Else
        oApplication.OpenCurrentDatabase sADPFilename
    End If
    oApplication.Visible = false

    Dim folder
    Set folder = fso.GetFolder(sImportpath)

    ' load each file from the import path into the stub
    Dim myFile, objectname, objecttype
    for each myFile in folder.Files
        objecttype = fso.GetExtensionName(myFile.Name)
        objectname = fso.GetBaseName(myFile.Name)
        WScript.Echo "  " & objectname & " (" & objecttype & ")"

        if (objecttype = "form") then
            oApplication.LoadFromText acForm, objectname, myFile.Path
        elseif (objecttype = "bas") then
            oApplication.LoadFromText acModule, objectname, myFile.Path
        elseif (objecttype = "mac") then
            oApplication.LoadFromText acMacro, objectname, myFile.Path
        elseif (objecttype = "report") then
            oApplication.LoadFromText acReport, objectname, myFile.Path
        end if

    next

    oApplication.RunCommand acCmdCompileAndSaveAllModules
    oApplication.Quit
End Function

Public Function getErr()
    Dim strError
    strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
               "From " & Err.source & ":" & vbCrLf & _
               "    Description: " & Err.Description & vbCrLf & _
               "    Code: " & Err.Number & vbCrLf
    getErr = strError
End Function

Again, this goes with a companion "compose.cmd" containing:

cscript compose.vbs youraccessapplication.adp

It asks you to confirm overwriting your current application and first creates a backup, if you do. It then collects all source-files in the Source-Directory and re-inserts them into the stub.

Have Fun!