Greetings all! I'm in a bit of a pickle of in terms of which direction to go with a database, and could use some guidance.
Some background: About 1/3 of my job is programming access databases. I have no formal training in access, and am learning how to do things when the need comes up to do a new thing. This forum has been tremendously helpful in that regard. This haphazard approach to learning access has been stressful, but I'm proud of what I've been able to accomplish. So in response, please remember that I don't have any formal training in Access, nor am I likely to be allocated the time to take formal Access lessons.
The situation: When I was hired, our 'contacts database' was a giant excel file, 80 fields wide, terrible to use and terribly out of date. I created an interface on top of that excel file, trimmed out redundant and unused fields, established an update system to verify records once a year, and for a long time that worked. It was an internal database, so my ability to pull information out of it in 1/100th the time it ook the previous person was much appreciated.
Then, my boss wanted me to make a "new" contacts database, so "other people in the department can use it." The database I had made was functional but certainly not user friendly, as I was the only one using it. I then make a proper database with forms and data separated, and create a forms file with locked down permissions and settings. The data file is on our shared network drive that anyone else in the department has access to, but if someone else somehow gets the form file they can't pull any data with it.
However now my boss is saying that by "others in the department" she meant "Others who work with our department." Thus I can't keep the data on the shared network drive, because people outside our department can't have access to that drive. As I see it, these are my options I've been able to identify, hopefully you experts can recommend which would be more practical than the others, or perhaps have options I don't know about.
1. Put the data file on Sharepoint, and link the forms file to the new location. My main issue is that our Sharepoint doesn't have fantastic user access controls, and I'm worried someone will mess up the core data.
2. Merge the forms and data files into one file, and put that up for download. I'd have to re-upload it any time there's new data, and they'd have to re-download the file each time they want to do something to make sure they have up to date information. However, this also solves the "people breaking the core data" issue.
3. Use the export to sharepoint option to upload the data itself to sharepoint, and then try to build sharepoint forms to read it using Infopath. I know next to nothing about how this is accomplished, only that it can be accomplished.
I'd really appreciate some guidance on what to do. My boss wants a solution "today" and I still have hours of my 'regular' work to do.