Join Adam Wilbert for an in-depth discussion in this video Password protecting a back end, part of Access 2013 Power Tips.
- View Offline
Now that our database is split into a front end and back end, we might want to take the additional step of password protecting our files to make sure that only authorized people can get in and make changes. Now because of the way Access stores absolute file pass to the back end database, where our tables are stored, I can't actually distribute a starting exercise file for this movie. So if you're following along, you'll need to continue on with the database we split in the last movie. So I've currently got the front end of our database open, and I want to apply a database password to this file. To do this, we go up to the File tab. And right here on the Info section, I've got this option here that says Encrypt with Password.
But if I try pressing that button, I'm going to get an error message, saying that I must have the database open for exclusive use to set or remove the database password. So what is that all about? Go ahead and say OK to this forwarding message. I'm going to come back up to the File tab. And in order to open up our database in exclusive use, we actually need to close it first and then reopen it through Access. So I'm going to do down to close here, that'll close my front end file, but leave Access open. Now, we need to reopen the file. Now, we might see it over here in our recent section here there's the KinetECO splitting file that we were working with, but I can't actually open it using this recent dialog box.
I need to drill a little bit deeper. I'm going to go down here to open other files at the bottom. From here, I'm going to go and find on my computer, I'm going to press Browse, and I'm going to go into my Desktop, scroll down to the Exercise Files folder and inside of that, chapter four. There is the file that is our front end, the KinetECO Splitting file. Now instead of pressing the open button or double clicking on it, what I need to do is press the downward pointing arrow on the right half of the Open button. That'll give me a little pop up menu with some additional options, and here one of the options is Open Exclusive. Let me go ahead and choose that. When you open up a database in Exclusive mode, that means nobody else can be using the database at the same time.
So if another user is using the database, it won't let you open it up exclusively. You'll have to figure out who that person is, get them to log out of the system before you can apply a password. Okay. Now the database is opened up exclusively and we can set our password. To do that, I'll go to the File tab and I'll press Encrypt with Password on the info section. Now it gives me the standard dialogue where I set, and verify a password. Here I'm going to type in the word front. And I'll go down and verify it. Front, F-R-O-N-T. Go ahead and say OK. Now when you set a database password, you're going to get this warning message that says encrypting with a block cypher is incompatible with row level locking. Row level locking will be ignored. And that, is completely fine. Go ahead and just say OK to this message any time you see it. Now our database is protected by a password, so let's go ahead and try it out. I'm going to close the database, and in Windows Explorer, I'm going to come over here and find the file in our chapter four folder and I'll double click on it to open it. Now before anything happens, it's going to ask me for a password. So I'll type in the word front. Now my database opens up and I can get to work. Now, previously we had split this database into a front end and a back-end, and we saw that the front end and the back-end were just two separate Access database files. Setting a password on the front-end does not set a password on the back end as well, so let me close the front-end file, and I'm going to go into that network folder where we stored the back-end file in the last movie. Now I'm going to open up this one, and then notice that it doesn't ask me for a password.
Anybody can get in here and open up a table and modify its structure. So let's set a separate password on this back-end file. Once again, we need to open it exclusively. So we'll go File > Close, and then I'll go to File again. Open, I'll go to my computer, I'll click the browse button, I'll Browse out to my Desktop, and find the Exercise Files folder, inside chapter four, and inside the network folder. So there's the BE file, you'll see the underscore BE that indicates that it's the back-end. Let's go ahead select it once, and then click on the downward pointing arrow on the open dialogue. Then I'll choose Open Exclusive. Now I can set a password on the back-end file. I'll go up to File and press Encrypt with Password. Now, one of the things that we can do is set a separate password that's different from the front-end. That way we can give the front-end and the back-end database to our end users, but only give them a password to the front-end, they won't have the same password for the back-end.
So, let's set the different password on the back-end. Here for the password, I'm just going to type the word back, B-A-C-K and we'll verify that B-A-C-K again and say OK. Again, we're going to get the same warning message and I'll just say OK. And now I have a separate password for the back-end. Let's go ahead and close this back-end file. Now our front-end and our back end have separate passwords applied. Let's see how that affects our linked tables. I'm going to go out of the network folder, back into just the chapter four folder and open up that front-end file again, the one that's just KinetECO-splitting. It's going to ask me for the password for the front end. I'm going to type in front and say OK and the database loads like normal.
Now, what happens if I try and open up one of the tables? Let me double click on the customer's table. It's going to tell me that it's not a valid password. When we originally linked these tables together, there was no password on the back end so there isn't a password on this linked file. Let's go ahead and reattach all the tables to the newly password protected back end. I'll go ahead and say OK to this. Now we might think that we can go back up to external data, linked table manager, and relink the files this way. So let's try that. I'll say select all, we'll say OK. And again, it's going to tell me not a valid password, so I can't do it through the Linked Table Manager.
In order to relink these tables to a password protected back-end. We actually need to delete them all first and then re-import. So, let's do that. I'll say OK. Let's go ahead and delete these tables. I'll click on the Customers table, and then shift click on the States table to select all of them. Then I'll just right-click and say Delete. It's going to warn me that it's deleting all the selected objects and I'll just say Yes, and they are gone from the front-end of the database. Now remember, these are the link files, this aren't actually the data tables that that are store din the back-end, these are just references or links to those files, so I'm not actually deleting any data here, I'm just deleting the links.
Let's go ahead and re-import those tables. Back on the external Data tab, I'm going to go in the imported links section and click on Access. Here, I'm going to browse out to my back-end file. I'll click Browse. We'll go into my Desktop, into the Exercise Files. Chapter four. And it's on that network folder. So there's the back-end. I'll select it and say Open. And this time, I'm going to make sure I change this option down here on the bottom to link to the data source by creating a linked table. I don't want to import the tables into my database. So I've chosen link data tables, go ahead and say OK. Enter the database password.
This is the password for the back-end file. And that was back, B-A-C-K. We'll go ahead and say OK. And it's going to ask me what I want to import. I want to import all of these tables, so I'll go through and I'll just Shift-click all of these. And we'll say OK. Now it imports those tables again, and I've got my linked tables back. And now, I can double-click on them to open them. And they open up just fine. But, if I only have the password to the front-end, I can get to them directly in the back-end file. So that took a few steps, where we had to close and reopen each component of the split database several times. But the end result is that each piece, the front-end and the back-end are separately protected.
Now you can give authorized people the password to the front-end of the database without them being able to get to the underlining data tables and relationships. And this way, you can compartmentalize access to sensitive information and create customized front-ends for different work groups or tasks. Let's talk about that some more in the next movie.
- Creating a kiosk experience
- Hiding the ribbon
- Creating cascading combo boxes
- Working with hidden objects
- Creating a separate back end
- Building a dynamic reporting tool
- Writing custom functions in Visual Basic
- Distributing your application