multiple-tier Access DB app

[wengang]

Hi all.
I need to build a database application for my office, but it has to be done in Access with no VB or other environment, in other words using the forms in Access.
The database will be accessible to only a few people over the office network, and will be a place for everybody to upload data via an input form, or view reports.

The questions I have are about the architecture. I've only ever made Access databases for individual use.

How can I have the central database in a location where people can't see it, but still have access to the data, both read and write? Would it be one central database and then an individual database for everybody else? How would I link them, or have one point to the other? How could I password protect the central db so that people don't open it and damage the data tables, and at the same time still have the local access not reveal that password somewhere in the VBA?

If I have it all wrong, just ignore the above questions.
Basically, I need a central database, limited access by multiple users over the network, and the forms in Access as the interface.

Can somebody give me the broad strokes?

Thanks.

[techgnome]

1) that's not a mutli-tiered. It's not even muli-layered as everything would be in Access. (that point is debatable though)
2) Personally, the second multi-user access comes up, you should dump Access and go with something like SQL Server, Oracle or even MySQL.
3) If you use a shared Access database, then it has to be in a folder, inside of a shared folder, on a server that EVERYONE has access to. That means if someone figured out where the mdb is, they could navigate to it directly and open it.
4) It means EVERYONE that access the app would need to have Access installed ... there's licencing issues there.
5) Personally I'd go with a VB front end and a SQL Server back end, skip Access all together.
6) You can have a SQL Server back end, with an Access front-end by using linked tables. I've never personally done it, but I've seen it done. I don't like it because it seems like if you're going to go that far with it, why limit yourself with Access like that? Plus I've seen problems if the SQL Server credentials change or if something gets moved to a new server, etc.

-tg

[wengang]

Yeah I get what you're saying.
What I mean to say is:
1) I'm not allowed to make my own apps in VB or any other environment and install or run them.
2) I'm not allowed SQL Server or any other database product.
The IT dept has strict control over anything one would normally use and they won't authorize it.

So the premise is, I'm limited to Microsoft Office and everything available in MS Office, and I have to accomplish it the best way I can.
I've been reading this page:
http://office.microsoft.com/en-us/ac...010279159.aspx
And it looks like a split database is the way to go.

I just don't see yet how to secure it.

I have been reading this page from Microsoft:
http://office.microsoft.com/en-us/ac...010342026.aspx
and it seems like a compiled database would prevent a lot of security issues.
Here is an excerpt:

"To restrict changes to the front-end database that you distribute, consider saving it as a compiled binary file (an .accde file). In Access 2010, a compiled binary file is a database application file that has been saved with all the Visual Basic Access (VBA) code compiled. No VBA source code remains in an Access compiled binary file. Users cannot change the design of objects in an .accde file."

I've never tried that before, but it sounds like a good first step.
The problem, as you mentioned, is somebody finding the back-end database and deleting it or modifying it.

What can I do to prevent that or at least make it harder?