57. Splitting Our Database (Programming In Microsoft Access 2013) 🎓

hello everyone and welcome to programming in Access 2013 my name is Steve Bishop today's video is going to be on splitting the database now up to this particular point we've been putting our database file on one single computer and that's perfectly fine if only one person is ever going to need access to the file but most of you probably work in some sort of office or network environment where you've got multiple workstations out there and you've really probably been designing this database in order to be viewable by other people in your office but this kind of poses a problem because obviously they may be working on it simultaneously you can't just have one single file and just keep copying it moving it from PC to PC you need to create some sort of relationship that allows your data to be accessible from all of these other workstations so this scenario this networking situation here is often called a server to client relationship where you have one single host computer that is serving the data while your clients are accessing that data through some sort of some sort of software on their local PC and that local PC has permissions set up in order to gain access to the the data that's being hosted by the server so this is exactly what we're going to go ahead and do with our Access database and it starts out with our current database file which is just a single file and we're going to need to actually split our file up into two separate files in one file we're going to put all of our tables which houses all of the data then in another file we're going to put our forms our reports and our queries as well as the VBA code that supports those things so again we're splitting our database into two separate database files one file contains the tables another one contains the forms reports queries and VBA code this is called these are called our back-end and front-end files the backend holds the tables or our data and our front-end contains the user interface which is our forms reports queries in VBA code okay you've probably heard those terms before back-end which is our data you've probably heard data or server and then on the front end we have a UI or user interface that the user actually utilizes okay so once you've got the database split up into our back-end and front-end you're going to put the back-end on the server and you're going to distribute the front-end to all of the different workstations out on your network alright so that is what we're going to go ahead and work on today now I've got my database here with all of the tables queries forms reports and modules all in one service inc accdb file and i'm going to need to split that up but before I can even do that I need to set up some permissions for access to those files before they're even created so I'm going to go ahead and go to my computer and go to my C Drive and I'm going to create a new folder I'm going to call this folder database you can call it whatever you'd like now you also need to make sure that if you are in one of these Network scenarios that you have full permission in order to do this you may need to contact your domain administrator to see what kind of access you have and you may have to work with them in order to get your back-end available out on maybe some other server that's going to be hosting the file you know perhaps there's one specific file server location that's out on the network somewhere and you need to let them know that you're guilt you're planning on putting your back-end into a folder out there on that server but for right now I'm just going to go ahead and use my local PC as the server so I'm going to create this database folder and then I'm going to right-click on it and go to properties now under the properties here there's a sharing tab and I'm going to go ahead and go to the advanced sharing you may not have the option to share just yet and there are a series of steps that you can go through in order to turn this on and I suggested at this time if you don't see this advanced sharing button go ahead and stop the video here and go to Bing and try to see if you can search for how to turn on advanced sharing for Windows 7 or higher I'm going to go ahead and assume that you got at God advance cheering turned on so I'm going to go and click on the button and I'm going to click on share this folder I'm going to leave the the share name database so that it's easy for me to remember and then I'm going to go and click on the permissions button and right now everyone just has read access and that's okay but there's a problem if I'm going to put my back-end my back-end file and make it available for others to write data to they're going to need to be able to have more control than this so I'm going to go ahead and allow full control for everyone I'm going to go ahead and apply that and apply my shared folder and go ahead and close out so now my database folder that I created is now in a state of shared and I could just double check this by going and hitting backslash backslash my computer name which is just Steve's - PC and you'll see that there's that database folder that's made available on right now there's nothing in it anyone else on the network should be able to see the same folder now using this same address up in their address bar its backslash backslash your computer name backslash and then whatever the shared folder name is all right so I've got my shared folder already set up and everyone has access to it you may want to go through and consider strongly who has permissions right now I've set up everybody oops right now I have everybody that's made available to this database folder but you may want to go into these into the into the permissions here and add different users maybe you want to add just your boss or just you or whoever is all going to need access to this folder and set them up but just make sure that they have full control okay so that's all done we've got our folder all created so now it's time to split up our database now I could go into our database folder here and I could create a new Microsoft Access database and then just export these tables one at a time to that database but Microsoft has given us a little wizard to do this for us and I'm going to go ahead and do that by clicking on the database tab there the database tools tab and under move data there is access database so if I click on this I'm going to go and click on split database you're welcome to read that text if you want to but it's kind of unnecessary and now I'm going to go to my C Drive hit database and I'm going to go ahead and save it but I'm going to make sure that the file name has this underscore back-end or B e you know de underscore B is added to the service Inc and that stands for back-end okay so this is going to be one file that is going to be stored on our database folder that is going to be the backend so we're going to go ahead and click on the split button and you can see accesses world away here says database successfully split I click OK and if you look over here now our tables have this blue arrow pointing next to them and when I hover the mouse over each one of these tables you can see underneath is the file location C colon backslash database backslash service ink underscore bacc DB now there's a problem not everyone is going to have this C colon backslash database backslash service ink underscore PE because this is my local drive ok this is now pointing the table to my local drive to get access to that file but other computers out on the network will not be able to see this file ok so I need to make this file I need to relink all of these tables using my network share rather than the actual local location and there is a way to do that but just before I do that I just want to show you that my service an ink underscore PE file is located in my C colon backslash folder but it is also available now if I do backslash backslash Steeves - PC whoops - PZ backslash database if I go there you can see there's the same file okay and if I just click on this you can see Steve's - PC backslash database I'm going to copy this location because it's going to help me in just a moment I'm going to go to external data and you'll see there's this button for linked table manager I'm going to select on this and you'll see here is a list of all of the linked tables that are in my database you can see right now all of my linked tables are pointing to the C colon backslash database backslash service st. gutter Scourby e so I'm going to go ahead and select all I'm going to take all of these tables and I'm going to tell it to prompt me for a new location of where to find these tables then I click on the ok button it's going to ask me for where is the new location of that table and I'm going to go ahead and go to that folder location that's out on the network backslash backslash Steve's PC and there is our file so again backslash backslash your computer name backslash your your shared folder name and then select the file the back end file so I'm going to go and click OK accesses world away all selected linked tables are successfully refreshed click OK you can see now that the folder that the file location is that backslash backslash computer name and folder location okay so that's what we want to see we want to see that shared name for the location of where all these linked tables are now if I click close you'll see I can I can double click on these and I can open these up all of these tables are available to me even though they no longer reside in my Service Inc file I'm actually accessing them through this file that sits here out on my on my shared folder and I can prove that to you by if I just open up one of these okay I've got a table open you'll see that this L accdb file opens up which means that there's currently buddy accessing this accdb file okay that's what this LACC DB file is doing it's keeping track of all of the modifications and changes that are being made to this file and it also makes it so that there is a bit of a problem for the next step alright so I've got my database successfully split up I've got my back-end database file here but what about the front-end what about my queries forms reports and modules right now they're sitting out here on my if I go here to my documents here I've got my database file that I've been working on here with Service Inc and you can see there's that LCL accdb file okay and while it's open while is LACC DB file is currently operating on my service Inc and I've got it open no one else can access this file okay and that's going to be a problem later for right now I'm going to go and click on file I'm going to go to save as I'm going to save my database as and I'm going to go to that database folder and I'm going to go ahead and save it but this time I'm going to name it with underscore F II which stands for front-end okay so now I've got my back end and my front-end are both being worked on here on in this shared folder okay so back-end and front-end and you can see there's that LACC DB file now if anyone else tries to double click and open up this front-end file they're going to get a permission issue even if they have full permissions to the file you can only have one person accessing this front-end file at a time and that's going to cause some problems let me go ahead and click on enable content this is just basically giving me security warning that I've got these modules with code in the background and you'll get that from time to time so don't worry about it just make sure that everybody knows to go ahead and enable that okay so how do we resolve this well I'm going to close this down for right now and I'm going to go ahead and right click on my front-end file I'm going to go to the properties go to security and then under the Advanced Options here I'm going to click on advanced and right now it's kind of disabled right now it says include inheritable permissions from this object's parent which means that this file my front-end file has the same permissions on it as the folder that it's whole that's holding the file so all those permissions that I set up for all the users and my database folder they also apply to this file and I want to change that so I'm going to go to change permissions and I'm going to uncheck the include inheritable permissions I'm going to go ahead and yes I do want to remove and now no one has access to the file okay literally no one has permission to the file so I'm going to click Add now and I'm going to add users now again you're going to want if you're in a domain environment where you have an administrator you're probably going to want to talk to them about who you're going to want to give access to this folder and these this users group comes from your domain controller or your active directory so you're going to want to talk to your administrator and find out what the group names are that you should be giving permission to okay and again this is just your front end your back end is still going to be made available to everyone so with the front end though we're going to want to just do users for right now which is a special group for Windows 7 which is basically anybody who is authenticated any with anyone who's logged into this local computer so I'm going to go and click OK and for right now I'm just going to go ahead and allow full control and click OK and apply and we'll exit out of that now right now again we've basically given everybody full control over the front end and we need to change that I'm going to open this one more time and go to properties again and under the security tab now I can use a little bit of a simpler method here I don't need to go into the advanced anymore I can just click on edit for my user group okay so hit edit and now I'm going to drop the full control I'm going to drop modify I'm going to drop read and execute and I'm just going to leave checked only this read option so the only thing that they can do is read the file I'm going to go ahead and click apply and okay and now if I try to open up the file you'll see that it only allows me to open this and read it okay I can only read it I'm going to go ahead and able to contact I won't let me get until I save it okay so now you can see that they're getting this option that says save as so now I can click save as I'll get a little warning that's perfectly fine don't worry about that I'm just going to go ahead and go to my desktop and save it enable the content and now I have my own working version with full permission and full access to this front-end database while at the same time my database file you'll see my front end does not have the LCC be a B file it's just my back-end file so this file here is basically locked no one is going to be able to open it and access it they're going to be prompted to save it because it's only going to open up and read only okay so that's just one nifty little way of handling that probably the most popular solution to this particular problem is to create a batch file that actually copies this front-end file to their local C Drive and then opens it for them I'm not going to take you through all the steps in order to do that you can probably just go to Bing and do a search on how to distribute access front-ends and you'll see that they're probably already batch files created that you can just copy and modify a little bit you can also talk to your domain administrator and see if he has a better solution for copying these you can actually use home directories and such you know certain things like that in order to grant permissions to that front-end file but anyway that is how you break up that's how you split up your database into a back-end and front-end file and a way for you to make that back-end and front-end available to your users while still allowing for full full working versions of the database file okay if you have any questions feel free to pop them in the comment section below I know this is a pretty complex scenario and I'm sure lots of you will have questions I'll be sure to answer them as best as I can

Channel: Programming Made EZ

Views: 164,405

Rating: 4.9138098 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, beginner, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, split, splitting, front end, back end, server, client, Software (Musical Album)

Id: 50NSwFfCHF8

Length: 18min 35sec (1115 seconds)

Published: Sat May 03 2014