How ODBC Drivers handle Data Isolation and Concurrency for Microsoft Access

              It has been a very long time since my last post. Much of my time has been spent with family and the rest with Hadoop and Hive, oh, and of course the daily grind that comes with a full time job. Anyway, I know what you're asking yourselves; "Clay, why are you doing a post on something so trivial and basic as Linked Tables in Microsoft Access?" Good question. Believe me when I say I would much rather create a new post that discusses what I've learned about Hadoop and Hive in particular; however, this was necessary. I had a recent interaction in which the other party could not grasp the concept of Data Isolation and Concurrency in a Client/Server style Microsoft Access application.

                We all know that in a RDBMS, the database itself follows a set of built-in rules called the ACID properties which, among other things, maintains data consistency and isolation. When an outside "client" application is developed to act as an extension of the data stored in the RDBMS, its design should include a method of maintaining these ACID properties.

                 The correct way of building client/server Microsoft Access applications where the data is stored in a RDBMS is to use the Linked Table method. This ensures Data Isolation and Concurrency is maintained between the Microsoft Access client application and the RDBMS data with no additional and unnecessary programming logic and code which makes maintenance more difficult, and adds to development time.  

    

                First, an ODBC (Open Database Connectivity) API driver is used to create a DSN (Data Source Name) to the RDBMS.  Then the "backend tables" are simply linked from inside the Microsoft Access client with the wizard provided in Access.  ODBC is a translation layer between the application and the DBMS. ODBC is designed to provide access primarily to relational data by using the SQL data language in a multiplatform environment.   

             

                In these two examples, I've created a test database in SQL Server. After creating Linked Tables via an ODBC, I created a form with the form wizard for the dbo.Customer table. Now, for this first example, we have two users. User A, and User B. This will show how Microsoft Access handles the case where both users are looking at the same data in an Access Form and one user makes an update before the other user attempts to update the same data.

                USER A                                                                 USER B 

Here we see User A and User B both are viewing CUST_ID 5. 

                USER A                                                                 USER B 

Now User A makes an update to CUST_ID 5's phone number and saves the change.

                 USER A                                                                 USER B 

Now User B attempts to change the same data but gets a message that the data has been changed and the current data is refreshed for User B.

             In this next example, one user will be in the process of updating data; however, before the first user is able to save the update, another user saves changes to the same data.

                 USER A                                                                 USER B 

User B has updated the phone number but has not clicked the save button, while at the same time, User A updates the phone number and clicks the save button.

                 USER A                                                                 USER B 

When User B now tries to save the data, he gets the following message:
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

       This is all built into Microsoft SQL Server, Microsoft Access, and the ODBC driver.  It is simple to implement and does not require excessive and unnecessary logic and code that would make maintenance difficult.

More information on ACID can be found in the MSDN library at http://msdn.microsoft.com/en-us/library/aa480356.aspx

More information on Concurrency Control can be found at http://databasemanagement.wikia.com/wiki/Concurrency_Control