DataBase - Ensuring data integrity using linked tables

Asked By j_gold

11-Sep-09 02:18 PM

Hi, I have been slowly learning Access 2007 and trying to determine how to
handle data integrity when updating records.

I have an Access database (accdb format) in which I have linked tables to a
MySQL database using DAO. (we are experimenting with MS SQL Server too, and
will go with whichever one provides the best/easiest solution). Data on the
server can be updated via the Access database, as well as the web and batch
updates. There will be multiple users using the Access database; the IT dept
has said specifically not to split the Access database. Each user will have a
local copy of the Access database.

Am I correct in understanding that in order to determine if the recordset
has been changed by another user, I would have to split the database and
share the linked tables? If so, is there another way to do this without
splitting the db as my IT department has specifially requested that Access
database not be split (concern over locking issues and the network drive
going down).

Also, how does Access handle changes that are made outside of Access i.e.
via the web updates and batch updates?

Any insights/suggestions are greatly appreciated.

Please let me know if a more detailed explaination is required.

J Gold

11-Sep-09 06:00 PM

Your IT department is misguided. If each user has his/her own local copy of
the database, they willl be unable to see changes made by others.

For shared databases, not splitting the database is a pretty good way of
ensuring database corruption. It also makes upgrading the functionality of
the database significantly more difficult.

it is not clear to me the sort of data integrity for which you are looking. If
you are talking about referential integrity between the tables that are in
your Access database and the tables that are in MySQL (or SQL Server), it is
not really possible. To ensure referential integrity in Access, you create
relationships between the tables. You cannot create relationships between
non-homogenious tables. If you are strictly talking about RI in the tables in
your Access database, if you have relationships set up correctly, then they
will apply to updates made from outside of the Access application. (In other
words, if you try to make an invalid update, it will not be accepted)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

12-Sep-09 01:04 AM

I am not entirely clear on your configuration. If you ONLY have linked
tables, splitting the db is not an issue since you do not have Access
tables. If you have Access tables and MySQL tables, then the app
should be split. Let IT point to an authorative article why this is a
bad idea, and we will have the tar & feathers ready :-)

On your last question: it depends. Say you have a form linked to a SQL
Server table over ODBC. In the footer it says you are looking at record
1 of 5000. You batch-add 1000 records. Will it somehow automatically
show "1 of 6000" without closing and reopening the form, or Shift+F9
to requery it? There is an ODBC refresh interval, so if the stars are
aligned properly indeed this will be the case. Or you requery the form
in a timer that ticks every so often (e.g. 5 minutes). More elaborate
schemes are possible; it depends on how important those new records
are.

-Tom.
Microsoft Access MVP

14-Sep-09 08:28 AM

Thanks Douglas for your reply. To clarify, I have no Access tables, I am only
using Access as a means to provide a gui to the linked tables on the MySQL
server.

I read Tom's response, which said that because I am only linking my tables,
splitting the db is not an issue.

Regards,

Angela

14-Sep-09 08:50 AM

Hi Tom, Thanks for your reply and the offer to tar and feather - though that
will not be necessary - at least not for now ;-)

I know about the ODBC refresh interval and have it set to 60 secs. I am not
sure if that is sufficient, but will start there.

What I need to do is figure out how to determine what data has changed and
give the user the opportunity to override any changes that may have occured
since the information was pulled. I thought this would be easy, but it is
stumpping me. I have not dealt with linked tables before.

Thanks,

J Gold

14-Sep-09 09:28 AM

In my experience, the primary cause for "Data has changed" errors in
Access is not because someone actually changed but because the code
behind the form was working against the Access's managing data behind
the scene. An easy way to do this is to start editing a record, then in
a event of a control, fire a separate query that then updates the same
record. As soon you move off the record, you get that error even if you
were the only user. This is because from the backend's POV (regardless
whether it is MySQL, MS-SQL, Oracle, DB/2, PostgreSQL, whatever) the data
bound to form and data modified by the query is two separate connection
and thus appears to be as if there were two users accessing same record
at time.

Thus, this can be avoided by being judicious with how we manipulate
data. A good rule of thumb would be to consistenly manipulate data
through the form only using its controls or recordset rather than using
queries except where you may want to do a bulk operations and only when
there are no dirty records on the form.


Just to be complete because of your thread title, if you are using
anything but InnoDB tables, then you do not have referential integrity,
so you need to take responsibility for checking the data integrity when
working with data via Access. (In fact, if it is important that you
protect the data's integrity, then I would not use anything but InnoDB
tables.)


Generally speaking, Access will automatically detect if it has changed.
You may be also able to influence the behavior by configuring the
backend accordingly (e.g. locking defaults and isolation level comes to
mind, but that is usually overkill in most cases). Are you experiencing
specific cases where this is not happening?

Or maybe you want to know whether the data has been updated since a
certain point, and not necessarily since we opened the Access form? If
that is the case, then we would need to do it manually, perhaps using
timestamps to track the last time it was updated.

Like Tom said, it depends on specific case because the devil is in details.

I hope this helps somehow.

14-Sep-09 10:56 AM

Thanks Banana,

Yes, I am using InnoDB tables.  In my various readings, it was suggested
that in order to have more control over the data, you should use unbound
forms. I set up my forms that way, and I found it easier that way to populate
the forms as a few of them required data from multiple tables and using
Access to do union queries and multiple table joins was proving to be too
much of a headache.

