sample code to create and drop a link to an external DB table.

[Guest]

In order to prevent someone from opening the FE DB (by using the Shift key
technique) and changing data in a linked table, I would like to set up the
link in code when the FE is opened and drop the links when the FE is closed.
I have not been able to find how to do this in VBA.

If you know a better technique, please let me know.

Thanks,
Bob

 

[Marshall Barton]

In order to prevent someone from opening the FE DB (by using the Shift key
technique) and changing data in a linked table, I would like to set up the
link in code when the FE is opened and drop the links when the FE is closed.
I have not been able to find how to do this in VBA.

A couple of ways to think about.

Since a linked TableDef doesn't contain much information
beyond its Connect property, you can just delete the
TableDef when you close your app and recreate it (using
TransferDatabase or DAO methods) in your startup procedure.

An alternative is to not have a TableDef for the linked
table and use a query that specifies the backend table using
IN "same as Connect property" in the query's FROM clause.

 

[Tim Ferguson]

"=?Utf-8?B?V2lzY29uc2luIEJvYg==?=" <Wisconsin
(e-mail address removed)> wrote in (e-mail address removed):

If you know a better technique, please let me know.

Access user-level security. You know it makes sense.

HTH


Tim F

 

[Guest]

Tom,
I'm thinking that user level security would be used at the time the link is
initially established in the FE application. Then if the FE is saved with
this link established, anyone could open the FE and open the linked table.

Am I wrong about this?

Bob

 

[Guest]

Adding TableDef code looks like what I need. I will try it.

Thanks,
Bob

 

[Tim Ferguson]

I'm thinking that user level security would be used at the time the
link is initially established in the FE application. Then if the FE is
saved with this link established, anyone could open the FE and open
the linked table.

Am I wrong about this?

Really, it should be a design-time decision; at least it's an early
implementation consideration. The overall plan in a nutshell is to:

+ remove _all_ privileges from users on the tables and
everything in the back end.

+ define queries that allow users to write/ see the data
they need, and give the queries Run With Owner
Permission. This means that the queries run with your
access level (e.g. Admin) so that the users can do what
they need to. The users have read and write permission
on the queries (i.e. they can interact with the data),
but not design (so they can't rewrite the query itself).

+ In the front end, the users need to be able to see the
various GUI objects like forms etc, but not design them
or create new ones.

Bear in mind that you need to read all the instructions before securing a
database in Access -- at worst you can lock yourself out of your own
data; and it's easy to leave holes too. Once it's working, though, it's
pretty secure against anything less than a really determined effort. If
you absolutely have to lock up everything, then you need a proper RDBMS.

Look here for more info:

http://support.microsoft.com/kb/q165009/
http://www.vb123.com/toolshed/00_accvb/accesssecurity.htm

Hope it helps


Tim F

 

[Guest]

Tim,

Excellent! I agree totally.

Thank you so much.
Bob