Database split & referential integrity

[Angela]

Database splitting question:
I have a db that contains data tables (fixed information), dynamic tables
(info that changes based on users input), queries, forms, reports etc. I've
split the db into a front end & back end, leaving the "dynamic" tables in the
front end since multiple users would otherwise overwrite each other's tables.
After I split the db, I recreated the relationships between the dynamic and
data tables. However, I can no longer enforce referential integrity between
them, probably b/c the data tables are linked (the options for ref integrity
were grayed out). Once a db is split, is it impossible to enforce
referential integrity?
Thanks
Angela

 

['69 Camaro]

Hi, Angela.

Once a db is split, is it impossible to enforce
referential integrity?

Yes. No relational database engine can enforce referential integrity
between tables in an external file (for file-based databases, such as Access)
or an external instance of the database (for client/server databases).
There's no way the database engine can control what goes on in an external
file or external instance, which may be manipulated by other, external
processes when the current database is not open. Therefore, the Jet database
engine can only enforce referential on tables within a single database
container, i.e., a single database file.

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.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Dermot]

Hi,
This topic interested me. The "Access Tips" link is useful to know.
I have one other question relating to this topic.
Because reerential Integrity can't be achieved with the way this databse has
been designed with a front / back end........what way should have been used
to achieve the desired relationships?
Thanks
Dermot

 