Again, if I understand what I am reading, Access's ability to manage the
data relies on bound controls. Is this correct?

FYI - I am pulling a single record from the data base at a time and so am
not moving through the recordset. I am controlling when a record is
added/updated using command buttons. I am also using transactions to roll
back the update if unsuccessful.


Regards,

J Gold

14-Sep-09 11:12 AM

FWIW, I do not totally agree that unbound forms should be used. Having
developed a project with MySQL backend using "wide" datasets (e.g. a
single entry is actually several records in several many-side table)
using bound forms, I am inclined to think that bound forms can work well
in many circumstances, far more often than unbound forms.

Generally speaking, each form is best bound against one table, but
subforms makes it possible to manage master-detail, and as mentioned
before, I had five to eight tables bound to subforms to enable data
entry for what was a complex entity.

UNION queries usually should not be necessary for binding a form, though.
Mind giving an example of where you use UNION query? it is possible we
can show a solution that does not need to use UNION or a workaround.


That is correct. I may have missed that you were using unbound
forms/controls which makes all the difference. With unbound form, you
are completely responsible for is going on. You can use the Refresh
method to check whether the data has changed since you last
accessed/refreshed a record. Refresh is usually faster than Requery,
though it will not tell you whether a record was newly added or deleted
(though you would find out when you get to a row with "#Deleted")

Transaction would also prevent writing to the data so there would be no
problem anyhow.

HTH.

14-Sep-09 07:41 PM

Someone in your IT department is rather incoherent in their
understanding of terminology, or you, yourself, have misunderstood
what they said.

By definition, an Access front end to a MySQL or SQL Server database
is ALREADY SPLIT, because the program is in an Access file and the
data is in a server database. Splitting only has meaning in
comparison to a single MDB/ACCDB file with tables and
forms/reports/etc. in the single file.

Saying "each user will have a local copy of the Access database" is
the correct way to do it, whether your data is stored in a Jet/ACE
file or in MySQL or SQL Server.

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

14-Sep-09 07:43 PM

Tom van Stiphout <tom7744.no.spam@cox.net> wrote in


The refresh interval *never* changes the number of records. It only
refreshes the data in the records in the original dataset that was
retrieved. If you want to see new records, you have to requery the
form.

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

14-Sep-09 07:45 PM

Having read more in the thread, I question what you mean here.

Linked tables to a MySQL back end require no DAO at all.

Do you mean that you used DAO to link the tables?

Or that you use DAO to retrieve and update data?

If the latter then STOP DOING IT. Use the linked tables directly
bound to your forms. Use optimistic locking. You will find your life
to be substantially easier than trying to do it all unbound.

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

14-Sep-09 07:47 PM

One of the real drawbacks of the way that MySQL has been depending
on InnoDB tables for features that make it more than a toy database
is that only MyISAM tables allow full-text indexing. If you want
both RI and full-text indexing, you are completely out of luck.

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

14-Sep-09 07:49 PM

I that case, there is virtually no advantage whatsoever in using an
unbound form.

Stop fighting against the design of Access -- use forms bound to
linked tables.

And if you find yourself in need of UNION queries for data editing,
then you probably have a schema design error.

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

14-Sep-09 08:21 PM

I believe that with any kind of linked tables, Jet/ACE & DAO does
nothing in regards to locking and always assume optimistic locking even
if set otherwise. The only way to manage locking client side would be to
use ADO or configure the server to behave a certain way.

Otherwise, I do agree that one should be careful to not run codes that
may conflict with what Access is doing behind the stages for the bound
form and thus cause locking conflicts. To that end, I find myself
manipulating form's controls or recordset directly, running queries only
for batch process where there will be no edited records. That usually
takes care of those silly locking/write conflicts.

18-Sep-09 01:52 PM

You can find someone who is written almost any kind of poor advice.  If you
are following advice to use unbound forms, that is exactly what you got in
the "suggestions in your various readings" -- poor advice.  Others have
explained why, but few of us are likely to be very interested in helping you
find workarounds for something that is implemented in what is basically
erroneous fashion.  We are much more comfortable telling you how to go back
and do it right, so you do not create even more problems for yourself.

Larry Linson
Microsoft Office Access MVP

24-Sep-09 03:15 AM

Er, what? Pessimistic locking works. There are problems with trying
to use record-level locking with DAO, but I have never actually cared!
I have completely ignored record-level locking in all my Jet 4 apps,
and have not had any issues whatsoever.


I never muck with a form's recordset. I write direct to the edit
buffer, either via the bound controls in the form's Controls
collection or through the form's Fields collection. This is the way
Access has always worked, it is how all the tutorials and help files
and example code suggest you work. And what I mean by that is:

Me!txtMyTextField = "this is the new text"

Or:

Me!MyTextField = "this is the new text"

I see no utility whatsover in mucking around with the form's
recordset when I have already got access to the current record's edit
buffer.

And the idea of binding a form to a recordsource so it has a
recordset and then not binding the controls to the fields in that
recordsource is just perverse. You get all the problems of unbound
editing without any of the benefits of actually editing unbound.

Perhaps that is not what was being suggested, but it is the best I
could get out of the discussion.

Bind your form to a recordsource of 1 or a few records and leave all
the saving of the data in the controls to Access. The locking issues
are minuscule in that scenario because the main point is not unbound
controls, but retrieving the smallest useful data set so you impose
the fewest number of locks to get the job done.

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/