We rely heavily on an Access database within our team to control actions that we need to undertake. This is basically recording the action to be undertaken, its status, its completion by date and a progress log. The action to be undertaken and completion by date remains static but the users assigned to the action are responsible for updating the status and progress log.
The database is secured on a share file so no other departments can gain access. As a result the database doesn’t utilise usernames or passwords. Instead there is a table of network usernames and the email addresses (so email reminders can be sent), it’s a static team of about 10 people so doesn’t require any maintenance. There is then a procedure when the database loads that determines the windows username the user has logged in with and uses this to filter the actions only allocated to them (=environ("username"). Actions are assigned to the users username basically.
The problem we have is that we are starting to allocate these actions to people outside our department. The current process which is now separate to the above is that the action is set to an email address and when the completion by date is passed, an email is sent asking them for an update on progress. Once this update has been received by our department, someone must manually go and update the records based on the email response. This is becoming a more time consuming task as we asign more and more actions to people outside our department.
What we want to do is make the users in other departments responsible for making their own updates and considering we have a SharePoint server, this seems the best solution at the moment. I don’t want to share the whole database because it will in my opinion become harder to manager users and also the database contains other forms and reports that I don’t want people to access (although probably better to create a separate frontend just for people outside our department).
The idea is that I will publish the front end and a few forms to SharePoint so that when a completion date has passed, an email is sent to the user in the other department with a link to a SharePoint site where our database has been published. Here they can only see and update their own actions. Or they can access and update at any point in time.
My question is, how can I control access and link this access to filtering only their actions, so that when they log in they can only see their own actions. Can I still determine the network username when access has been made from Sharepoint (its windows based loging in/SSO). One of my concerns with this is that I will have to maintain a list of usernames in the database to match the logged in user with, which might be ok for about ok for about 10 people but perhaps not such a good idea for security and maintenance when this could grow to hundreds. Are there any other solutions to this?