selection in multiuser environment

[Jing]

Hi, Tony,

I got your information from dbForums, I believe you can
solve my question:

I am new about Access. We have a JDBC-ODBC bridge to
connect Java code with Access 2000.

2 users will access Access DB. When user A updates records
in a table, will user B select the correct records?
Correct records, is records that completed.

I don't know how select in Access works? Are there techs
in Access to save any changes to file before allowing a
select? Or Will this depends on which Locks strategies
that Access is set to?

Thank you very much,
Jing

 

[Larry Linson]

If you are using JDBC-ODBC for an "Access" database, you are actually using
a Jet database. The Jet database engine is distributed and installed by
default, with Access -- for this reason, it is often called an "Access
database". But, in fact, Access is the user interface and development tool,
and can be used to front-end other database engines.

So, Access itself will not be involved in your configuration. Access cannot
access a Jet database with ODBC, so few of the Access-experienced people
here will have answers for your questions. I made a quick search of the
microsoft.public newsgroups but didn't find one to recommend. Sorry.

Perhaps in USENET there will be some JDBC newsgroups where you will have a
better chance of finding the answers you need.

In general, Jet does have locking mechanisms all of which lock an "edited"
record, but how you specify those via JDBC/ODBC, I couldn't say. I would be
confident that a second user would not get a partially-updated record,
however.

Larry Linson
Microsoft Access MVP

 

[Jing]

Thank you for your reply, Larry.

I would be
confident that a second user would not get a partially-
updated record, however.

I totally agree, but I don't know why. Here's what I am
thinking: when one user updates some records, the
modification will be saved to data files. When another
user selects from this table, he will get either saved
(new) records or unsaved(old) records, but none will be
uncompleted. Chances of getting unsaved records are very
very small, because Access Refresh time is set to seconds.

Am I right? Thank you very much,
Jing

 

[Brendan Reynolds \(MVP\)]

No. That wouldn't work, because code or an action query could easily update
many records per second. The JET database engine locks a record while it is
updating it. Another user can't retrieve a partially updated record because
the lock is not released until the update is complete.

There are options that influence how and when locks are placed and released,
e.g. lock an entire table, a page of records, or a single record, lock as
soon as a user begins to edit a record in the UI or only when the edits are
submitted to the database, but at a minimum, with any combination of
settings, the record is always locked during the actual update.