Unanswered: MSAccess vs. Excel for a small, multi-user relational database?
First post at DB Forums. I'd be very appreciative for any insight you can give. I am a newbie at setting up databases, although having written a number of fairly elaborate Excel-VBA macros, I'm hoping I can come up the curve.
I am trying to determine whether a PC-based Access database is appropriate for my project. For background, I need to:
- Track data on two types of entities: groups and individuals. Individuals belong to one group so there is a one-to-many relationship. There is no possibility of more than 5,000 individuals or 500 groups.
- Most data is single-line text box or numeric, but would like a multiline, all-purpose notes field for each record
- Provide access to a number of users, probably less than 25 individuals overall and rarely serving more than 5 at a time
- Probably not heavy report usage or any heavy-duty analysis; the main virtue is giving a number of people access to structured data without the worry of multiple versions of a spreadsheet floating around.
I am currently using a spreadsheet, and I can place the spreadsheet on a shared drive to allow multiple people access. However, the volume of records, the need to deal with multiline text fields, and to deal with data on both groups and individuals is feeling too cumbersome for a spreadsheet.
(1) Will Access do the trick?
(2) Can this run off of a shared drive and serve multiple users simultaneously? (Purchasing servers is out of the question - my budget is $0, but everyone has MS Access)
(3) Will this be difficult - more difficult than, say, an Excel sheet with the same data arranged through pivottables and elaborate lookups?
Yes, according to what you describe there should not be any problem
2) Can this run off of a shared drive and serve multiple users simultaneously? (Purchasing servers is out of the question - my budget is $0, but everyone has MS Access)
Yes but not like that. You create a back-end database that contains the data (stored into tables), then a front-end database that contains the user-interface (forms and reports) and the logic of the application (relationships, queries, modules of functions, etc). The back-end goes on a server machine (even if it's not running a server O.S., let's call it the server). Every client client machine has a copy of the front-end installed with links to the tables residing into the back-end on the server. The server can be a client too.
3) Will this be difficult - more difficult than, say, an Excel sheet with the same data arranged through pivottables and elaborate lookups?
Probably a little because you do not build an Access application like you would create a complex solution with Excel.
- There are other concerns (security, backup, etc.) that (almost) do not exists in Excel
- You need to have a basic knowledge of the theory of relational databases (say: simplified Set Theory) and of Data Normalization.
- You'll need some time to get used to the Access Objects Model which is different from Excel's.
Nothing you cannot learn (at least superficially) in a few hours. Moreover you need to carefully analyze and planify the application you want to create before actually building it. Failing to do so will bite you in the back and haunt you for ages
If the number of users should increase in the future (over 20-25 permanently), you'll need to transfer the back-end to a more robust system (such as MS SQL Server, MySQL, Oracle, ...) but most of the investments in the front-end would be preserved.
Especially since you can deploy with Access Runtime, which is free. Coming from someone that didn't know anything at all about databases and VB programming it was relatively easy to create the database i have (with the help of individuals on this forum), which is getting ready for company wide deployment (Hope it doesn't blow up). If everything you have is in an Excel workbook and your good at VB programming it will be a simple task. I think it will be harder designing the forms and reports.