Requesting guidance on which direction to go with a database

[securitywyrm]

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.

[aytee111]

You could use SQL Server, Access has a wizard to convert the tables, then there can be multiple front-ends with that one back-end.

[CJ_London]

you also need to clarify with your boss what is meant by

Others who work with our department

. Are they company employees who have access to your network, if not to the backend folder? And/or are they outside contractors who clearly won't have such access? Another important question is how these others are connected? via wireless or hardwired?

And you also need to clarify what these others will be doing - just viewing data or will the be updating/appending as well?

[NTC]

I don't quite follow your post. But in sum if you wish to restrict your application to a set of authorized users there is several ways to do that:
a. create a log in screen
b. if Active Directory is in use, auto call in the Active Directory Users ID (essentially same as a. but is automatic)
c. as part of the permissions profile of Active Directory - control who gets a front end to the app

The 3 methods you suggest are not the way to go. With Access split, as it should be for multi user, - then the back end goes into a file folder common to all users - - and each end user gets a front end that links to it. You simply don't give an FE to those who have no need to use the application. If you are worried about people going directly into the back end file - in a way that's a management problem but it can be managed by using the encryption feature which prevents that.

[ssanfu]

<snip>
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.
<snip>

Maybe you could have a directory (folder) on the server that gives/has permissions to only that directory. Put the BE there and link using DNS less connections.
That would be the only directory they could get to on that server. Talk to your IT dept to see if they would go for that.