How to implement dbDenyRead (1 Viewer)

Hello. First off, wow! Thank you all for everything on Access World Forums. I have built an entire db based off your knowledge.

Issue: I have several update queries that run from an admin form to make changes to my main tables. I want to implement a dbdenyread (or something to that effect) so users cannot make changes to tables until my code is finished.

This is a multi-user split db, normalized (as best as I could), relational db.

Database:
TCS

Tables in question:
tblComplaint (Main Table)
tblAreaStreet (reference table)

Forms:
frmAdminMain (Where the code resides)

Listbox:
lstStreet (based on tblAreaStreet)

"frmAdminMain" has a textbox ("txtStreetChange") that allows the admin to make changes to the selected data on listbox "lstStreet". The admin must click save, which then runs 3 queries. The first two change all records in tblComplaint which contain the old data to match the new data. The third query changes the tblAreaStreet to match the new data in the textbox.

The code I have below works great to make the needed changes.

I am pretty sure I need to restrict access to the tables while the changes are being made. In the off chance someone were to access a record while the queries ran I think it would cause errors.

I searched and searched for the format to implement dbdenyread and now I'm asking for help. Thanks in advance!!!

Hmm, perhaps you need to look into Record Locking...
http://office.microsoft.com/en-us/access-help/recordlocks-property-HA001232785.aspx

Great info! Thank you so much for the reply. It seems like that would work well except the form is unbound and since this form is the platform to edit multiple tables (about 7) I don't see how locking the unbound form would tie up the tables. Do you think if I were to make a list of the separate forms that are actually based on the tables such as :


Not sure if that would even work, however I'll give it a try when I get back to work on Monday. Anything else that might work better to limit "Table" access instead of via form control?

I must have missed that in your initial post, Form being unbound that is No, it will not work with an unbound Form. The only other thing that comes to mind is having a field in the table that get *tagged* right before the query runs which can then display a message informing Users records will not be updated until query is complete. Then once the query has completed send another message table *good to go*.

Regarding dbDenyRead it only works with a table type recordset, i.e. dbOpenTable so your best option is dbDenyWrite which you can use with the Execute method to run your UPDATE statement.

Now the only caveat to this is that you must have exclusive access to that table before locking the table, i.e. it must not be opened by anyone through a form or through any other means and you cannot open it whilst running your UPDATE command.

So long story short, this option is only best when you're running an overnight batch or similar and you're sure that you have exclusive access to it. I guess your only option is using a flag like GinaWhipp said.

Thank you both so much! Gina in the flag method, users can still have forms open and it won't allow record changes or additions? Can you point me in the direction to learn about flagged fields in a table and how to get a message out across the board. Thank you all so much!

VBAINet - what about a check to see if I have exclusive use and once I do, lock it down until it's complete? Variations of the db will be used in different locations in our state. The admin forms would seldom be used and at most we are looking at 40 users at each location and it wouldn't be to hard to tell everyone to get off. I would even go for a force off warning and then force off. Any suggestions, I up for it. Thank you both again!!

Hmm, there is no place that comes to mind for pre written instructions. We can work on it here with you.

Since the Form is unbound you need to *push* a message to the Users because they will not know it's locked being the Form is not bound to the Table.

redneckfiveo said:

VBAINet - what about a check to see if I have exclusive use and once I do, lock it down until it's complete?

Click to expand...

You could end up in an endless loop but it's possible to implement. So here are the steps:

1. Get everyone to log off
2. Set a Timer event of a form to continuously attempt to deny write until it's successful or until how ever many tries you set it for. You can set the timer to be every 30 seconds for example.
3. The attempt to deny write will be done via the Execute method of the current db instance, something like this:
... trap the error, clear it, and retry

Once the table is updated the lock is automatically released.

Just to add my 50 cents to Gina's idea, you could create a separate table with the table name and lock state of the table. Read from the table to check the state before attempting to open, update, insert or delete from the table.