Access & Sharepoint Link username to filter records

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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Unfortunately for remote users, SharePoint is not really a solution.

Your best option is to setup a RDP Server (Terminal Services), place the DB there, then have people remote into that.   With the newer servers, these can take the form of an icon on the desktop, and it looks and acts like any other installed app even though they are executing it remotely on the RDP server.

I can go into more details if you like, but Access Web Databases and Access Web Apps have all been depreciated at this point.  You can still store data in SharePoint, but that will gain you nothing.

RDP is it unless you want to write the app in something else.

jdc1944Author Commented:
Thanks for your comments Jim.  The users in other departments are still within the same building so they are not remote users.  If they are remote users then we have access to Direct Access and Citrix Access Gateway.  Is sharepoint still not possible?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The users in other departments are still within the same building so they are not remote users.>>

 As long as they are on the same LAN, then you just need a dive mapping to the app.  This is assuming that you have "split" the app into a front end / back end.   Back end (with the data) being on the server, and each user having a copy of the front end (everything else).

<<If they are remote users then we have access to Direct Access and Citrix Access Gateway.  Is sharepoint still not possible?>>

 You'd still not to want to use SharePoint.  Access can use SharePoint in three ways:

1. To store data there (any version) - There's no real advantage to this as all users can see as the raw data.  Also, performance is an issue once you go past 5,000 records.   Everyone gave up on this long ago.

2. Access Web Databases - This applies to Access 2007/2010.   Data was stored in SharePoint, and with Access Services in SharePoint, you could off a app through a web browser.   Problem is, the app you can offer resembles nothing like what you can do in the desktop and the data was in SharePoint.

3. Access Web Apps - This was rev 2 of getting Access to the web and was done in Access 2013 and 2016.   The change was that the data was moved into a Azure DB (SQL), and the web app front end was served through SharePoint.   Again, the app resembles nothing like would you'd get on the desktop and what you could do with web apps was very limited.

 Microsoft (and developers) have given up on all of that.   So really, forget about SharePoint<g>.

 As long as your users have a network connect with latency <30ms and have a speed of 100mb/sec or more, they can run the app directly.        If you can't meet that, then you can:

1. Move the data into SQL server

2. Run the current app on Citrix or Terminal Services.

  The first may require a re-write to get adequate performance depending on where the users are, but you do get rid of the problems of running on a wide area network.

  #2 is the route most go because it's basically "plug and play" and the app needs little (if any) changes.

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

I don't like duplicating forms but this may be a situation where I would do it. Giving the other people access to the entire app doesn't sound like something you want to do so that would mean imposing a login system on top of the app and modifying all the forms (or possibly just the menus) to restrict access to only specific users.  It might be simpler to create a very focused FE for just the "other" users.  I would make them log in so you can restrict them to only updating their own tasks.  If there is no problem with seeing other people's tasks, I wouldn't bother to control viewing.  But you will need to control updating.

If you already had a login and security functionality, I would recommend expanding it to include the new people rather than creating a separate FE for them.
jdc1944Author Commented:
Thank you both for your help so far.  Having read your comments I've just spoken to our IT dept. and Access is not part of our standard build.  It's not something they will really consider to be part of the standard build either, instead it is added to a device where there is a business need.  This isn't really something that is going to be manageable - I've just had a look and we have actions assigned to over 280 people outside our team and it's growing each month.  Making a request to IT to have Access installed on several devices each month isn't going to go down well.

If SharePoint is a no go, and actually getting Access on users machines isn't looking like a manageable solution, is there any other alternatives.  I'm starting to think my idea is dead in the water now.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I've just had a look and we have actions assigned to over 280 people outside our team and it's growing each month. >>

 So you need some form of web based input more than likely.

 Without a lot more detail, it's hard to say what would be the best approach, but here are the different options:

0.  Leave the app in Access and use the run time version.   While IT would have to install the run time, the run time is free otherwise.   You can add as many users as you want.  As long as everyone is on the same LAN, you can stick with a JET/ACE DB.   If not, then you'd need to move to SQL server, and possibly re-write parts of the app for good performance.

