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