Access 2019 - Splitting the Database into Backend and User Interface

welcome to another video in the series about access 2019 in office 365. and the last video i made was about adding password protection to your database what we're going to be looking at today is splitting the database into two parts one part being the data um in the back end so that would be just the tables and then the other part being the front end would be the user interface the forms the reports and the queries the benefit of this is that you can create multiple versions of the front end and each one can be put in a different place and then linked to the data in the back end this allows multiple users to use your data from different places we're also going to look at how to add security to that back end because it's pretty pointless having your your front end and password protected when your data in the back end doesn't have password protection so once we've split the database we'll have a look at how to add security to the back end and then re-link that back end to the front end the user interface and so they talk to each other okay firstly i'm going to open up the database that we're working on last time that we added the password security to as you see it's prompted me for my password and there we go password in and we're straight into the database access has um database tools up here and very sort of strangely named button um up here it says access database but obviously we're in an access database i don't know why the microsoft's word is like that but when you click on it you'll see it's all of the dialogue is about splitting the database into the two parts um by clicking split database it then gives us a prompt where you want the back end to be saved if you look down on the name of the the database file which i close calls before split copy it's actually appended the underscore be onto this file name indicating that this is saving the back end of the database as it says up here create backend database so what i'm going to do is i'm going to rename that because we don't want copy we don't want before split but i'm just going to call that backend there we go and i'm going to save that in a different folder called after split so here we are so we're saving the back end part of the database and that's going to have just the four tables that we have up here in the tables tab in the objects box so clicking split and it says oh that can't be done because my client table was being used so what i'll do is i'll cancel that close down my navigation and then in fact if i actually go into that folder we will see that it's actually already split the back end anyway um so i'm going to open it up and probably see that it's only got oh it does have all the tables in that's good so it has actually happened i've got all my client data sales data and so on so it's got all of my data in that backend file okay so what i'm going to do is as as this split has happened fine it's just going to see just make sure i do have the front end and go find an open and that was before split copy let me in so that process actually did fail what i'm expecting to see here it are arrows next to these tables so it'd be a big big blue arrow saying that these are now um linked tables rather than um local tables as they are here so i'm going to do is going to close this form i'm going to go back into that folder and delete that back-end file and i'm going to do the process again right so this time i'm going to split the database with nothing open my navigation form is closed so go to database tools access database and split the database again i'm going to show it where it needs to be saved which is after split and then i'm going to as i did before rename it to and then split this time it should split successfully so we'll know that it splits this has split successfully because now we can see we've got the big thick blue arrows next to the tables saying that these are um remote so they're actually linked tables they're actually not part of this database file that we're looking at um i can open up my navigation form and i can let's look at some historic data there we go we can see the data is super fine inside my clients form there there we go so the database is split let's just have a quick look in the folders and just make sure that after split it has the backend file and here's the the data log file that goes along with it so that's fine that's been done now i'm going to close that down i'm going to close our database again because when we open up go before split copy obviously our front end is going to prompt us for the password and great let's be in but if i was to look at my data file so if i go file and open actually have a look at that back end file here so it's clive scars back end i open it here's my data in my tables and it didn't prompt me for a password at all so this is extremely insecure and where this should be the most secure part of the database it's the bit that has client details in it so it all needs to comply with data protection acts across the world so this part of the database also needs to be password protected so again i'm going to close it down and just like we did um last time we're going to open this file this just this back end file um in exclusive mode and if you've looked at the last video the way we do this is we click the open tab and then we go to browse we don't get tempted to open any of the files here in your your pinned files but we're going to go to browse and that's going to open up this box which we're now going to navigate towards where that back end file has been saved just after split so again we're going to single click just to select the back end file and rather than click open we're going to click the arrow next to open and open exclusive and enable content so what we have here now is exclusive rights nobody else can access this data because in the file and info tab again we're going to encrypt with password this time i'm going to give it a slightly different password and see how that it's dealt with the same and yet again block cipher and there we go so now that back end is password encrypted and we can prove that by trying to open the back end and there it goes it prompts me for my password and type the password in there's our data so that's now secure the problem we have currently is that i've used two different passwords i've used the password for the front end and a different password for the back end um now the problem we have is our front end which i'll just go and open up there we go closed cards before split it's going to ask me for my front-end password and then it's going to say password so it's not a valid password although it's actually opened the database because i've got all my navigation forms here you can see that every time i actually try and draw on the data from the tables it tells me that it's an invalid password that's because this front end doesn't have access to the back end because they've got two different passwords and even if they had the same password it would still deny access to that back end what we need to do is actually delete our links to the tables so i'm going to click on each of the tables and press delete and say yes to delete those tables so now currently our database has no take no data but what we're going to do is we're going to go to external data and new data source and then from database and we're going to select from access there we go okay so now what we're going to do is go and find the back end file which is should be here and we're going to open that okay so we've got here now the path to the back end file and rather than import the tables as we would um normally and we actually want these to be linked tables so link to the data source by creating linked tables and we're going to select that radio button down the bottom there so if the source database requires a password the unencrypted password will be stored with the linked table so what that means is that as i click ok it's going to prompt me for my back end database password just let me nbe so this is the back end password not the front end so when i click ok it's then going to show me the tables that are in that file and i'm going to select them all and select ok there we go now we can see we've got our linked tables back all four of them but if we actually now go to one of our forms it's actually giving us our forms now and we can see looking clients look at our historic data we can see that all of the data is now visible from the table so we can open the tables up and look at all the data's that have been stored in there and then close them down again what i'm going to do is going to show you that when we actually close our front end again and reopen it which was clips card before split copy it's going to prompt for my front end password and it's not going to prompt me for the back end password and it's going to still have access to the table so the backend database password has been stored in the front end so we've got access to all of our data so there we go what we can now do is go and make i should close you down we can make multiple copies of our front end so which is just actually i've got another database open as well let's close you there we go we can make multiple copies of our front end and we can name them differently one here i'm going to call that um bills copy and then we can have a another copy julie julie's copy and we can just make multiple copies of the database front end and each of them will have individual access to the data and we can open up multiple copies of the database so we can have multiple people actually working on the database at once so we've got clients cars so we've got one copy here working nicely and got another copy in the front so these can be on different parts of the network can be different locations with different people actually um adding data to the database or or getting information from the database um across across a business so hopefully you found that helpful and you're able to split and password protect your back end of your database and so it's all nice and secure okay i'll catch you again next time there's a video bye-bye

Channel: WBHS Digitech

Views: 860

Rating: 5 out of 5

Keywords:

Id: DdcyGLFp9d4

Length: 14min 53sec (893 seconds)

Published: Sat Nov 07 2020