Tough issue securing database

[mvrp350]

Hello,

for our company we have an access application we use to keep track of our customer-info.

The databse consists of 3 parts:
The (replicated) frontend
The databse holding the changing data
The database holding the unchanged data (lookup db)

We have the following problem:
somewhere in our front-end db is a bug that allows users to change the contents of the lookup db. In our case this can result in a major problem because the users are able to change the city-zip code table.
I have tried to figure out where things go wrong but so far no result.

We have picked up the idea of making the lookup db read-only for normal users. This will prevent them from modifying the contents, and will also result in error messages. Hopefully will these error messages point me in the right direction of the bug.

Problem is that the ldb, created when opening the mdb, inheritates the same security settings of the mdb. If we set the mdb to read only, the user gets an error-message stating it can not find the lookup db or that the lookup mdb is locked. This is because the user can not create or modify the ldb. On the other hand, if we set the security to create and modify for the mdb, the user still is able to change the data in our lookup db.

I have never worked with the security in access itself. and I want to try to avoid that. Is there a way of securing the data in our lookup db. So I'm able to figure out what really goes wrong.

Can someone help me out on this major problem.

 

[Brian1960]

The problem lies in the lookups on your front end. It is there that you can dictate what can be edited or locked.

 

[ghudson]

You will have to use Access security and create a workgroup and define the user permissions. It also sounds like you are making a big mistake by allowing your users access to the database window which allows them open/edit/delete access to all of the objects in your db.

You state you do not want to learn and use Access security but you will have to if you want to secure your db. Search around the forum for there are thousands of postings on Access security. Ensure you understand what you are doing before you attempt to secure your db and make backup copies of your db [before and during] while you are learning Access security.

 

[mvrp350]

Hi Ghudson and Brian,

first of all thanks for the fast replies.

the users are working in a replicated db, so they are not able to create or modify objects and the access-window is not shown to the users.

The reason why I'm trying to avoid the use of the security is that we have a security system set up for the front-end that can easily be handled, and where I'm able to grant or deny permissions to users and or groups in the forms and reports.
I'm afraid that when I'm using the access secrity, our own security system needs to be rewritten, or the users have to log in twice.

mvrp350

 

[Brian1960]

I understand your reluctance to get into a rewrite of security on workgroups. It is bad enough on the first time around!

I am still unsure why you can't lock the lookups to the tables as uneditable. I have an application with workgroup securtiy which has lookups which cannot be edited by anyone other than those in a particular group.

 

[ghudson]

I am confused for your original posting at the top states that the users are changing the contents of the lookup db and you want to prevent them from modifying the contents. If the user can not access the db objects then how can they modify the data or the design of the objects?

You can open a form in a read-only state so the they can not modify the data displayed in the form. Or you can set the data permissions to the form to not allow the user to add/edit/delete the data in the forms record source. I prefer to use something like this in the forms OnOpen event...

Me.AllowAdditions = False
Me.AllowDeletions = False
Me.AllowEdits = False

 

[mvrp350]

I am confused for your original posting at the top states that the users are changing the contents of the lookup db and you want to prevent them from modifying the contents. If the user can not access the db objects then how can they modify the data or the design of the objects?

The users are not able to access the content of the db directly, this is all done through forms or reports i.e. a user cannot see the tables used.
The only way they can access the data is through the forms and reports.

The problem arrises in the forms.
Say:
We have a new customer.
That customer lives in a place with zip code 12345

in our lookup db the location assiciated with zip-code 12345 is new york
on another location is the zip-code 67890 that stands for washington.

the user enters the data, including the zip code. In the form the location is automiticly lookud up and filled in, so this should give new york.

Sometimes, and I don't know when this occurs, a user accidentally, without him/her knowing, changes the location associated with 12345 to washington.

In all our written communication with the customer we use a lookup for the zip-code, so instead of the mail going to new york, it goes to washington.

Brian, can you give me an idea on how to do this. I've looked around, but I didn't find an property that sets a table to 'uneditable'.

Thanks,
mvrp350

 

[neileg]

You don't need to do this in the table. If the edits are happening in the form, set the properties for the control that is bound to Location to Locked. This way the user can't change the location data.

If you might need to enter a location when you don't have the zip, create a button that opens a pop up form to capture the location but only if the zip code control is null.

 

[Brian1960]

I have never tried this but it should do what you want. neileg is correct in both his last comments.
In the lookup.
Rowsource "Select zip, district from tblLookupZips"
Column Widths 1.5cm;0cm
Bound Column 1

On the Before Update event

District.SetFocus
District.Locked = False
District.Text = Me.ZipLookup.Column(2)
District.Locked = True

The syntax of the Me.ZipLookup.Column(2) may be wrong but it is similar to this and the columns may be 0&1 not 1&2 but you get the idea.

 

[Brian1960]

I shoulsd have said the before update event is on the lookup control not the District!

 

[mvrp350]

Thanks guys,

I'll try to make it work using your solutions.

thanks again,
mvrp350