Hi guys
I developed a database application using VB6 for the Adult Education Department at my University, using advice from this thread http://forums.devx.com/showthread.ph...639#post470639

The staff was previously using MS Excel and MS Word when I came along and they seem pretty pleased with the automated system. Now, they want me to extend it a handful of users with various access levels and permissions. So here I am. Now before I go on with my question, a little about the system:

Basically there will be two databases - currents and archives. Since at any time the currents database will contain a maximum of several thousand records, I assume I can stick with MS Access (can I really? )
The archives obviously is huge, holding records from as long ago as 1992 so I used MySQL here. The VB6 front-end interface is the same for both the databases meaning I only use the MySQL connection for the less-frequent querying and back-up operations. The currents database in MS Access meanwhile is accessed through an ADODB connection.

Now I am looking at providing various user access levels in the system. The authorisation that is required is actually on rows in a table (in the currents database only), rather than on add/update/delete operations. That is, a course officer can only work with courses that are assigned to him/her. Meanwhile the system admin has access to all courses. Authorisation is only required on the currents database.

And very briefly on the currents database: users are categorised based on the records in the Courses table (Joe is the course officer for Addiction Studies and Psychology, John is the course officer for Family Studies, etc). Tables like Students, Assessments, Course Weighting, etc are linked to the Courses table and hence must be appropriately made available to the authorised users.

How would I provide authorisation in such a case? I can use views, then I have to move the currents database from Access to MySQL. Is this the only way? I'm also looking for suggestions and advice on implementing concurrency in both the databases. There will of course only ever be less than 10 simultaneous users logged in.

Any thoughts on this? Cheers guys