Database split & referential integrity

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?

"'69 Camaro" wrote:

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.


"Dermot" wrote:

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" wrote:

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.


"Angela" wrote:

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

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" AM wrote in
message ...
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.


"tina" wrote in message
...
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


"'69 Camaro" AM wrote
in
message ...
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/de...vellocking.asp

HTH.
Gunny

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


"TedMi" wrote in message
...
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?
--
Ted

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.


"tina" wrote in message
...
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" AM wrote
in
message ...
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.


"tina" wrote in message
...
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


"'69 Camaro" AM
wrote
in
message ...
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/de...vellocking.asp

HTH.
Gunny

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


"TedMi" wrote in message
...
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?
--
Ted

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.


"Dermot" wrote in message
...
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?

"'69 Camaro" wrote:

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.


"Dermot" wrote:

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" wrote:

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.


"Angela" wrote:

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

okay, and thanks!


"'69 Camaro" AM wrote in
message ...
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.


"tina" wrote in message
...
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" AM wrote
in
message ...
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.


"tina" wrote in message
...
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


"'69 Camaro" AM
wrote
in
message ...
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/de...vellocking.asp

HTH.
Gunny

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


"TedMi" wrote in message
...
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?
--
Ted

You're welcome!

Gunny

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


"tina" wrote in message
...
okay, and thanks!


"'69 Camaro" AM wrote
in
message ...
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.


"tina" wrote in message
...
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" AM
wrote
in
message ...
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.


"tina" wrote in message
...
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


"'69 Camaro" AM
wrote
in
message ...
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/de...vellocking.asp

HTH.
Gunny

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


"TedMi" wrote in message
...
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?
--
Ted