Need to secure a database, its forms and reports 2

[Sherman6789]

We have a Microsoft Access Database which is saved in a shared drive. Until now, we have not had a problem with many people having access to the database or the data. Beginning immediately, several people will be using the database and we want to be sure that they are not allowed to open and modify forms, tables and reports without permission. We are using version 7 of Windows and Access. I try to keep the list of items on the left side and top of the screen closed as much as possible. The default seems to allow excessive access to most things most of the time. We want people to search for information but not be able to change it. We also want to discourage people from playing with the data and the program. I think that I must learn about security but I don't have much time to get it operational. I might have to learn about front ends and back ends. Do you have any suggestions that we can do immediately?

 

[MajP]

I might have to learn about front ends and back ends.

NOPE! You have to! Anything else is a waste of time. Backend goes on the network, and every user has their own front end. Spend your time and google it. Once you are done splitting your database and understanding that then come back.

 

[dhookom]

You might want to read through Security FAQs in Access Other Topics.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

http://lmgtfy.com/?q=How+to+split+and+access+database

 

[Sherman6789]

Thank you for your prompt responses.

dhookom: I am looking over your information now. Thanks.

MajP: I read the link that you listed and I see that I probably need to split the database. However, I have three major problems.

1) About 30 people are already using the database. I must add about 10 more within the next 48 hours.

2) It seems that to split the database, the tables are kept on the server but all of the forms, reports, etc. are saved on each individual computer. Anytime a change is made to a form, report, etc. it has to be sent to each computer. As of now, the users can enter data at the same time because they work on different areas of the database. They are able to access any of the forms at the same time. We will have problems trying to update each computer every time a change is made to a form, etc. When I make a change to the forms or reports, I can do it at one time and everyone is updated immediately. I make design changes after hours when they are all off the system.

3) I don't know anything about front ends and back ends and it will take time for me to learn. I will enroll in a class and study it plus do a lot of reading but we have to let the users work on the database immediately. I don't have any assistance because the people to work on databases here seem to know only the basics and I have no one to talk to about technical stuff therefore I write questions to organization like Tek-Tips.

Thanks for your suggestions.

 

[MajP]

There are several FAQs on how to distribute the most current front end using a batch program.

If you do not split your database. It will get real slow, then it will get corrupted, then it will be unuseable. And if I hear the urgency then you will get fired, end up homeless, and living in a trailer down by the river.

40 people logging into the same database, it just a disaster awaiting to happen.

If you want a second opinion I ask for you.
------------------------------------------------------
Is there anyone on this site that thinks it is not imperative to split the database with each user having their own front end?

 

[MajP]

Here is one, I have not used it, but hopefully some of the MVPs can recommend what they use.

http://www.autofeupdater.com/index.htm

 

[MajP]

http://www.access-experts.com/default.aspx?selection=TutorialSplitDB&sm=18

 

[Sherman6789]

Thanks MajP. The 30 or more people currently have access to the database but only a few will be on the DB at the same time. They all have other work to do also. I doubt if more that 4 or 5 will be actively on the database at any given time.

I backup the database every two days.

Most people that I talk with have no idea of what a frontend or a backend is. Most do not work on the design of a database they just use it when necessary. I have not been able to find anyone who says that it is very necessary to split the database. I have no problem with that as a goal, but I need to have the users to be able to use the system immediately while I learn more for the future.

So far it does not seem to be slow. It might in the near future but not now. I will learn about setting up front and back ends plus the methods of securing the files and forms for better control.

We have used MS Access databases in the past and present on a shared drive with multiple users. We have not had any corruption of the data and no one have been subject to firing, homelessness or the need to live in a trailer on the river.

I will now look over the links that you have provided.

Thanks.

 

[MajP]

Wow, I do not know what to say. You seem committed, have lots of excuses, and seem to know better. I am not just making this up, I do have knowledge and experience in this area. Good luck. You are warned.

 

[Sherman6789]

MajP: I am sorry that you have interpreted my comments in that negative manner. I said that I have no trouble making plans to make a front and back end to this database and probably to the others as well. I am not committed to keeping it set up the way it is. Time is the problem that I have right "now". I know that I can not learn what is necessary to do to make that change in the next 48-72 hours and must do something "now" which is based on "old fashioned" procedures and knowledge. I am not trying to make excuses for not doing it. I don't pretend to "know better". I do not believe that you are making this up. I am convenced and have been from the time I read about spliting the database. With your knowledge and experience, it may be easier for you to declare that this change must be made "immediately"; but if you were in the situation where I am and it will take a little time for you to learn how to make the changes, run tests, make copies, etc. you might have to come up with a "temporary" solution to use immediately with the plans and expectation of other changes in the near future. If you can think of anything that we can do immediately I would greatly appreciate it. Take note that at the moment, I have little or no help. However, it will be changed as you suggest, in the near future.

Thanks.

 

[MajP]

You can make the db into a mde/accde.

http://www.dummies.com/how-to/content/securing-your-access-2003-database-as-an-mde-file.html

 

[Sherman6789]

Thanks MajP

This link seems to be headed in the direction that I need for immediate use. I will study it tonight. Again, the database will be changed to front-end/back-end very soon. I am studying the procedures now.

I will keep this thread open a little longer to see if any other suggestions come in.

Thanks again.

 

[MajP]

Making it an accde is a start. You should read up on those other FAQs. There are a lot of additional strategies depending on what you want to do, and how locked down you really need it. You can hide the navigation window, control the ribbons, lock the form add/edit/delete, etc.

However, once you make it an accde it can not be edited by you. You need to maintain the .accdb for editing. This is another reason for splitting the db. Once split you can give out new accde and never worry about having to take the db offline to update.