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?
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?
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?
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.
I have had this in another Access forum ... without a resolution. I am pulling out what little hair I have left, so hopefully someone can help me.
I have created a program using MS Access that a small business will use to enter service contracts and invoices. The program was created in Access 2000, but converted nicely to Access 2007 which resides on the 3 brand new Dell computers that the business bought.
I did some research and determined that since 3 people would possibly be using the database at once, I needed to split the database. I did so using the wizard contained in Access. One of the computers acts as the "server" and houses the back-end of the database (in a "Public" folder that is shared to anyone on the network ... with all rights). All three computers have the front-end (forms, queries, reports, etc.) The front-ends all point to the shared "Public" folder on the "server" computer.
In each of the computers, the program will open and work properly. The problem comes when trying to access any of the input forms (that get data from the back-end). Only 2 (and sometimes 1) of the computers will open the form at the same time. If 2 are currently open and I try to open the third, it will sit and crank away as if it cannot find the file. It will not open. If I close one of the other forms, the third will open immediately with no hesitation.
At first I thought it could be a network bandwidth problem, but all are running at 50 - 100 Mbs and shouldn't have a problem opening a simple database file. I can't find anywhere that says Access would limit the number of users to 2; everywhere I look says that a split database should allow up to 10 users at once.
Again, the program works fine ... I just can't get it to open by 3 concurrent users.
I have a "client/server" split Access 2003 database with an Interface file containing all the code/forms/queries and some system tables and also a Data Backend file, with all the data tables and table structure.
This is being deployed to multiple sites, each getting their own version of the backend, so sites will not be using the same backend between them all, rather multiple users in each site will all share that site's backend file.
Since users will deploy in different network locations, etc. they will have to re-link all tables. Is there a meaningful/user-friendly way of doing that without relying on user's knowledge of "Linked Table manager", etc.?
I have a 2007 back-end db on a network share and around 15 regular users who access it through their own front-end db/interface. One user on Windows 7 is able to launch and use his front-end copy fine except when he goes to launch one form for data entry.
This form clocks and freezes the entire application. I sat with him and was able to determine that the issue is that the form_load code is never running. He clicks on the button to launch the form (done in vba) and the code never runs. If he opens the form in design view first then changes to form view, the code runs fine and there are no issues. This is his present work-around, but I had to give him an unlocked FE copy to pull it off (not ideal!).
I tried this with him on different PCs, with different front-end copies, and even in the back-end copy. Same result each time. None of the other users have this issue, and he can replicate this issue on different PCs. I'm at a loss for what might be causing this since it really seems so user-specific.
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.
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:
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.
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...
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?
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.
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.
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.
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?
I have a multi user database in Access 2000 that is on a server. The individual users have shortcuts pointing towards the server instance. One user and one user only when opening the shortcut gets the error "you do not have exclusive rights to the database" .
All other users can enter the database with no error box. I have checked the advanced setting under options and they are correct. Ironically if you go to start and open access then navigate to the database shortcut. It opens with no error.
:confused: I have read a quite a few threads on spiting the database. My database is 50 Meg and running very slow. I have compacted and repair, still the same. This just happen all of sudden. Could that be possible?. What should I look out before I try to spit it?. It’s given that I will make a backup. I am running Access 2003. I have over 150 users.
I am almost at the finishing line with a great deal of help from here. I have now normalised the tables, built complicated queries, forms, reports, all from scratch as I knew absolutely nothing before I started with Access three months ago. I have compacted and repaired the database using the wizard, and also complied the code and analyzed all tables, forms, queries, and reports and relationships. The final step is to make it an MDE file so that it can go on the local network at work (not on the internet). My questions are these;
Do I need to split the database into front and back ends?
If I save a copy of the original MDB file and then make it a MDE file would that not suffice in making it run more efficiently?
Can a split MDB database be made into an MDE database?
I have a database that resides on a file server share. I have split the database to create a backend. I then posted a shortcut of the front end to the desktops of two users who have access to the share. I then tried to access the front end from the two machines simultaneously. It opened on the first PC but not the second? When I viewed the server share it had a 'padlock' icon?
Where am I going wrong? This is the first time I have attempted this scenario!! Thanks in advance, Phil
Should splitting only be done when all tables,queries,forms,reports, etc. are done. Or can it be done anytime. I am almost to that stage but didnt want to jump the gun and run into problems later on.
I was having a discussion with Rickster57 concerning my newly developed database, and he recommended that I split it so that it has a front end and a back end. Rick listed a number of very good reasons for doing this (so I will definitely do it). But I wanted to pose the question so I could get some of the reasons the more seasoned Access programmers have for creating a front and back end to their programs.
I recently split my database and also used Bob Larson's Autoupdating tool. Everything appeared to be working fine until a few users were unable to access the db. I later found out that because they are at a different site, they don't map to the server that houses the database BE. However, even if they manually map to the correct server, they are unable to bring up the db Form. They get an error stating the BE "...is not a valid path". My questions are:
1) If I were to copy all the files (the Master FE, BE, and MDE) to a public folder that ALL users have access to, will everyone be able to bring up the database Form?
2) How would this affect what was already set up when I ran Bob Larson's utility (or even the splitting and MDE creation)?
3) Is there a way to "Undo" what was previously "Done" to this database (ie: Splitting, Larson Utility, MDE creation) in order to correct the issue, or do I need to start from scratch and split the database while it's on a Public drive.
I hope this makes sense. Thanks in advance for your help.
I just want to know that " How to split the Access database in Front End and Back End application"? I am creating a database that i want to store on server and simultaneously want to give access to 20 terminals (cleints) to access the database.
Secondly, I have to provide a field in a form "Document number". However as per the requirement user can enter "PIR No", "Serial No" or at times both as document number. Both the things "PIR No" and "Serial No" have different format types like "PIR No 001" and "Srl No001".
On basis of document number later i have to give the option to search the particular document. Please guide me how to provide the option to enter the field values.
I am almost at the finishing line with a great deal of help from here. I have now normalised the tables, built complicated queries, forms, reports, all from scratch as I knew absolutely nothing before I started with Access three months ago. I have compacted and repaired the database using the wizard, and also complied the code and analyzed all tables, forms, queries, and reports and relationships. The final step is to make it an MDE file so that it can go on the local network at work (not on the internet). My questions are these;
Do I need to split the database into front and back ends?
If I save a copy of the original MDB file and then make it a MDE file would that not suffice in making it run more efficiently?
Can a split MDB database be made into an MDE database?
I am combining 12 Databases. I have split all 12 between the server and the desktop. My question is “Should I combing all of the data table on the server into one database or should I leave all of the individual application data tables in separate Databases on the server”. Additionally, is there a limit to the number of tables an Access DB can handle? My inclination is to keep the functionality separated but the problem I have is that some of the functionality within the applications overlap. Recommendations!
The moment of truth finally came and I split my database FE/BE for testing only the truth was a bit sour.
Background: I have several forms in my database that have a common series of buttons at the top which open their corresponding form. Before the split, everything was fine - you can click on the button and the desired form would open. After the split, I'm getting the error message:
"Microsoft Office Access couldn't find the toolbar 'EVM Database Menu'."
This is referencing a toolbar that I used during development to help jump to forms.
I tried removing the toolbar from each form (Tools > Customize > Toolbars > unchecked the custom toolbar), and re-split the database FE/BE ends, but to no avail..
So, I checked the event codes to be sure I didn't program something wrong. There are 9 buttons total to open forms, 7 of them give this problem, the remaining 2 do not. The code for the problematic and non-problematic ones are identical!
Example - Non-Working (error message re: Custom Toolbar): ----------------------------------------------------------- Private Sub cmdActuals_Click() On Error GoTo Err_cmdActuals_Click
Dim stDocName As String Dim stLinkCriteria As String
Example - Working (no error message): ----------------------------------------------------------- Private Sub cmdPerfProj_Click() On Error GoTo Err_cmdPerfProj_Click
Dim stDocName As String Dim stLinkCriteria As String
Questions: So..this has nothing to do with code, it must be a problem in the linking somehow, right? A bug possibly? What else could be causing this? More importantly, is there a solution to my predicament that anyone could help out with? Searches so far have returned no help either from forums (including here), MS site (no Q-articles), and VBA Help. I'll continue searching though.
