Is Microsoft Access Still Viable in 2024?

hi folks and welcome back to the advantage applications Channel my name is Rich and today I wanted to make a short video on a topic that I see brought up from time to time online in forums and it's also been discussed at my work in meetings and that is is Microsoft Access still a viable development platform to build custom tools for the modern office and the short answer is yes absolutely it is but not exactly as it was originally intended access was meant to be a standalone database solution for a very small business or a small Department with maybe one to five users uh concurrently in the database at a time it was probably meant to run on a single workstation or maybe over a local area network again for a small number of users and those users would have been hardwired onto that Network and there were some pretty severe limitations when you use access in this way um since it's file based wherever the database file resided anyone that was going to use that database needed read write edit and delete permission to that folder where the file resided and that meant that the file could inadvertently get deleted renamed copied older versions copied over it information could have been updated or changed in some inadvertent ways it was vulnerable it was prone to data and code corruption and the more users you had in the database concurrently the greater that risk went up and also using the database over a remote connection like VPN was pretty much a non-starter it was painfully or prohibitively slow and again the risk for corruption went way up if you tried to connect and use it over VPN or something like that so how then is access best used today in the modern work environment well in my opinion it's really well suited as a rapid application development tool when the data resides in a more robust database server like SQL Server uh any database system that allows obbc connections or my personal favorite SharePoint and I'm going to show you the way that I configure my Solutions when they are access front end and SharePoint backend I'm going to go over some of the advantages of setting it up like that and in fact I'm going to start a series of videos on how to create a solution where access is the front end and sharepoint's the back end some special considerations that you need to think about when you're first starting up a project like that and eventually even how to migrate an existing access database no matter how long it's been in use to a SharePoint backend so if that sounds like something you'd be interested in consider subscribing and uh let's go ahead and get into it okay the first development tool that I'm going to show you that you get in Microsoft Access that is really awesome is the table designer I've already got a few tables made in this database and a form but I'm going to go ahead and create a new table just to show you how it works so we're going to come up to our create menu here and there are a lot of options obviously in this menu and there are actually more than one way to create a table but I'm just going to show you one I go to table design and here I can Define the names of the fields I want in my table and also what data types those fields will be so the first field that I'm going to put in here is overtime ID I'm going to let that be an auto number field and I'm going to make it my primary key when I'm working on a project that I know ultimately is going to reside in SharePoint as far as the data goes I always explicitly name the ID field I don't just use a generic ID name for that field I give it a name that actually makes sense for the table that it's in the reason I do this is because SharePoint is going to add new fields to this table whenever we export it out and using a generic field like ID sometimes conflicts with the names of the fields that SharePoint assigns the next field I'm going to add is overtime date and again I don't like to ever name a field just date because that that definitely has conflict with reserved words and access and other applications as well I'm going to define the data type as date slash time and I'm going to specify a format down here too I'm going to use short date and this again is another another really cool thing about access table designer it is very much like a really high-end database application like SQL server for instance where you can set formatting input masks default values captions and in fact on this one I'm going to put a default value of the current date okay the next field that I'm going to add is going to be caused by and this is actually going to map back to the t uh TBL employee over here so it's not going to store text it's actually going to store an identity from this table as a foreign key here so this is going to be a number and it's format as default to Long integer that's perfect now I'm going to enter Work crew ID this is also going to be a foreign key from TBL work crew then I'm going to add job ID another foreign key field so it's a number then hours this will be this this database is going to be like an overtime database I Ed this in another one of my videos and now I'm just going to use it to kind of demonstrate this as well but the idea is that people can come in here and track their overtime hours so instead of short text obviously I want this to be a number and instead of just whole numbers I'm going to format it so that it's a decimal single precision and I'm going to change the decimal places from Auto to two after that I'm going to create a comment field this will be text but not short text I want to make it long text so that it'll accommodate more than 255 characters okay that's it I'm going to save this table that's TBL overtime and it pops up over here in the Navigator and in a moment when I'm ready to export these out to SharePoint it will keep all of that formatting all right the next thing I'm going to show you that I use in Access quite a bit is the relationship designer so that you can Define the relationships between your tables now to be honest I don't really use this a whole lot with solutions that are going to reside on SharePoint because SharePoint doesn't support the relationships that you define and access but that's not a big deal because I can define those relationships in my queries on the front end and also in the forms if they use combo boxes and things like that and I can also do it in code so it's not that it's limiting it's just SharePoint doesn't use it but I want to show you how it works real quick anyway so here under database tools I can click relationships then I can just drag tables over from my navigation pane here we'll start with table overtime and I'll take table job and we see job ID is here and job ID is here is a foreign key it's the primary key in TB job so I can just click and drag that primary key to the foreign key in this table and it's going to pop up a relationships window and it says table job to table overtime job ID fill in table job job ID fill in table overtime that's what we're relating I'm going to go ahead and check uh enforce referential integrity and you can even set up cascading updates and deletes I'm not going to do that for this relationship but when I click create you can see that it sets up a one to many relationship and I can do the same thing with TBL employee as I mentioned earlier caus by field over here Maps back to employee ID we do the same thing we have another one to mini and I would do that with work crew as well work crew ID is a primary key here and it is a foreign key here enforce referential Integrity click create and there we go so if I were if I was keep keeping this solution in Access which I don't recommend but even if I was migrating it out to SQL server using their SQL Server migration assistant it would keep these relationships as we Define them here and once you define the relationships like that it carries through inquiries and forms and reports and everything you do in the database so it's pretty handy all right so now let's say that we've actually got data in our TPL overtime table if we open the form it would look like this basically we have the date overtime was taken who took the overtime which work crew they were a part of which shift basically what the job was and how many hours was worth and I show you this because the third thing that I want to show you in Access that you have is a development tool that is really handy I think it's very powerful is a graphic uh query designer so again we come into our create menu and you can use Query wizard to create certain types of queries and this can be really easy especially uh this find unmatched query wizard I go into what that is but I do use that from time to time otherwise I tend to just create my queries on my own so I go into query design and again I can pull tables over from my navigation pane or I can use the add tables pane that pops up over here on the right so let's say that we wanted to write a query to total and sum all of the hours for each employee that's taken overtime to do this we know we're going to need the overtime table so we double click it and we get it here on our query panel I'm going to close the property sheet here and we could start just as it is right here without linking this to other tables and I'll show you what that looks like and then I'll show you why we link it to other tables since all we're really interested in is totaling and summing hours for each employee individually all I really need is the employee ID which in this case would be the caused by and the overtime hours now if I ran this just as is it's already a select query and I could Define queries to make new table I could Define queries to append data to existing tables I could Define queries to update data in tables make changes to that data in other words I can make cross tab queries and delete queries also Union pass through and data definition queries I'm going to keep it real simple today just with a select query but this is a really powerful tool and you can not only connect to access tables but you can connect to excel you can connect to any database that exposes obbc connections and you of course you can connect to linked SharePoint lists so if I run this now it's going to pull in everything because I have no filters no parameters no conditions on this data whatsoever so now if I go into query design and I click my totals icon here I get a new row here that says total and I'm going to leave it Group by cost by that's going to group it into the employees and then I'm going to sum on hours now if I run the query now each of these IDs correlates to an employee and TBL employee and this is the total hours that they work in the data set that we have but this number isn't very useful to us so let's change the query so that it actually displays the employee name go back into design view and now I'm going to pull in TBL employee and you see my relationship pops right up here because we've already defined it in the relationship panel but if it didn't we we could Define the relationship right here again and that's what I do when I link to SharePoint lists because SharePoint won't hold relationship information about these tables on its own whenever I use a query I just redefine that relationship in here it takes a little extra time but it's really not a big deal okay so I'm going to bring in last name and first name I'm going to create a new display field called full name and I'm going to Define it as the last name field and I'm going to concatenate a comma in space and then concatenate the first name field now I can uncheck last name and first name here since I don't need it represented in the query at all because I'm going to have full name that's going to show up and I want to click and drag that just to the left of our so that it still makes sense as a matter of fact I don't need caused by either because I don't need that identity to show up okay now if I run the query my results look like this and it makes a lot more sense it looks much nicer okay so now let's say that I wanted to give that some kind of parameter like a date range I don't want to see everything in the data table I want a specifi date range to look at I can go back into design view so since I want to filter on date now I'm going to need to pull it in from TBL overtime and I'll pull this all the way to the left and instead of group by I'm going to say that this is going to be a wear condition and I'll go ahead and Define it explicitly here and we'll say it's between February 1st of this year and February 29th of this year since it was a leap year now it just so happens that all of the data in this table overtime is 4 February and that's because it's just some test data that I put in so we'll actually narrow that down a bit right here in our criteria expression we'll say that we're going to 2 15 all right let's run that and our numbers update and we could actually break that down even further we could have it grouped by job underneath employees and that would work it would break out even further uh there's just a whole lot you can do with anything you can do with SQL you can do in this query designer and as a matter of fact you can actually view the SQL directly and modify it or what I do is copy it sometimes I'll come in here and I will design a query and then I'll copy the code into my VBA and then tweak it there and that way I don't have to Define parameters or certain relationships explicitly I can do it later in code dynamically so it is a really really handy and Powerful tool it's one of the things I use the most in my solution development and we save it and we see it shows up here in our navigation pane under queries the next thing that makes access a very powerful application development environment is its form designer this form here is a good example let's look at it in design view and then I'll show you how it works so in design view you can come up to form design here in the menus and you have a whole set of custom controls that you can add to your form including text boxes list boxes combo boxes option groups tabed forms sub forms images charts graphs I mean there's just a ton of stuff that you can do in here it's a lot like visual studio and everything in here is customizable with code access has its integrated language Visual Basic for applications VBA and you can write some really powerful code to Loop to make decisions to modify attributes and properties and also to automate tasks it's really powerful and to get to the VBA editor you just come in here and choose properties and you have a tabed panel that opens up here and in this tab panel you can modify all of the attributes and properties of the form or whichever control you have selected you can see that the properties window here updates when I select a control and here I have the submit button selected if I go to the event tab on the click event here you see that I have event procedure listed there and if I click the little ellipses to the right it takes me to the code that runs when that button is clicked and I won't go through this code right now I have a couple videos that you can check out if you want to learn how to work with record sets in VBA or if you want to learn how to execute SQL from VBA I'll put the links to those videos in the description and also all of the practice files and all of the source code that I go go over in these videos is available to my members I'll put a link in the description on how you can become a member of the channel as well it costs less than a cup of coffee and you can cancel at any time so let me show what this form does real quick just based on the code that I have written come and put it into form View and the idea is someone can come into the controls here at the bottom of the form select a date I'll just pick today select a person that may be absent or in Need for overtime we'll say that Skylar is going to be out based on the information in TBL employee it knows that she works shift C and that her primary job is weight staff and so now I can come in here and just put how many hours of overtime this absence requires and click submit and it comes right up here and Pops it into the list and this is just a really simple example I mean you can write code to do a lot of powerful stuff in VBA and it's a relatively simple language to learn and when it comes to VBA you can come into your create menu here and and you can create code modules to build your own functional libraries for any specific tasks that you might be building you can even build classes if you're into objectoriented programming and you can record macros but that doesn't really use VBA and to me it just isn't as powerful as if you actually learn to code and tell access explicitly what you want it to do and for the sake of being thorough I do need to mention that access has a built-in report generator you can design your reports to have some mized data group data filtered data and you can also have graphs and charts on the report as well but this is one area that access is really starting to show its age and it's definitely not what I consider one of its stronger points there are a lot more powerful Solutions out there like powerbi and even Excel as an analytical tool Excel has a lot more flexibility than what you get an access is report designer okay so let's say that we've used access now we've developed a solution it's ready for export out to SharePoint before we distribute it and by the way that is is definitely how I recommend to do this I definitely recommend making your tables local first testing all your code testing all your functionality then exporting those tables out to SharePoint so then I can just right click on the table that I want to export I choose export from the context menu and SharePoint is an option right on that menu select the SharePoint site that you want to migrate it out to you can open the list when it's finished I'll goe and leave that checked most of the times I uncheck that but for this demonstration I'll leave it checked and we can look at it I'll click okay you may be prompted for your credentials I've logged in pretty recently so I'm not going to be prompted right now it moves the data out and here it is now as a SharePoint list now to link back to that list what I would do is come back into access and I'm going to delete this local version TBL employee I go to external data new data source and I want to choose from online service and you see SharePoint is right here at the top of that list I click that that again is my SharePoint site I don't want to import the data to a local table I want to link to the SharePoint list itself click next and here I can come in and it's going to show me all of the lists that I could link to if I wanted to and since I'm looking for TBL employee right there it is I just put a check mark beside it and then click okay and there it is okay I'm going to do that for the rest of these tables and now that all my tables are linked up we need to retest the functionality of this database okay so I double clicked my form to launch it I come down here to choose a date we'll go with tomorrow we'll give this to Hank Sher we'll say 2.5 hours click submit and there it shows up at the top of our form so now we know that our linked tables now residing on SharePoint are working with everything that we wrote so far if we executed this query again we would also see that it works as well so what are some of the benefits that you get from migrating the back end out of SharePoint well for one you can control security a lot better whoever you grant permissions to to access your SharePoint site is going to have access to your data by default but you can also then restrict that further down to each SharePoint list if necessary I don't know why you would that with an access database but if you ever need to that's a possibility another thing is that it's always backed up you don't have to worry about corruption it can serve as many concurrent users as you need and it's highly available over remote connections like VPN so pretty much everything that access had as a weakness is addressed when you link up the back end to SharePoint and as I said I'm going to be going over more and more real world examples of this as we progress through the video series we're going to actually take a database that's already in use and migrate it out and you're going to get to see some of the challenges that you come up against and some of the things that you have to change in the table definitions before you migrate them out to your SharePoint lists but anyway I hope this is interesting if so like I said please consider subscribing and if there's any topics that you would like to see me cover or do a series on please let me know thanks for watching and take care

Channel: Advantage Applications

Views: 26,944

Keywords: access, excel, vba, visual, basic, applications, code, sql, database, data, base, good, Sharepoint, server, advanced

Id: SBDNSwhMpm0

Length: 21min 18sec (1278 seconds)

Published: Mon Mar 04 2024