Hi, David.
We currently employ a system that checks a table
entry to see if the front end is allowed to connect (sort of like a Locked =
True column) but in order for our customers to meet Sarbanes-Oxley compliance
that will not suffice.
That explains why you want to guarantee exclusive access to the data.
There's no guarantee that the separate table entry is correct, since there's
no transaction log to record whether the transaction occurred or not.
What we're trying to prevent is one user linking to the table and having
another change the data at the same time, skewing both of their results
(accidental or not.)
Due to record-locking schemes (called "optimistic locking") of modern
relational databases, if one user opens a record at the same time as another
user, but the second user changes the record (updates it) before the first
user attempts to change the record, then the first user's subsequent change
to the original record's values will be tossed out and the user will receive
an error message ("The record has changed..."). The database application
must then retrieve the record from the database again (with the freshest
data) for the first user so that the user can attempt to make another change
to the record. This may or may not be the change that the first user
originally intended, but at least the user gets a chance to make that choice
based upon the current values in the record.
Client/server databases such as Oracle and SQL Server use this "optimistic
locking" technique to prevent the results from being skewed accidentally
(i.e., overwriting already updated data). Access uses this technique, too.
However, client/server databases such as Oracle and SQL Server can
_guarantee_ that the results aren't skewed accidentally, even if there's a
power failure to interrupt the completion of the transactions. Access
_can't_ make this guarantee, because Access doesn't have the same type of
recovery processes to guarantee that the requested transactions were either
made or rolled back in the event of a power failure or disconnection from
the database.
Since I'm an Oracle DBA, you may consider this to be a biased opinion, but I
don't believe that Microsoft Access meets the requirements for compliance
with SOX, precisely because of this lack of guarantee that the "records are
absolutely correct" for all transactions ever made on the database.
However, I recommend that you contact a professional who is well-versed in
the Sarbanes-Oxley database requirements for a final answer on whether
Access is adequate to meet these mandated requirements.
It's not that I haven't had to deal with SOX compliance. Quickly after the
Sarbanes-Oxley law was passed, the question came up in a corporate meeting
that I attended, "How do we find out whether all of these Access databases
will comply with SOX?" The answer from our DBA's was, "Any records that
need to meet SOX-compliance will be migrated to Oracle, whether they are
currently in Excel spreadsheets or Access databases." There was never any
discussion whether or not we might not need to embark on this huge, very
expensive, undertaking (the corporation I worked for had 190,000+ employees
and had been in business for nearly a century, so there were probably many
dozens -- or possibly hundreds, if one counts the copies on employee
workstation hard drives -- of spreadsheets and databases per employee to
consider). We DBA's knew we could guarantee compliance by using Oracle, but
that Access databases might be challenged by legal experts, just for the
reason I stated earlier.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
