By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Shared access to Database

P: n/a
We recently upgrade form Office 97 to Office XP.

Under 97 we had about 15 small databases for various departments that shared
data.

In this example I'll use our Disconnect Database.

We have the DISCONNECT.MDB file on the network.
Each user would double click on the MDB file to open it (All 5 at the same
time)
They could edit using the form and launch the report to see all current
changes.

After the upgrade we are now limited to only 1 user in the database at a
time.

I have been glancing through the boards and I keep seeing this front
end/backend set up which is just far too much for the simple database
sharing we are currently doing.

What is the quick solution or is there a way to unlock the MDB file so it
behaves the same as the 97 version?

Thanks so much for all your help.

Doc
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

Donald Watson wrote:
We recently upgrade form Office 97 to Office XP.

Under 97 we had about 15 small databases for various departments that shared data.

In this example I'll use our Disconnect Database.

We have the DISCONNECT.MDB file on the network.
Each user would double click on the MDB file to open it (All 5 at the same time)
They could edit using the form and launch the report to see all current changes.

After the upgrade we are now limited to only 1 user in the database at a time.

I have been glancing through the boards and I keep seeing this front
end/backend set up which is just far too much for the simple database sharing we are currently doing.

What is the quick solution or is there a way to unlock the MDB file so it behaves the same as the 97 version?

Thanks so much for all your help.

Doc


Nov 13 '05 #2

P: n/a
Have you looked at TOOL | SECURITY to see if anything's amiss there?
As for frontend/backend, there are lot's of reasons to split your
database. One the strongest reasons is the environment you're
describing, which really isn't "simple database sharing." When you have
several people accessing a db at once across a network, you truly
should have the db split.

Splitting is not a difficult thing to do nor maintain.
Donald Watson wrote:
We recently upgrade form Office 97 to Office XP.

Under 97 we had about 15 small databases for various departments that shared data.

In this example I'll use our Disconnect Database.

We have the DISCONNECT.MDB file on the network.
Each user would double click on the MDB file to open it (All 5 at the same time)
They could edit using the form and launch the report to see all current changes.

After the upgrade we are now limited to only 1 user in the database at a time.

I have been glancing through the boards and I keep seeing this front
end/backend set up which is just far too much for the simple database sharing we are currently doing.

What is the quick solution or is there a way to unlock the MDB file so it behaves the same as the 97 version?

Thanks so much for all your help.

Doc


Nov 13 '05 #3

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm suprised you didn't have any data corruption when using your present
set up w/ Access 97.

When multiple users are accessing the same data you have to use the
front-end/back-end set up. Split the front-end (queries, forms,
reports, modules) from the back-end (tables) using the Tools > Database
Utilities > Database Splitter option. Place the resulting front-end on
each user's PC. For each user's front-end relink the tables (Tools >
Database Utilities > Link Table Manager) so each user's front-end links
to the common back-end.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlWD3YechKqOuFEgEQJLrACfZKY6kXu3qZAg+1Es1oemLK IQ4eMAn0vT
CALwKoQnXHuBbWeQx9toXI6w
=lcuU
-----END PGP SIGNATURE-----
Donald Watson wrote:
We recently upgrade form Office 97 to Office XP.

Under 97 we had about 15 small databases for various departments that shared
data.

In this example I'll use our Disconnect Database.

We have the DISCONNECT.MDB file on the network.
Each user would double click on the MDB file to open it (All 5 at the same
time)
They could edit using the form and launch the report to see all current
changes.

After the upgrade we are now limited to only 1 user in the database at a
time.

I have been glancing through the boards and I keep seeing this front
end/backend set up which is just far too much for the simple database
sharing we are currently doing.

What is the quick solution or is there a way to unlock the MDB file so it
behaves the same as the 97 version?

Nov 13 '05 #4

P: n/a
Donald, in general, you only need to link the tables once. And so,
MGFoster's method is perfect. However, sometimes you have need to
relink more often. In that case, use the following code and put the
relinking on a menu:

http://www.mvps.org/access/tables/tbl0009.htm

Don't forget the dependency:

http://www.mvps.org/access/api/api0001.htm

Just put them both into modules (I like putting them into separate
modules), create a button that calls the relinking routine
(fRefreshLinks) and fire away. It seemlessly relinks, even giving you
the ability to specify a different directory if you need.

Nov 13 '05 #5

P: n/a
One question. Using the Front End/ Back end version. Wont my users need to
export modified reports or forms back and forth to all 5 copies of the front
end for them to be accessible by everyone?

We have 5 users on this particular database and any of them may be making
changes or adding queries, reports or forms to the database.

Thanks

<jc*************@yahoo.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
Donald, in general, you only need to link the tables once. And so,
MGFoster's method is perfect. However, sometimes you have need to
relink more often. In that case, use the following code and put the
relinking on a menu:

http://www.mvps.org/access/tables/tbl0009.htm

Don't forget the dependency:

http://www.mvps.org/access/api/api0001.htm

Just put them both into modules (I like putting them into separate
modules), create a button that calls the relinking routine
(fRefreshLinks) and fire away. It seemlessly relinks, even giving you
the ability to specify a different directory if you need.

Nov 13 '05 #6

P: n/a
It sounds like you have a development environment. So, yes, if you have
multiple people making changes to the frontend, you'll need to come up
with a distribution strategy. Most developers in such an environment
use a "check out" system whereby the code is literally checked out then
checked back in when finished, making it available for check out by
someone else.

I'm not aware of any MS Access-related check out software (doesn't mean
it doesn't exist) but perhaps others in the ng know of something.

Nov 13 '05 #7

P: n/a
"Donald Watson" <RE**********************@linncountyrec.com> wrote
in news:2q**************@news.uswest.net:
One question. Using the Front End/ Back end version. Wont my
users need to export modified reports or forms back and forth to
all 5 copies of the front end for them to be accessible by
everyone?

We have 5 users on this particular database and any of them may be
making changes or adding queries, reports or forms to the
database.


Well, wake up and smell the coffee: with Access 2000, Microsoft
changed the way code is stored in an Access MDB, and two users
*can't* edit the code/objects at the same time. There is no getting
around that under any circumstances.

MS's reason for this was that it prevents corruption, but it's a
kind of corruption I never ever saw in any of my clients' MDBs.

Also, because it converts your Access project into a single record
in a Jet data table (that's why only one user can edit it at a
time), you can't save incremental changes -- any SAVE of anything
has to save the whole record. This is not nearly as slow as it was
in the initial release of A2K, but it is still slower than A97 (and
annoying, because of the multi-save dialog, which drives me crazy).

Your only answer for this is to have front ends for each user and
some method for saving their new reports to a reference copy of the
front end, then a method for pushing those changes out to the end
users.

I suspect, though, that most of these saved reports are highly
redundant, for instance, being the same report layout, but using a
filtered recordsource. This can be handled by a little programming
to allow an end user to choose their recordsource, either by
filtering or picking from a list of queries.

Your current setup is now completely unworkable.

It was previously working only by pure luck.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.