[AccessD] Connecting Access to SQL tables

I have written extensively on this subject, but so have many others.

Create an Access ADP project and connect it to your SQL 2000 database. This is a much superior strategy than using ODBC connections from an MDB.

Allow no one but yourself (and other db developers if any) direct access to the tables. Instead, create views and/or stored procedures and/or user-defined functions to access the tables. Then think about your app and create some roles corresponding to users and the power they should have (i.e. the lowest level in the hierarchy should probably not have the ability to run a report listing all salaries at all levels). Always think in terms of roles, not users. There is a huge reason for this: you add users to roles, and you can even add roles to roles. I am a big fan of this approach. For example, a sales manager in a pinch may have to do data-entry of sales. So add the SalesManager role to the DataEntry role: then any SalesManager can do both, whereas the DataEntry people cannot.

That's a thumbnail sketch of how to approach this. Basic two rules: nobody but developers gets access to database objects such as tables; roles define the permissions within the org, and users are simply added to roles. That keeps it all lean and mean and easily manageable.

Arthur

----- Original Message ----
From: "Johncliviger at aol.com" <Johncliviger at aol.com>
To: accessd at databaseadvisors.com
Sent: Friday, November 17, 2006 9:14:09 AM
Subject: [AccessD] Connecting Access to SQL tables

Salutations
 
 I am proposing to connect Access FE to SQL 2000 tables  which is simple but 
is it wise?
 With say 40/50 users using an Access runtime FE should a prudent  database
developer be connecting directly to tables. What should be  the
strategy in multi-user environment  where data is being  entered and 
modified??

There must be some documentation around on  this subject comments and
links will be thankfully recieved.

TIA
johnc

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com