1. Do the entire app in SharePoint - SharePoint is a workflow tool after all, and it does that very well.   Not sure what your doing with the Access app currently, so this may not be possible.

2. If your SharePoint is an on-premise server, then you could build a hybrid app with Access.   This is where you have a Access Web App running under SharePoint, with a SQL Server back end for the data.   You then point your desktop Access client to the same SQL Server DB.  In short, you have two "front ends", one for remote users and one for in-house.

  Note that you can only do this with SharePoint on premise (Microsoft has already cut-off all Access Services in Office 365), and that you need an enterprise license for it to be able to use Access Services (what let's you publish an Access Web App through SharePoint).

3. Re-write the web part (or the whole thing) in a traditional Web language, such as ASP.Net or PHP.

4. Look at using Word Press, which let's you build sites with forms and other features easily.  Word Press is a framework with plug-ins that you use to give you functionality.  Data is stored in mySQL.   Word Press hosting sites are many and reasonable.

5. Take a look at Microsoft Power Apps.   While still in their infancy, this is where Microsoft has their focus.   If your needs are simple, they might be able to do the job as they stand.   This is why Microsoft gave up on Light switch and doing Access on the web.  This is their new platform for the mobile world.

   #1 and 2 let you work with what you have (possibly), but their not good options for moving forward into the future.  #3 will depending on your needs be costly, but will stand you in good stead moving forward, and there is nothing you won't be able to do.

   #4 will let you put something together fairly easily and cheaply, and if it fits your needs, might be the best way to go.  

   #5 is a question mark right now....Microsoft is putting everything they have into it, but it's not quite ready for prime time as yet.   But if what's there fits your needs at present, then I would seriously consider using it.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If users have permission to install apps on their computers, you can send people a link to the Access runtime so they can use that to run their app.  If the PCs are too locked down, then you're right, Access is going to be problematic if it isn't normally installed.

If you are not in a position to create a web app, you might be able to do something by sending Excel files to the non-connected users and taking back updates.  This will work well enough as long as you are careful regarding what can be updated.  You don't want to be able to update the data from two directions.  If you do that, it is extremely difficult to reconcile the changes and decide what is current.  But if all the users do is enter a status, a date, and perhaps a comment and you people don't update those, then the spreadsheet can work/
jdc1944Author Commented:
Excel might be a short term solution, PatHartman so I will look into that thanks.

Jim, I like the look of Power Apps and is certainly something to watch for in the future as Microsoft builds its functionality.
However I suppose this brings me back to my original question.  How can I administer and manage access to the database via Power Apps.
As mentioned I don't currently use any inbuilt functionality (apart from using =environ("username") and a small table of usernames which isn't going to work for other departments unless I get everyone's usernames and then I'm not comfortable having a table holding hundreds of usernames) and now adding web access makes me concerned whether the inbuilt login and security is suitable.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<However I suppose this brings me back to my original question.  How can I administer and manage access to the database via Power Apps.>>

 Unfortunately  I don't know the answer to that.    I will try and get Joe (DatabaseMX) to look at this.   He's the only one I know of in the Access group here that has worked with Power Apps.  

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" Unfortunately  I don't know the answer to that.    I will try and get Joe (DatabaseMX) to look at this.   He's the only one I know of in the Access group here that has worked with Power Apps.  "

So what is the specific question(s) regarding PowerApps ?

re: "I like the look of Power Apps and is certainly something to watch for in the future as Microsoft builds its functionality."
Actually, the Future is Now.  I would loosely peg PowerApps as 2.0 in comparison to Access. But it's a moving target and changes monthly, if not weekly in terms of enhancements and bug fixes.

There is a HUGE PowerApps Community as well.
jdc1944Author Commented:
Thanks for everyone's input.  I think it is best to open some more specific questions regarding PowerApps rather than continue in this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.