Remove access to form/data entry during db admin work? 1

[thevillageinn]

I have a rather simple database application which has a few forms and a couple of tables.
You can enter data into the main table with a form which writes the records via VBA. The main purpose of this db however is to create a file which we export from the main table to a text file and read into another application.

However for reporting and data consistency purposes I want to turn off access to data entry while the admin is exporting the records, or appending them to the archive, or whenever they desire, via the use of a checkbox, or a pushbutton on the admin form.

There will likely be anywhere from 2-6 users at any given time. The only real requirement to this "cut-off" button is that it has to be very user friendly. Things like "create a file in this directory that the DB will check for on a timer" is too difficult for the users.

Many thanks in advance.
-Dan

 

[twinmill]

Assuming you have Access2000 security set up properly, one way to do this would be to have a small Admin table accessible by just you or anyone in the Admins group. The table would have 2 fields (User_Group as string, and Edit_Rights as boolean) and 2 or more records depending on the number of other groups that access the database. Each record would include a group's name (for User_Group) and True (for Edit_Rights). (You can also use login usernames instead of groups.)

When the Admin form opens, it would set an unbound checkbox to True or False depending on the Edit_Rights in the Admin table for the User_Group. The On Click action for the checkbox could then run an update query to set edit rights for all groups (except the Admins group) to True or False depending on whether checked or not.

The buttons your users use to open the editing forms, can now check the Admin table and determine if the user can open the form for editing, or just ReadOnly by using "DoCmd.OpenForm frmEdit,,,,acFormReadOnly,,", or can set the forms acAllowEdits, acAllowDeletions, acAllowAdditions properties to Yes or No prior to opening the form.

 

[thevillageinn]

twinmill,
I like that idea. That seems like it will work for my app. I don't believe I will be allowing access if a user doesn't have security properly set up, will I?

Thank you for the suggestion. I am going to play with that option after lunch.
-Dan

 

[twinmill]

If your updates are fairly quick and you only have a few users, an easier way would be to take your users offline.

You could have two buttons to change the startup pages back and forth from your regular menu, to something like an off-line screen saying that the database will be off-line for 5 minutes. Then when no one is in the database (you can use code to check that too!), you could press the button and do your updates. Good luck!

 

[thevillageinn]

twinmill,
I was thinking about how to implement your first suggestion, and it struck me that I don't think it will work because some of the admins may be users as well, so doing groups or usernames won't work because there will likely be more than one admin (the various supervisors of a department) and the users would also include the admins. So one admin could be performing the exports, while regular users are locked out, but another admin is entering records...that wouldn't work. Probably wouldn't have many issues, but I need to eliminate that possibility all together.

Your second option sounds like it may fit my application a little better. Do you have any code examples? or any websites with code examples? I think I could use the "admin" button to also set a timer function that would exit the database in a couple of minutes, or the button could pop-up a message telling users to exit the database for maintenance.

My initial ideas for implementation of this would include a table which would be populated with a specific value when the admin has set the "Admin" button. Then the forms that are open would check for that value in that table. Then to avoid closing the database on the admin or popping up messages at them, the admin form would close all other forms that included the check for the value, eliminating the pop-ups. Am I on the right track? Maybe better than the button would be to fill in a value when the Admin screen is open, and remove it when the admin screen is closed, thereby preventing the admin from forgetting to re-set the button and keeping everyone out?

Thanks again for your ideas.
-Dan

 

[twinmill]

Using an off line menu - here's some code but you will have to modify it a little...


On your main menu form...

'Validate the user as Admin on form open...

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

If GetUser() = "MrAdmin" Then 'Function to get the system or current username
Me.btnAdminMenu.Visible = True 'Make the button visible for MrAdmin
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Description
Resure Exit_FormOpen

End Sub

'On Click event of the btnAdminMenu

Private Sub btnAdminMenu_Click() 'Use a button to open the Admin Menu

Dim strLinkCriteria, strDocName
Const DB_Text As Long = 10
Const DB_Boolean As Long = 1

strLinkCriteria = ""
strDocName = "frmAdminMenu"

'Insert code to check if required tables are in use or being edited...
'If others are editing, send out a message to have them log out of the database...
'Set a timer and keep checking for the duration of the timer...

'Exit sub if taking too long, or somehow log the users off before continuing...

Beep
ChangeProperty "StartupForm", DB_Text, "frmOffLineMenu" 'Use your Off Line Menu at startup
MsgBox "My Application is set off-line for other users!"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit_btnAdminMenu_Click:
Exit Sub

Err_btnAdminMenu_Click:
MsgBox Err.Description
Resume Exit_btnAdminMenu_Click

End Sub


For the frmAdminMenu form, set the Record Locks property to "All Records" so you can lock everyone else out of the tables you are working on. On the forms On Open event, send a message to all users that the database is closed for edits. On the forms On Close event, send another message to all users saying that the database is open again.

Hope this helps.

 

[thevillageinn]

yes, that helps quite a lot.

thank you!
-Dan