Link to a table in a secure database

[Deirdre]

I'm using Access 2003, and am in an unsecured database. I want to link to a
table in a database with user level security. How do I do this?

 

[Joan Wild]

The easiest way is to open the secure mdb and grant read data permission on the table to the Users Group, and also Open permission on the Database.

 

[Deirdre]

The two databases have different workgroup files. And when I try to link the
table I'm getting the "doesn't have permissions" error because I'm not using
the shortcut to open w/ the workgroup file.

 

[Rick Brandt]

The two databases have different workgroup files. And when I try to
link the table I'm getting the "doesn't have permissions" error
because I'm not using the shortcut to open w/ the workgroup file.

You could accesss it in code because that would allow you to create a new
workspace that specifies the other workgroup, but you cannot "link" to a secured
table from a file that is not using a workgroup that allows access to that file.

 

[Deirdre]

I just did this and it worked. But what does this mean for the security on
the database now, who is it accessible to?

 

[Deirdre]

All I need is for the data to refresh upon opening. And for users to be able
to make updates in 1 table only. I would need three linked tables in total.
This is a front end, where I prompt for user values which determines the data
they will see on a form. I don't really even want them to see the tables.
Just the entry screen (menu form) and a data entry form. They will have
server access to the folder where the back end resides, but I don't want them
to be able to open the back end. The back end will be used by two people
only with user id and password. Is this possible?

 

[Rick Brandt]

I just did this and it worked. But what does this mean for the
security on the database now, who is it accessible to?

If you allow the "Users" group to open your file and access a table then you are
allowing *any* user using *any* workgroup file to do the same (you have no
security at all now).

 

[Rick Brandt]

All I need is for the data to refresh upon opening. And for users to
be able to make updates in 1 table only. I would need three linked
tables in total. This is a front end, where I prompt for user values
which determines the data they will see on a form. I don't really
even want them to see the tables. Just the entry screen (menu form)
and a data entry form. They will have server access to the folder
where the back end resides, but I don't want them to be able to open
the back end. The back end will be used by two people only with user
id and password. Is this possible?

You either make them use the secured workgroup and give them permissions or you
remove security. There is no in-between. You can use obscurity and obfuscation
(hide stuff), but you cannot use security unless they use the secured workgroup.

 

[Joan Wild]

Anyone with Access could 'open the database', and 'read that one table', but that's all.

I think you could use this as an interim measure, but you should reconsider your setup. It sounds as though the two databases should be combined into one, or at the very least they should be secured using the same workgroup file.

 

[Deirdre]

Thanks so much. I set up user level security, then copied the database and
split myself (when I used the database splitter it wasn't securing both front
end and back end). The two are now using the same workgroup file and I can
link to the back end tables while preserving the security on the back end.