Database structure grouping

[Ramesh]

HI,

I am using Access to manage a small training centre. Currently i have a
database with students and trainign info, another database with staff info,
another database with the placement info and another with enquiry info. I d
like to know if it would be a better idea to have them all into one single
database or to have them separate the way they are now.

I intend to setup user level security in all the databases now, but it s the
same 2 persons who will be using all the databases.

Thanks
Ramesh

 

[tina]

well, if you're duplicating table data in any two or more of the databases
(two dbs with the "same" student table, for instance) it would be better to
have that data in one place. you could do that by combining databases that
share common data, or simply by keeping the common table(s) in one db, and
linking same to any other db as needed. even if you don't combine the
databases, it would definitely be easier for your users to use only one
interface (forms, reports, etc) rather than having to open each database
separately, depending on which task they're performing. again, you can do
this by combining the databases, or by *linking* the tables from various
databases to a single db housing the interface objects. certainly it would
be easier to maintain one db from the standpoint of daily backups and
periodic compacting.

personally, i would probably combine the dbs, unless there is a specific
issue that is best solved by having one or more processes housed separately.
and of course, whether you have 1 db or 100, the tables should be housed in
a separate .mdb file from all other object (queries, forms, reports, macros,
modules) - in other words, you should follow the standard practice of
splitting the database(s).

hth

 

[Franck]

Easy solution.

Create a fifth database called example mix.mdb
- open it
- go in the table tab
- right click inside the right part (white part)
- select the option Link tables
- it's gonna ask you to select a database
- select it and another window will open, select the table you want to
link and that's it
- re click again in the table section and reselect link table but
choose another database and so on
- when you finish all the table will be LINK, not in the current
project, all the table still run under the original database, not like
the import that bring table to the current database, as link table if
you change something in a table it's like your changing it in the real
database

- be sure the other database will not move or it'll loose is link and
you will have to redo it
- Be sure to not have in any of those 4 database a table with the same
name that the other ones have because you cant link 2 table with the
same name unfortunately.

 

[Al Campagna]

Ramesh,
There may be differing opinions on this, but..
Since the 2 users will use ALL the apps, I'd combine them into one database.
Regarding security... I would think it would be much easier to maintain with only one
app. In one view, you can easily see what User1 is allowed to access vs. User2.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Franck]

unfortunatly access doesnt offer Distributor / subscriber feature like
sql server does, it would have been the perfect situation to use such
system

 

[Ramesh]

Thanks Franck for your solution.

If i created a fifth database, would i still need to manage the user level
security for the objects in each of the separate databases? Or wud i be
able to do that only on the fifth one? will the user level access given in
the 5th database cascade into the linked databases too?

Thanks again
Ramesh

 

[Ramesh]

thanks tina for your response.

i dont have any specific issue being solved by having the databases
separate.

did u mean that it s a standard practice to keep the tables in a separate db
from the other queries and forms? i had no idea about that .. as of now the
queries and forms pertaining to a particular table are together in the same
db. would that be bad practice?

please advise. my knowledge and practice of access is purely based on
reading help files and i missed reading anything on this one.

thanks very much for the input.
Ramesh

 

[tina]

yes, in a multi-user environment, standard practice is to split the data
(tables) from the user interface (all other objects). googling these
newsgroups on the issue will show hundreds of discussions about this same
issue - and the advice is always the same: split the db into FE and BE
files. suggest you read up on the issue at the following webpages:

http://www.granite.ab.ca/access/splitapp/index.htm
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

also, you can learn a lot from the Help files in Access (though finding info
about a specific issue can be challenging <g>), but you need to broaden your
horizons. there are vast resources on the internet that you haven't begun to
tap. suggest you bookmark the following sites to start with; they're
excellent resources in themselves, and also have many links to other great
resources:

http://www.mvps.org/access/
http://home.bendbroadband.com/conradsystems/accessjunkie.html

and, of course, these newsgroups are an excellent resource when you have
specific questions or problems that you've been unable to solve through your
own research.

hth

 

[Franck]

Thanks Franck for your solution.

If i created a fifth database, would i still need to manage the user level
security for the objects in each of the separate databases? Or wud i be
able to do that only on the fifth one? will the user level access given in
the 5th database cascade into the linked databases too?

Thanks again
Ramesh

Actually you still can apply from access right based on user, you will
juste have to create groups of user and give them only access to table
they supposed to have access too.