['69 Camaro]

Hi, Dermot.

what way should have been used
to achieve the desired relationships?

Referential integrity can be enforced only when all of the related tables
are in the same database file, i.e., the back end. Obviously, this isn't
always feasible. Records in related tables located in multiple files can be
programmatically manipulated to simulate the database engine's relational
integrity constraints, but this isn't 100% reliable and risks data integrity
whenever "something goes wrong" -- as we all know occasionally happens.

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.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[TedMi]

:
.....

what way should have been used to achieve the desired relationships?

I recommend leaving the dynamic tables in the back end, and identifying each
user's data by adding a UserID column to each dynamic table. That way, each
user's data can be kept distinct, yet all data resides in one db, so
relational integrity can be enforced. You can set up user ID's either at the
Access DB level, or use a system API to get the user's Windows logon ID.

-Ted

 

['69 Camaro]

Hi, Ted.

This approach works well in a multiuser environment as long as the users are
either not concurrent or there's no updating and deletions of these records.
Concurrent users who are using dynamic tables to update and delete records
will have record-locking issues and performance issues. And even if the
dynamic tables don't have records updated and deleted, one runs the risk
that the size of these tables will eventually meet the maximum allowed when
records are only added by the many users.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[TedMi]

Hi Camaro.
I'm more familiar with this scenario where the back end is on SQL Server,
which does not have the concurrency issues you describe. Doesn't Access JET
implement row-level locking?

 

['69 Camaro]

Hi, Ted.

I'm more familiar with this scenario where the back end is on SQL Server,
which does not have the concurrency issues you describe.

Client/server databases can implement independent locking schemes. Jet
can't. If the first user connects to the Access database file using
page-level locking, then all subsequent users' connections must use
page-level locking, too. Page-level locking has the potential of
interfering with other user's records in the same table, as well as slowing
down performance.

Doesn't Access JET
implement row-level locking?

Jet 4.0 (Access 2000 and newer) can implement row-level locking under
certain conditions. Please see the following Web pages for descriptions of
the conditions when this doesn't happen, even when the database settings
assign record-level locking:

http://support.microsoft.com/default.aspx?id=306435

http://support.microsoft.com/default.aspx?id=238258

http://msdn.microsoft.com/library/d...deovrpagelevellockingvsrecordlevellocking.asp

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[tina]

hey there, Gunny! thanks for the links, great reading.

i had an unusual situation the other day. i built a quick-and-dirty db last
week, and housed it on the local server. i had the db open (*not*
exclusively), and a co-worker was unable to open it at all - getting a "db
already in use" message. she was not attempting to open exclusively, either.
we're both using A2003 on WinXP Pro, db is in A2000 format. any thoughts?

thx,
tina

Hi, Ted.

I'm more familiar with this scenario where the back end is on SQL Server,
which does not have the concurrency issues you describe.

Client/server databases can implement independent locking schemes. Jet
can't. If the first user connects to the Access database file using
page-level locking, then all subsequent users' connections must use
page-level locking, too. Page-level locking has the potential of
interfering with other user's records in the same table, as well as slowing
down performance.

Doesn't Access JET
implement row-level locking?

Jet 4.0 (Access 2000 and newer) can implement row-level locking under
certain conditions. Please see the following Web pages for descriptions of
the conditions when this doesn't happen, even when the database settings
assign record-level locking:

http://support.microsoft.com/default.aspx?id=306435

http://support.microsoft.com/default.aspx?id=238258

http://msdn.microsoft.com/library/d...deovrpagelevellockingvsrecordlevellocking.asp

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

['69 Camaro]

Hi, Tina.

It depends upon the exact error message. Was it:

"The file is already in use"?

The first user to open the database doesn't have Windows security "create"
permissions in the directory where the database is located. Give Windows
security "Full Control" permissions to all users.

.. . . or "Could not lock file"?

The second user doesn't have Windows security "modify" permissions to the
files in the directory where the database is located. Give Windows security
"Full Control" permissions to all users.

.. . . or "The database has been placed in a state by user 'Admin' on machine
'machinename' that prevents it from being opened or locked"?

When this happens, the user on computer 'machinename' currently has the
database application open in exclusive mode, even if it was originally
opened in shared mode. Most likely, the user on computer 'machinename' is
editing a form, report, or module in the database on the server. Until this
user either exits the database or completes the modification of the database
that has temporarily elevated it to exclusive mode (if this user opened it
in shared mode), all other users will be locked out.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

hey there, Gunny! thanks for the links, great reading.

i had an unusual situation the other day. i built a quick-and-dirty db
last
week, and housed it on the local server. i had the db open (*not*
exclusively), and a co-worker was unable to open it at all - getting a "db
already in use" message. she was not attempting to open exclusively,
either.
we're both using A2003 on WinXP Pro, db is in A2000 format. any thoughts?

thx,
tina


in
message news:[email protected]...

 

[Dermot]

Hi Gunny,
Thanks for the reply.
Quote....... Referential integrity can be enforced only when all of the
related tables

are in the same database file, i.e., the back end. Obviously, this isn't
always feasible.

Question.........When it's not feasible what is the alternative?

 

[tina]

thanks, Gunny, i'll test it out on Monday. one additional question:

Most likely, the user on computer 'machinename' is
editing a form, report, or module in the database on the server.

does the same issue arise if the first user is creating or editing a *query*
object in the server database?

thx,
tina

 

['69 Camaro]

Hi, Tina.

does the same issue arise if the first user is creating or editing a
*query*
object in the server database?

No. It only happens when the user is creating or modifying an object that
will be saved in the monolithic record for Jet 4.0 databases. Queries are
saved in the MSysQueries system table, so it's safe for users in a multiuser
environment to create and edit queries without interference.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

['69 Camaro]

Hi, Dermot.

When it's not feasible what is the alternative?

Use VBA code to cascade updates and deletes to related records or to check
whether a parent record exists and, if not, to create it as soon as the
child record is created. There's no guarantee that this VBA code will run
successfully, so data integrity is at risk. Always use the database engine
for referential integrity whenever possible.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[tina]

okay, and thanks!

Hi, Tina.


No. It only happens when the user is creating or modifying an object that
will be saved in the monolithic record for Jet 4.0 databases. Queries are
saved in the MSysQueries system table, so it's safe for users in a multiuser
environment to create and edit queries without interference.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

['69 Camaro]

You're welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.