Using access as a project management tool

[20201112]

Hello all


I am new to Microsoft Access with very limited experience, so before I start watching hours of youtube videos, I first want to figure out if my idea is even viable.

I work at an industrial company with approximately 1.500 employees worldwide, with a handful of factories in Europe, North America and Asia. Our project management process is very decentralized (countless excel sheets created and stored in countless folders), and I want to centralize this into one Access database.



My approach is to begin small with one database that is supposed to keep track on hours spent on each project on a person and department basis. Creating the relevant tables is the easy part. I know how to do that.
Suppose there are 25 users. I want every user to have access to a form where the user can type in data and retrieve data (export to excel maybe?), but NOT having access to the database file. So can I store the database (maybe with password protection?) on a shared drive, then have 25 forms stored locally on people's laptop, and linking to the database? And can 25 people work in these forms simultaneously?

I hope to get some feedback on the viability of my project. Thank you

[Micron]

Research split database, 'how to split a MS database' or something similar. The way I read your post, some things you say are counter intuitive. You cannot put an Access form on a pc - only a database (which should be the front end only). Yes you can password protect the back end (be), but I'm not sure you know what the 'be' is. The most difficult part can be sharing across different LAN's (locations) but it's not clear if that applies here. When a novice says 'creating the tables is the easy part' I can't help but think 'you don't know what it is that you don't know'.

If you plan on sharing this across different geographical locations I'd say sure it is doable, but I can't help think that you will find yourself in over your head. I don't mean to be dismissive, but after many years of experience I think I have some idea of what you're up against. Last but not least, if the db will be used on a wide scale and must house attachments (documents or images) or security is of utmost importance, Access as the be is not even the right tool.

[20201112]

Research split database, 'how to split a MS database' or something similar. The way I read your post, some things you say are counter intuitive. You cannot put an Access form on a pc - only a database (which should be the front end only). Yes you can password protect the back end (be), but I'm not sure you know what the 'be' is. The most difficult part can be sharing across different LAN's (locations) but it's not clear if that applies here. When a novice says 'creating the tables is the easy part' I can't help but think 'you don't know what it is that you don't know'.

If you plan on sharing this across different geographical locations I'd say sure it is doable, but I can't help think that you will find yourself in over your head. I don't mean to be dismissive, but after many years of experience I think I have some idea of what you're up against. Last but not least, if the db will be used on a wide scale and must house attachments (documents or images) or security is of utmost importance, Access as the be is not even the right tool.

Thank you for taking the time to answer

Well, what I meant by "easy part" is that it is "easily" searchable. Yes, I will probably spend lots of hours of research and trial-and-error learning until I succeed in creating those tables. Yes, I planned to make a database as backend, and then the forms stored locally on the users' PCs as front end to type in data and retrieve data, preferably by exporting to Excel.

Everything is an upgrade of what we are doing now. As I said, countless excel sheets stored on countless drives, some of them accessible by all of our locations, some not. We don't need attachments.

To try and clarify a little: We store the database on a shared drive, sort of like a private, in-house cloud storage. Then the users get a copy of an Access file, on which the form is, and linked to the database. Is it doable you think?

If this is doable, and a success (again, everything is better than Excel sheets!), I hope that I can persuade my boss to invest in a more professional solution, preferably something SQL based, made by real developers As it is now, it's a PITA to try and get any kind of overview of how many hours employees and departments spend on each project, and also how many hours we have available in the future, so our sales people don't sell more than we can produce.

[orange]

As an industrial company with approximately 1.500 employees worldwide, have you or your organization considered a commercial package? Like micron I think you're talking a task that could be fraught with risk.
It appears to me, who knows nothing of your organization's details, but only your post, that data/information management discipline is/has not been a priority in your company.

You might want to review other companies in your market sector, or just similar size and geo-locations and research how they approach project management.

If you do want to some youtube suggestions, then see the Database Planning and Design link in my signature.

Goo luck with your project.

[Micron]

If you mean try to edit data on a cloud based be, definitely not. To work on a single be file over different networks you should use something like Terminal Services (I think TS is called something else now) or Citrix to access the be on a network server. That is not my area of expertise so I can't offer specifics, but everything I've read indicates that trying to work on a cloud based db is a recipe for disaster.

As for table design, you could always post a pic of the relationships you create from those tables but anyone giving feedback would need a good understanding of the process.