Databases share tables

  • Thread starter Thread starter A Hopper
  • Start date Start date
A

A Hopper

I have an database that is front end and back end. Due to
sensitive materials I have been asked to create a second
database for some of the information. (I suggested
password protection but it was declined.) I want to use
the same employee information in the two databases (first
name, last name, address, etc.) If I link the front end
(Form) from the second database to a table in the first
database's backend can I share the information? Can I or
should I link a database to more than one backend? Is
there a different or better way to accomplish this?

Thanks
Allan
 
Normally speaking, you would only need to have one table for each set of
related data and the only DB that should need to be linked would be your FE
DB. See below to see what DB consists of what in general:

BE DB

Tables
Data

FE DB

Links to the Tables in the BE DB(s)
Relationships
Queries
Forms
Macros
Modules
Anything else that I may have missed.

The one area that I need to steady up even more is the Security aspect of
Access, though I know it to some extent, but it's the one area that I don't
have too much experience with in Access.
 
If I link the front end (Form) from the second database to a table in the
Yes.
Click to expand...

Some will say don't do it! But you can easily do it. I have a database that
has 4 backend databases... the reason is that each of the databases have
different permissions set... it's just easier to set permissions on all
tables in different databases rather than half the tables having one set of
permissions and the other half having a different set, especially when
Access sorts tables in alphabetical order, i.e. not in the order you would
set permission on!

BTW... is the sensitive data not sensitive enough to protect it?

HTH.

Tom.
 
I have an database that is front end and back end. Due to
sensitive materials I have been asked to create a second
database for some of the information. (I suggested
password protection but it was declined.) I want to use
the same employee information in the two databases (first
name, last name, address, etc.) If I link the front end
(Form) from the second database to a table in the first
database's backend can I share the information? Can I or
should I link a database to more than one backend? Is
there a different or better way to accomplish this?
Click to expand...

The best way is to implement Access security on the database. If the
information is in an unprotected database, whether a different backend
or elsewhere, anyone knowledgable about Access can get to it easily.
If it's secured, it would take a very experienced and determined
hacker to break the security.

If implementing security is unacceptable, I'd suggest two backends,
and that you NOT store the employee information twice! Instead have
the confidential information in a table with the EmployeeID as its
primary key. You cannot maintain relational integrity across two
databases, but you would be able to create a Query joining the
employee table to the confidential table with a one to one
relationship.
 
Yes, I also have a DB system that has 1 FE DB and currently starting out
with 5 BE DBs. There's a 2 fold reason why I have setup the multiple BE
DBs. Set asside from the security stand point of view, which will more than
likely fall in line [in general] with one of my 2 reasons anyhow, each BE DB
file is created based on the type of information that it's storing, sort of
like how libraries works on commercial DB programs. The other reason being,
there can only be up to 2GB of file size per DB file. Of course, my DB
system is still in the creation stage, but then I spent a huge amount of
time planning it too.
 
Tom
Thank you for your reply.

Yes the sensitive data is sensitive enough to be protected
however, it is so sensitive that they would like to have
it protected and in a seperate database with a frontend
that is given to authorized persons. I had originally
integrated it into one database and was going to limit
access by security. Will the common link between the front
ends to the employee file (back end) compromise security?

Thank you again
Allan
 
John,
I don't think that implementing security is unacceptable,
however, there are two factors first, I have not used
security and I find it intimidating. I don't have an
understanding of how upgrades are done once the database
is secured and I understand you can lock yourself out of
the database if you don't know what you are doing.
Second, I have been asked to keep the information in a
seperate location, which I think I should also secure. So
I will need to do research on how this is done.

The query you mentioned would that be located (for
example) in a combo box on a form in one database frontend?

Thanks for your help
Allan
 
Ron, thank you for your help. I also need to look into
Access security, and become more confident with trying it.

Allan
 
From what you are saying, you want security and think that is by putting the
data in a different database... no way! The backend can be opened just like
any other database and edited/viewed... just because a form has permissions
assigned to it doesn't mean the backend is secure... You don't need forms to
view data in a table! The link isn't a security problem, its the BE
tables... Beware of 'clients' who lose data and then say 'but no-one told me
that...' a secured workgroup is a good way to start the discussion...

HTH.

Tom.
 
Tom, thanks for the insight you have given me. I had not
thought about the BE security. I thought that if I used
the security on the FE(mde) it would also cover the BE
(mdb). If I want to secure the data I have to secure the
BE and then grant permissions to users through the FE. Is
that correct? I have looked into Access security and at
one point I experimented with it. I did have it on a copy
of my database and was learning how to set up workgroups
etc, however, I was not certain how updating would work
and was nervous about locking myself out so I returned to
my copy without security. The BE, FE security sounds
complicated.

Thank you for your help
Allan
-----Original Message-----
From what you are saying, you want security and think that is by putting the
data in a different database... no way! The backend can be opened just like
any other database and edited/viewed... just because a form has permissions
assigned to it doesn't mean the backend is secure... You don't need forms to
view data in a table! The link isn't a security problem, its the BE
tables... Beware of 'clients' who lose data and then say 'but no-one told me
that...' a secured workgroup is a good way to start the discussion...
Click to expand...
 
John,
I don't think that implementing security is unacceptable,
however, there are two factors first, I have not used
security and I find it intimidating. I don't have an
understanding of how upgrades are done once the database
is secured and I understand you can lock yourself out of
the database if you don't know what you are doing.
Second, I have been asked to keep the information in a
seperate location, which I think I should also secure. So
I will need to do research on how this is done.
Click to expand...

Get a copy of the Security FAQ: you can download it from CompuServe at
the address in my signature (it's free), or find it on Microsoft's
website http://support.microsoft.com. Read it carefully. Then read it
*again*. Then follow its instructions, step by step. Security is
tricky, but this FAQ provides a good and thorough map through the
swamps.
The query you mentioned would that be located (for
example) in a combo box on a form in one database frontend?
Click to expand...

Well, you can't put a query in a combo box... any frontend which
needed to display employee data in conjunction with the secure data
would contain a stored Query linking the two tables (which might be in
the same or in different backends). As noted elsethread, you need
security on BOTH the backend and the frontend.
 
Yes... The tables in the FE, i.e. the linked ones, shouldn't have any
security on them because these links are just that, links... the security
should be applied to the tables in the BE. So set the permissions on the BE
tables. The queries, forms, reports etc., in the FE have permissions set on
them so that only some users can open a salary form, etc. It is well worth
looking at this carefully as it's not as difficult as you may think. One
point is not to set individual permissions, only group permissions... this
way you add new users to groups that already have permissions set.

Good luck.

Tom.
 
Back
Top