Multi-User Question

[dbain30]

My office is still using Access 97. We have several databases that
use ODBC connections to interact with tables generated each evening
from our mainframe system. Access to the mainframe tables is limited,
as a result we've developed a daily download process that moves much
of the data from those tables (that are linked) to tables within
Access so that anyone with access to the file can obtain the necessary
information.

The issue is that we have two locations and approximately 30
associates who can be in the database at any given time. We have
tried to setup a time each morning that everyone would close out of
the file so that the daily update can occur, however, that's not
always successful. My question is whether there's any way that we can
setup a system, even if it included multiple database files, that link
the information so that the files can be updated even if people have
the access file open.

Any help with this would be greatly appreciated!

 

[Albert D. Kallal]

not knowing how the import/update works, if you don't drop the tables, but
only clear them out, then you likely be able to do the imports in code even
when other users are in the data file...

I suspect you might be able to get the import process working while others
are in the database, but you have other issues.

If you daily deleting lots of records, then you need to daily compact and
repair that data file..and means everyone has to be out of the file
anyway...

The only practical workarounds here is to ensure people are out at the end
of the day, but you have to do that for maintains, modifying, and compact
repairing the file.

You don't have a lot of options here. Also, if you are running a split
database, you have somewhat more flexibility here, but a open database tends
to tie your hands here.

Might need a few more details on how the daily import is done, but it likely
can be re-written to function while users are in the database.

I suppose an ideal would be simply *always* link to the odbc system, and
always have live up to date data...

 

[John W. Vinson]

My office is still using Access 97. We have several databases that
use ODBC connections to interact with tables generated each evening
from our mainframe system. Access to the mainframe tables is limited,
as a result we've developed a daily download process that moves much
of the data from those tables (that are linked) to tables within
Access so that anyone with access to the file can obtain the necessary
information.

The issue is that we have two locations and approximately 30
associates who can be in the database at any given time. We have
tried to setup a time each morning that everyone would close out of
the file so that the daily update can occur, however, that's not
always successful. My question is whether there's any way that we can
setup a system, even if it included multiple database files, that link
the information so that the files can be updated even if people have
the access file open.

Any help with this would be greatly appreciated!

A local instance of SQL/Server might be the ticket here; you could still use
ODBC connections, but they should be much faster over the local network, and
SQL can permit "hot" updates.

 

[genesun]

Thank you for your replying to the original poster.

My question is simpler: If my office is populated with Access and about 7-8
workers use it. How does Microsoft Access solve the problem of simultaneous
access? More specifically, two or three people access the same form (or
table), will there be any conflict? Or the conflict will be only if they are
accessing the same field?

I am seriously considering to discard other market software and adopt
Access. But before making the decision, I need to be sure about this point.

And also am looking for an expert for regular consultation in first month.

Regards,

genesun

 

[Paul Shapiro]

You get to choose how Access handles potential or actual multiuser
conflicts.

A form can be set to lock ALL records when it's opened, so no conflicts
should occur. But concurrency is drastically reduced since only one person
at a time could use that form.

A form can be set to lock a record when the user begins editing. The user
will be prevented from editing if any other user is editing that same
record, but each user can be actively editing different records.

A form can be set to Optimistic Concurrency, where no records are locked.
When the user tries to save their changes to the db, Access checks to see if
any attribute values have changed since the user read the record from the
db. If no other changes occurred, the record is saved. If someone else
changed the data while this user was working on the record, Access prompts
to see if the user wants to a) discard their changes and see the current
data b) screw the last person and save the changes anyway or c) save your
changes to the clipboard and show you the current data. The idea behind c)
is you can look at the data changes and then choose to paste your changes
over the top, but I've rarely found this works.

Still, you have quite a bit of control, and Access will protect multiple
users automatically in any of these 3 scenarios.

 

[John W. Vinson]

Thank you for your replying to the original poster.

My question is simpler: If my office is populated with Access and about 7-8
workers use it. How does Microsoft Access solve the problem of simultaneous
access? More specifically, two or three people access the same form (or
table), will there be any conflict? Or the conflict will be only if they are
accessing the same field?

Users can access the same form (which might be bound to several tables) or the
same table simultaneously.

There will be a conflict only if two users attempt to update the same RECORD
in the same table at the same time.

There are several options for how to handle this type of collision;
"pessimistic locking" is probably most used, as it will lock only that one
record for other users when a first user has opened it for editing.

I am seriously considering to discard other market software and adopt
Access. But before making the decision, I need to be sure about this point.

And also am looking for an expert for regular consultation in first month.

This isn't really a jobs board, though some of the volunteers here are
selfemployed consultants.