At the moment I have about 40 databases scattered all over. What I would like to do is to make a main page for all of the databases sorted by departments. My question is that if I do this, can I still keep the securities seperate for the people who can access which ones? Can I open misc databases inside of others or will I have to create the whole front end as on. We do not use the access securities we use active directory for that. I am using access 2003 on windows xp but switching to sql server 2005 for the back end soon, front will still be in access. Let me know. Thanks
If you are converting to SQL Server then security issues should be converted to SQL server as well! with your frontend designed and built around that dictated by your SQL server database design arrangements.
A thorough understanding of how your data will be 'laid out' in SQL server is I believe essential for you to deal with 'before' any GUI interface redesign as I feel you will only be revisiting it time and again otherwise.
For instance 40 databases in Access can equate to one or maybe only a very few databases in SQL server separated out and broken down maybe by entity type or other 'data flow' considerations. All of it can be presented to the desktop uiltimately via a single frontend or indeed separated out to different frontends it depends largely on your model.
You can for instance query one database in SQL server from another database in SQL server simply by referencing the database object by the database and owner name
(example SELECT * FROM address.dbo.tblAddress )
where 'Address' is the name of a specific database, 'dbo' is the default owner and 'tblAddress' is the name of the specific table yet you might be logged into a completely different database entirely.
SQL Server security is much more robust than trying to stick by any existing strategy in Access. Grant access,select, update,delete permissions are all handled in SQL server including selects, updates and deletes right down to column level based on users who are stored in SQL server and authenticated against using either windows integrated security and/or on a domain, or sql server stored users and password.
Users and OS window groups can be added to 'roles' and so on, so that you can have permissions tied down all ways round on the server side at a 'single reference point' as opposed to on any Access side scattered around in maybe local access tables.
Hope this helps a little
Regards
Jim :)
234
