How can I make an SQL Server 2012 table programmatically read-only or writable?

[MacroScope]

I have a 25 station Access 2007/SQL Server 2012 db that has developed an annoying glitch. One particular table, called Processors, which holds name, user name, phone numbers, and other data for processors on the floor is intermittently changing the name of a processor from the correct name to another name on the list. Of course I know the table isn't doing that, but somewhere something is telling it to write over the name of person A with that of person B.

The problem is that it is extremely intermittent (sometimes a week or 10 days can go by between occurrences), and therefore it's very difficult to track down where it might be coming from. Someone tries to access their name from a list, finds it isn't there, and then I get a phone call. I thought of a work around that I believe will solve the problem quickly and effectively without hours of searching.

The only time that table is supposed to be written to is when the AddProcessor form is open. It seems a relatively simple task (for someone who knows how) to write code that makes the table writable on opening the form, and then run other code that makes it read-only again when the form is closed. If I get error messages when the problem code runs, that's fine, because it will direct me to where to look to solve it.

Can someone suggest a way to accomplish this thru VBA?

As always, thanks in advance for your help.

 

[dhookom]

All data editing should be done through forms with tables never being exposed. Forms have data properties that can be set to allow edits, deletes, adds etc.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

I agree but I don't know where this is originating and it's not happening in the normal way through a form. That's why I'm trying for this workaround. Nothing would make me happier than to see an error message that points me to where it is coming from. In the meantime, it will at least prevent names from being changed. Do you know how to change the table properties programmatically in SQL Server 2012?

 

[Andrzejek]

If it is not done thru your Form, you may want to introduce a trigger on sql server – every time the field(s) get updated, write a little text file (or some data into another small table) of when and who does that. Most of the time there will be ‘normal’ activities, but as soon as you get the phone call complaining about the issue, you can check when and who changed that field for that name.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MacroScope]

Is there anyway you know of that will allow me to do what I suggested? I want to correct the problem but if I can force the table to be read only except when the form is open that would temporarily solve my problem and probably provide me with an error message when the writing attempt to occur. That in turn would allow me to find the source of the issue.

 

[dhookom]

Rather than link to the table, use a pass-through query which doesn't allow editing. Then, use an unbound form for editing the record with some code to update the actual SQL Server table.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Thanks for the advice, Duane. I appreciate the help.