Project Feasibility

[mskusace]

Good Afternoon,

I have developed in MS Access in the past, but I had a lot of guidance and I have forgotten most of it now. However, I have a much stronger database knowledge and I believe overall developing knowledge. My question is in regards to the feasibility of a project given the tools and systems I have at my disposal, as well as my own skill set.

I need to create a project tracking tool that can handle multiple user access with varying permissions. I have restrictions with the systems and tools at my disposal. I have access to MS Access, a local shared drive, a SharePoint intranet site, and potentially a central Oracle (or some other type) database. There will be some repeating tables because one project may have 5 entries whereas others may have 10 for that particular field. Each program will have multiple projects so I need a form entry system for new projects and then a tabular sort of view for quickly editing projects for each program. There will be multiple permissions group. For example, a project manager should only be able to see information related tot he projects he or she is managing. A program manager should be able to see all the projects under him or her, but not for any other program. A director should be able to see all the projects under the program he or she manages.

I wasn't sure what the best path forward was or if Access is even the best option available. I might be able to get some space on a web server with concrete5 or Bootstrap framework for front end development, but it would all be new to me as I am not a web developer, much less a developer in any other system.

Any guidance or input would be greatly appreciated. I am happy to provide more details if needed.

 

[stumac]

It would be possible to do this in access, however, it seems like you are trying to build something (which is fairly complex), that already exists and is available with the features you need for free or at very low prices...

I would recommend having a look at Trello and JIRA for a start.

If you do decide you need something more bespoke for your needs then I would say have a look at the online templates available for project management within Access (on the New file screen, search for project). It may be one of them would give you a good start. If you need specific help then post your questions up.

Access security is not a strong point, you can filter data out based on filters you create, however, it wouldn't take much for someone to be able to gain access to the underlying tables.

Good Luck with your project.

 

[mskusace]

Thank you for taking the time to reply @stumac.

I see all your points and we are actually using Trello currently and transitioning over to JIRA now. One clarification I did not make is that this is for other programs to input their basic data strictly for reporting purposes. There is a monthly brief that occurs where we capture certain data and need a data entry system for the program managers to enter certain data. This is not meant to be a project management tool for their teams to use, but for the reporting division to use to populate the dashboards. Unfortunately, there are many different systems used and they don't all communicate. I was trying to create a single point of entry and data retrieval for these specific elements we are trying to capture.

Would your recommendations still hold based on that information?

 

[stumac]

Ok I understand what you mean, as you are moving to Jira you should have a look at portfolio for Jira which may be configurable for what you need. (I haven’t used it)</SPAN>
In terms of Access this is possible, I would recommend that you have main data tables like:</SPAN>

Programmes</SPAN>
Projects (this would link to programmes)</SPAN>
Metrics (this would link to projects)</SPAN>
Users (this would link to programmes and projects)</SPAN>

There is various ways to view the information, however, I would think a form for Projects with a subform for metrics to be added in. At programme level I would have a main form with a subform for linked projects and I wold summarise the metrics on that form.
</SPAN>
I would use VBA code to set what form opens for each user (or role) and then on the forms load events set the recordsource to only show applicable programmes/projects.</SPAN>

I realise this is vastly oversimplified, however, its where I would start if I was building an Access DB.</SPAN>

 

[mskusace]

Got it and that makes sense. In terms of the multi-user simultaneous access to the database for entry and retrieval, what would your recommendation be to handle that? I can use a networked share drive, an internal SharePoint site, or possibly even a hosted database. This is something that I tried to do in the past, but I could not achieve due to my limited knowledge. I am using MS Access 2013. Also, how should splitting be handled (I made an assumption that any approach would require some sort of splitting)? Is it possible to use Access just as a front end and connect to a hosted database on one of our servers? Then if so, that could make it easier in the future to transition to a web based front end system.

Thank you again for the reply!

 

[stumac]

It depends on the numbers you are talking about - Access split to a Front End & BE could be enough. I have used SQL for larger projects and used an Access FE (as that has been the only option/skills I had available). If you create it in Access then there is a tool for upsizing to SQL.

I have uses SharePoint Lists as back end tables in the past too, however, I found that to be somewhat clunky in comparison.

In short - I would create in Access getting it to work and then split out, if you feel you need the robustness of a better BE then look to upsize and relink the FE to the new source.

 

[mskusace]

Is there a best practice for splitting a database and also for creating those various permission groups? I don't need extremely tight security since it should not be a big issue, but I would like some line of defense to prevent people from viewing/entering data in the incorrect location by accident. We have an access card we used to authenticate our log in into the computer systems here and we also have unique window users names, which I have a list of everyone (which I also believe is tied to the access cards). If I could use that to authenticate or give/grant permissions as opposed to some sort of password system, it would be preferred. Would that be possible?