General :: Securing A Database / Can't Edit Forms / Reports / Queries
Jan 21, 2014
I'm trying to secure my database so users can't edit tables, forms, reports, queries, etc.I'm splitting the database, making an ACCDE for users:
1. I inserted code to disable the bypass key.
2. I inserted code to hide the Quick Access Toolbar (QAT) in the On_Load sub of the form that opens with the DB.
3. Deselect Navigation Pane, Allow Full Menus and Allow Default Shortcut Menus are deselected
4. Then, I use the immediate window to show the QAT, I then create an ACCDE.
How do I link this ACCDE with the original ACCDB? Am I supposed to delete tables from the front end and link the forms/reports to the back end DB?
View Replies
ADVERTISEMENT
Mar 24, 2014
I'd like to secure my frontend by disabling access to the linked tables, queries etc. I thought converting to .accde would do this but apparently not. Is there a way that this can be achieved?
I am aware that I can disbale the ribbons etc but they can all be turned back on using the options menu.
View 3 Replies
View Related
Aug 28, 2007
Hi
I have just put together a small database, and have used the user-level security wizzard to set up groups so i can restrict access to the database.
I have also been through all the user and group permissions and tried to set the security up so that the data entry staff can enter data but not change any of the forms or queries.
This is where i am hitting the wall.... although i have set up this protection, when i access the database under the staff name and password I am still able to view the forms and queries in design view and change them.
Can someone plaese point out where I am going wrong
Thanks very much :)
View 5 Replies
View Related
Sep 15, 2011
I am used to old Access 97 where I could edit a report while others are in it. I created an Access 2007 db and it does not let me edit unless I have exclusive rights to the file. Is there a way to turn that functionality on?(editing reports/queries/ while others are in the database. ?
View 6 Replies
View Related
Jul 24, 2007
I have two questions. All of my experience has been in Access 2003. My work just upgraded to 2007. I am ready to secure a database that I have now converted to a 2007.
1. Is it possible to set the db to open as read only for everyone on staff except for a few people?
2. How do I set security levels where some can view all tables and others can only view specific tables?
For example, I only want 2 people to be able to modify any data. And I don't want anyone to view the accounts receivables, but they need to see other data on the customers.
Thanks in advance.
View 2 Replies
View Related
Dec 18, 2003
All,
As I promised earlier, here is my suggestion to secure an MS-Access database. The explanation below is applicable for MS-Access 2000 and XP versions, I haven't tested it on Access 2003.
This way of securing a database is a bit different from the one I posted earlier, but more secure.
Disclaimer:
This explanation of securing a database is based on MY experineces. I mean I've been doing it in this way, and it worked OK for me.
I DO NOT say that there is no other method of securing a database.
I will NOT take responsibility for any problems caused by securing your databases either in this or in any other way.
Use this guide on your own risk.
Pre-reading notice:
Menu paths and commands may be different in the English version of MS-Access. I use Hungarian MS-Access, but I am doing my best to remember (or translate to) the English menus and commands.
Where I'm in doubt about the exact English menu path or command and there are more than one possible translations, I enclose the alternative between parentheses.
Menu paths are indicated like this: File -> Get external data -> Import...
Command buttons are indicated like this: <OK>
To secure a database:
1. close the database you want to secure.
BEFORE closing it:
- If a database password is set, then clear it.
- If the VB Project is protected then unprotect it. To unprotect the VB Project go to Tools -> [project_name] options... in VB Editor and click the Protection tab. Remove the tick from the 'Lock Project from viewing' check box and clear the password.
2. CREATE a new blank database;
3. Check and make a note of the path of the default Workgroup Information File (.mdw).
- to do this, go to Tools -> Security -> Workgroup Administrator, and read the file path indicated.
4. create a new .mdw file using the Workgroup Administrator, and stay joined to it.
- to create a new Workgroup Information File go to Tools -> Security -> Workgroup Administrator, and click <Create...>. Provide the required information, click <OK>, click <Browse...>, and type a name.
- I usually use the name of my database which I want to secure.
- Note: if you wish your .mdw file to be unique, then you need to provide a workgroup code.
- If you provide a workgroup code, it's strongly recommended to make a note of and keep it in a safe place. Should the the .mdw file be lost, you will need this information to create the "same" .mdw file.
5. create a User with your logon name and put this user to the group called 'Admins';
- to create a new user go to Tools -> Security -> User and Group accounts... Click <New...> on the Users tab. Type a logon name, and a PID.
- Note: users are identified by their PID, not by their logon name. Anytime you create a user, it's strongly recommended to make a note of the PID and keep it in a safe place. Should the the .mdw file be lost, you will need the PIDs to create the same users in a new .mdw file.
- It's enough to create a user account for yourself. You can create the other users' after the whole process.
- Don't forget to put yourself into the 'Admins' group.
6. Create a password for the default Admin user (which you are at the moment).
- To enforce users to provide a logon name and a password, you need to set a password for the default Admin user. If the default Admin user has a blank password (in other words: no password set for it), then Access automatically starts with the default Admin user, and does not require user authentication.
- To create a password for the default Admin user, go to Tools -> Security -> User and Group accounts... and select the Change Password tab. Leave the Current Password blank, then type & confirm the new password.
- The default Admin user does not need a difficult password, because this user will not have any permission on the secured database at the end. We need the password only to enforce user authentication.
7. Close the database.
8. CREATE an OTHER new blank database.
- If a password has been set for the default Admin user, it will prompt for a password.
- Delete the "Admin" and type the user name you just created. NO PASSWORD REQUIRED for this user, so just click <OK>.
- From now on, you are logged on as the user that you've just created. Note: if you haven't put this user into the Admins group, you won't be able to continue.
- IMPORTANT NOTICE: the reason of creating an other blank database instead of just doing the whole process WITHIN the database to be secured, is that the only way to take the ownership of a 'Database' object is to create it as YOU, and not as the default Admin user. As I experineced, 'Database' ownership CANNOT BE TAKEN AWAY from the creator of the 'Database' object.
9. Go to Tools -> Security -> User and Group accounts... and REMOVE the user 'Admin' from the 'Admins' group.
- Clck Users tab, and select 'Admin' from the drop-down list. Select 'Admins' in the list box 'Member' ('Member of'), and click <Remove>.
- If you haven't added the new user to the Admins group, then you won't be able to remove the default Admin user from the Admins group because Access will not let you to have no users in the Admins group. At least one user must be in Admins group.
- If you can remove the default Admin user from the Admins group, then, from now on, you are the only user with administrative permissions for this database and in this .mdw file.
10. Set a password for yourself.
- How? See point 6.
- Memorize your password, because it can only be retrieved by using certain Password Recovery processes and softwares. As far as I know, there is NO WAY to retrieve a password via VBA or MS-Access for a normal user, it needs someone with deep hacking knowledge...
11. Import all objects from your database that you want to secure.
- Go to File -> Get external data -> Import..., and browse to your database.
- Select ALL objects: tables, queries, forms, reports, macros and modules, and click <Import>.
- If you haven't unprotect the VB Project, it WILL NOT import any VB code even behind the forms.
- From now on, the owner of the 'Database' object and all tables, queries etc. is YOU.
12. Run User Level Security wizard and make the permission settings.
- It's recommended to select all objects and grant only data modification rights to the 'Users' group.
- After running the wizard, it's recommended to restrict all rights of the default Admin user manually. This will ensure that if your database is opened with the default system.mdw file (which is automatically created when MS-Access is being installed), then the default Admin user will not have any permission to any data or object.
13. Join to the default Workgroup Information File, and close the database.
- to do this, go to Tools -> Security -> Workgroup Administrator, and click <Join...>, click <Browse...> and navigate to the default .mdw file you've hopefully made a note of.
After this process, if you just open your secured database then you will open it as the default Admin user. It will not require a password, because in the default .mdw file, the Admin user does not have a password. And, if you've restricted all permissions of the deafault Admin user, you won't be able to make any data or design modifications, and even open any objects.
The reason of it is that the default Admin user's PID is the same in all .mdw files. The default Admin user is automatically created when you create a new .mdw file. The default Admin user also CANNOT BE deleted. The default Admin user uses the same PID everywhere, so regardless of how many .mdw files you have on your system, Admin user logically is the same for all databases.
So then how to open your secured database?
You need to use the .mdw file you created. So open your database with the /wrkgrp switcher. Create a shortcut and use this in the Target field:
[drive]:[database_path][database_file] /wrkgrp [drive]:[mdw_file_path][mdw_file]
Example:
"C:MyDatabasesMyDatabase.mdb" /wrkgrp "C:MyDatabasesMyDatabase.mdw"
If you open your secured database in this way, then it will require a password (of course, because the default Admin user in your .mdw file has a password). Log on as yourself, and you now can add other users.
NOTE VERY CAREFULLY:
1. BEFORE starting the whole process ALWAYS make at least one backup copy of your unsecured database.
2. NEVER delete this backup copy UNLESS you're ABSOLUTELY SURE that everything is approved and tested and IS WORKING OK in your secured database.
3. DO NOT LET anyone to make any changes in data or design in your backup copy until you're absolutely sure that everything is working OK. Otherwise your backup copy will not be anymore a clone of your database.
Other notice:
I've been blocked out from my own databases many times while I was searching for a right way of securing. Unfortunately, HELP provides a likely poor aid.
If you encounter problems, I'll do my best to help you as my time allows me to.
I kindly ask the members that feel themselves more or less experienced to make a test and post any notes/suggestions/bugs/mistakes to this thread, PLEASE.
REMEMBER: IF YOU HAVE A BACKUP COPY YOU WON'T GET TROUBLE.
View 14 Replies
View Related
May 27, 2014
I have a split database made in Access 2007. Each user gets their own copy of the frontend from a script. I wanted to be able to edit the design view of the backend tables even if people were using the database so I made all the forms use snapshot source and only allowed data updates through VBA macro update queries. Having any form open locks the backend source table from being edited. In fact, I've found that just having a normal snapshot query open causes the message "Either an object bound to table 'whatever' is open or another user has the table open. Do you want to open the table as read-only?"
Is there some way to have a table be the source for a form or query, but still have it designable under most circumstances?
Attempted to late-bind a recordset on form load; result was the same:
Code:
Set rs = CurrentDb.OpenRecordset("Select redacted as ft from tblRedacted ", dbOpenSnapshot, dbReadOnly)
Set Me.Recordset = rs
Set rs = Nothing
View 3 Replies
View Related
Nov 7, 2005
This is my 2nd thread on the topic...I've searched the forums....
I have a database. It is on the shared folder on the server. I ran the security wizard and created a shortcut. On my machine it works perfectly...asks for a login, has a couple user groups etc...
When I run it off the server though, the shortcut doesn't work....refers to files on my machine?? and the database is unsecured.
Do I have to run the security wizard on every machine? Do I need to run the security wizard from the server? Or do I even need to run the security wizard at all??? My 2-day access course is failing me miserably...
View 5 Replies
View Related
Jul 17, 2007
Have created a database that will be placed on a shared drive, giving access to others to add, delete, modify records. That aspect of it is fine -- but how to I prevent someone from deleting a field?
View 2 Replies
View Related
Aug 23, 2005
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.
View 10 Replies
View Related
Feb 7, 2006
I've been fiddling around with the Tools>>Security settings but I can't seem to find a way where only I can make changes to the database and the users can't just use the switchboard. If you are regular user you don't need password but if you are admin you do.
How do I do that?
View 1 Replies
View Related
Sep 22, 2013
Is there any systematic way of naming Tables, Queries, Forms, and Reports in order to identify easily which of them are connected to each other?
View 1 Replies
View Related
Jun 16, 2006
I don't have the database finished so I can't really comment on the structure. However, I know the database will be used by multiple people, very likely at the same time. I've heard it's a good idea to split the mdb into a frontend and a backend so I've already started that process. I have no idea how this would fit into a security scheme though. Do I have to set up user-level security on both ends? Without actually implementing it I kind of think it would be secure if the backend had a master password and the frontend had user-level access. What do you all think?
View 1 Replies
View Related
Oct 14, 2005
ghudson:
I am experiencing a problem with the mousetrap sample after I secured my database.
When I save on my main form and I try to go to my subform I keep getting the "Please Save this Record! You can not advance to another record until you either 'Save' the changes made to this record or 'Undo' your changed."
I have saved but it is still preventing me from going to the my subform. I numbered the Save Required msgs so that I know which one I am getting and I am getting the one from:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
Me.tbHidden.SetFocus
If Me.tbProperSave.Value = "No" Then
Beep
MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance to another record until you either 'Save' the changes made to this record or 'Undo' your changes.7", vbExclamation, "Save Required"
DoCmd.CancelEvent
Exit Sub
End If
Exit_Form_BeforeUpdate:
Exit Sub
Err_Form_BeforeUpdate:
If Err = 3020 Then 'Update or CancelUpdate without AddNew or Edit
Exit Sub
Else
MsgBox Err.Number, Err.Description
Resume Exit_Form_BeforeUpdate
End If
End Sub
Above it is checking if tbProperSave.Value = "No" and in bSave you have
Case vbYes: 'Save the changes
Me.tbProperSave.Value = "Yes"
DoCmd.RunCommand acCmdSaveRecord
Me.tbProperSave.Value = "No"
So it's setting the value to no again?
I know it's not a permissions thing because I am admin and have full permissions on the forms.
Any suggestions?
View 7 Replies
View Related
Dec 3, 2012
I have an application that is used by individual teachers to generate reports for central admin. It is not secured except I have locked out access to all objects, Navigation pane is hidden, etc to protect the integrity of the tool. I must also maintain this as changes are propagated. I am trying to find a way to upgrade forms, functions, queries, etc without manually having to unlock and relock every db for each school site when changes are required. I have tried importing and exporting from a master db, turning objects on and off by recognizing my password, splitting code and data (db's are on flash drives and path changes every time they are inserted, many teachers cannot handle refreshing table links), I have tried writing code to import the changes at next startup, etc. It is written on Access 2003.
View 3 Replies
View Related
Dec 9, 2013
I have a split db with tables in the back end and my forms, reports, code etc. in the front end.
I encrypted the back end with a password. That worked fine.
I deleted and relinked my tables to the encrypted back end. That worked fine as well.
I have a function that will disable the shift key bypass. If I run that in my front end db then I can't save it as an accde because I can't get to the HOME screen. If I save it as an accde first then I can't run my 'disable shift key bypass' function because I can't get to the modules!
How can I secure a split Access 2010 database so that the user cannot execute shift bypass on the front end or make any changes to the code?
View 7 Replies
View Related
Mar 6, 2015
Is it possible for me to give them access to part or all of the database so that they can run queries to target venue mailshots etc if so how would i do this?Secondly my concern is that the database is valuable and I want to protect myself from potential theft of info, ie what stops them taking the database and using it for there own purposes?
View 2 Replies
View Related
Apr 29, 2006
On a webserver certian files are stored above the root directory. Therefor the user of the website can never access those files directly. But he is utlizing that file.
For example:
We use Hotmail to see our mails. but we never download the complete database.
So is there a method that we can secure the backend using roaming profiles or something else so the user has a link to the backend but cant directly access the backend.
View 2 Replies
View Related
Jul 9, 2014
Our office needs a way to track reports that are due to us, so I'm trying to build an Access database to do that. What happens is this...
We put out a weekly tasking document (called an AFCTO) every Friday that tasks our outside agencies (units) to do various things. Each task in the AFCTO directs a single unit to do a specific thing. Units may be tasked multiple times in the AFCTO (one-to-many relationship), but each task only applies to one unit.
Some tasks require the units to send us reports on the status of that task, while other tasks don't. The reports that are due can occur at different frequencies. For instance, some tasks require our units to send us reports weekly on Thursdays; other tasks may require reports to be sent to us monthly on the 1st; other tasks may require daily reporting.
Now, with all that said, we need a way to see what's due to us each day. What I would like is a report that displays what's due for this week, similar to this:
So far I have a very rough mockup of what the form should look like (fmAFCTOTasks in the attached db):
The user can type in the AFCTO Task Number of the task, the start and end datetime group, the unit assigned to that task, the task desc, what type of report is due, what triggers the report, and the frequency at which the report is due. Reports can have one of two triggers...
1) event driven (something happens that requires a report to be due), or
2) date driven (report is due on certain date or day(s)).
Obviously if a report is event-driven, then there will be no frequency or date/day associated with it. How to structure the tables and the form.
View 1 Replies
View Related
Apr 21, 2014
I have a database of high-school football players, and I am looking to print out single page reports (or forms) that will show detail from several tables and queries. This will act as their resume when they visit schools on recruiting visits. The reason for needing query items, is that I have developed queries that return the most up to date height, weight, 40 time etc., and that single most up to date number is what should print, not the entire table. When I try to build a report it will let me bring in multiple tables, but not queries.
View 2 Replies
View Related
Jun 24, 2014
I have a report database that provides my company with clients that took our training modules and notify us of which clients completed our trainings.The clients can complete training in 3 States and "Passed" means they are good to go.
I download an excel report daily and import it to Access on a daily basis. Problem is the Report is over 8,000 rows long and basically I just need the clients that completed training within past 48 hours. The excel report provides a date of completion.
code that only pulls those clients that "Passed" within the last 48 Hours. Here is my SQL Statement I use on the RecordSource.
SELECT report.SPS, report.FirstName, report.region, report.id, report.AZ_Cert, report.AZStatus, report.CA_CERT, report.CAStatus, report.OR_CERT, report.ORStatus, report.Completed FROM report WHERE (((report.Completed)=False));
The completed checkbox removes the record from the cert queue. How can I do this more efficiently? I think I have it right.
Private Sub Completed_Click()
Const cstrPrompt As String = _
"Are you sure you want to complete this record? Yes/No"
If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
If Me.Dirty Then
Me.Dirty = False ' save the record
Forms!frmRecertView.subfrmRecert.Requery
End If
End If
End Sub
View 6 Replies
View Related
Nov 29, 2012
Whenever I create a new query or report, it automatically becomes Hidden, and the only way that I can see it (still as hidden) is to close out of Access and then go back in.
View 2 Replies
View Related
Jul 8, 2015
I need to know if possible to create multiple queries or reports at the same time. We have large table that is updated monthly this report gets broken down manually by Manager (30 managers in total) hen email to each manager.
Report or query specs will never change only the data I could do this manually create and save query/report for each manager once but I was wondering if there was code that will create all the queries and reports at the same time.
View 1 Replies
View Related
Feb 8, 2006
I would like to edit a MS Database structure from a Web Browser using ASP. What I mean is I would like to be able to add more columns to a database table or delete or rename...Not the data that is already in the columns... Is this possible? If so can some one please point me in the right direction. I've searched these forums and have not been able to find the answer.
View 2 Replies
View Related
Jul 27, 2013
I have an A2007 database used for time and billing. As time goes on I've had to add more forms and especially more reports. I feel the because of all these "additions" the FE is getting a bit bloated, something like 2.2MB right now.What I want to inquire about is the possibility of moving at least some (if not all) of the forms and reports to another FE and can this be done without having to move tables and/or queries?
The navigation pane is "hidden" from the users so they don't see all the tables, queries, forms and reports but some are smart enough to figure out to "unhide" the navigation pane.Concerning the forms; there are certainly some forms that I do not want other users to open out of curiosity, or for whatever other reasons they might have, so these I would want to move. The same basic reasoning would apply to the reports.
My thinking would be this; move a particular form/report to a separate FE that merely acts as a "bucket" to store the form/report. Clicking on the control in the main FE would open the form/report stored in the other FE using the tables and queries in the main FE.can it be done without having to re-write a bunch of code? I know I can add code so certain controls aren't visible to certain users but I've not found a way to permanently lock and hide the navigation pane.
View 4 Replies
View Related
Oct 15, 2014
I've attached my database as I have it now. I am trying to create what amounts to an activity log.
I have created my main Subject and underlying related Issues tables, and a table for my daily activity notes. I have created my main form and subforms that should be sufficient (hopefully) for my purpose. Where I am getting hung up now is making the Subject and Issues fields combo boxes and filling them.
For the top-level Subject, I want to be able to type a new one in at any time, but also have the option to choose from a dropdown on the control as well. Then, if I do type one in, the next time I try to add a new record, it would appear in the dropdown as well. I think I have to query the underlying field to drive the dropdown list, but I cannot remember how to do that.
For the second-level Issue, there would also be a dropdown in the same way as the Subject control, and I want to be able to choose from the dropdown or add a new one in the same way... but I need to make sure that the ones that appear in the dropdown are related to the top-level Subject shown in the main part of the form. Of course, I don't want to be able to add an Issue unless it is related to a parent Subject. On these things, I am totally lost.
Finally, I built my notes to show in the subform as a continuous form, which I have used only once before. I'd like it so that the notes show newest at the top. And, I'm thinking about putting a "lock" checkbox on the note record so that I don't overwrite previous notes unless I consciously go back to edit something. I'm not sure how I can make an individual note dependent on an individual checkbox in the continuous form.
View 1 Replies
View Related
