Running Networked database

  • Thread starter Richard Harison
  • Start date
R

Richard Harison

Hello!
I have written a comprehensive database for a non-profit agency, and they
require multiple users to access it. I'm not very conversant with networks,
Access permissions etc. They want one person entering data on a consistent
basis, but another to access it for info occasionally. How would I go about
this, especially in view of the possibility of data collision?
Thanks!!
 
M

Marshall Barton

Richard said:
I have written a comprehensive database for a non-profit agency, and they
require multiple users to access it. I'm not very conversant with networks,
Access permissions etc. They want one person entering data on a consistent
basis, but another to access it for info occasionally. How would I go about
this, especially in view of the possibility of data collision?
Click to expand...


Access automatically notifies users when there are two of
them changing the same record. See Help for Optimistic and
Pessimistic locking.

The big trick is to split your database application into a
back end mdb file that contains all the tables and a front
end that contains everything except tables. You might see a
few differences in your VBA code, but, in general, the
wizard takes care of the splitting operation.

Once the appl is splt, put the back end on the server and a
copy of the front end on each user's machine. Use the
Linked Table Manager (Tools - Database Utilities) to link
the front end to the back end.
 
R

Richard Harison

HOORAY!
Thanks Marsh!! For a different reason, I had already split into
frontend/backend! (This was so new modifications to frontend would only require
the end user to re-link his tables)
The users theoretically would NOT be changing the same record. One would only
be accessing for informational purposes. Your tip on opt/pess record locking
was also greatly appreciated! So I set it for lock "edited record"
Many thanks again!!
 
M

Marshall Barton

Richard said:
HOORAY!
Thanks Marsh!! For a different reason, I had already split into
frontend/backend! (This was so new modifications to frontend would only require
the end user to re-link his tables)
The users theoretically would NOT be changing the same record. One would only
be accessing for informational purposes. Your tip on opt/pess record locking
was also greatly appreciated! So I set it for lock "edited record"
Click to expand...


Actually, if the odds of two people changing the same record
at the same time are small, then No Locks is probably the
one you should use.

If you feel ambitious (of if getting your users to relink
correctly is a problem), you might want to use code to do
the relinking. There are several of these procedures out
there on the internet. Here's one that's probably more
elaborate than you need, but you can strip it down if the
back end is always in the same place.
 
R

Richard Harison

Hi Marsh,
One last question. When I split the database, I did not use the wizard. (Quite
frankly didn't know it existed)
I simply copied the database and then deleted all the tables in one copy, and
deleted everything BUT the tables in the other copy.
Everything seems to be OK, yet you mentioned there might be some modification to
my VBA code. Got me to biting a few fingernails. I did make sure that the
table relationships survived.
Any thoughts?
 
M

Marshall Barton

Richard said:
One last question. When I split the database, I did not use the wizard. (Quite
frankly didn't know it existed)
I simply copied the database and then deleted all the tables in one copy, and
deleted everything BUT the tables in the other copy.
Everything seems to be OK, yet you mentioned there might be some modification to
my VBA code. Got me to biting a few fingernails. I did make sure that the
table relationships survived.
Click to expand...


If all your program's forms and reports run, it should be
fine. There are relatively few differences in using a
linked table vs. a local table. For instance, you can not
open a table type recordset on a linked table so you would
have to either add code to open the back end database object
or change the type of record set. If you change the type of
recordset, then you would not be able to use the Seek
method. Most applications use dynaset type recordsets
anyway so the question doesn't arise all that often.

You already know about the relinking issue when you
distribute a new version of the front end.

Bottom line - Split your databases from day one so you
develop the entire application in the split environment.
 
Top