VB.NET Tutorial - Connect & Query a Microsoft Access Database [FULL]

welcome to the VB toolbox in part 1 of my series on working with Microsoft Access in visual basic.net in this tutorial we will be learning how to connect to a Microsoft Access database I will be using Access Microsoft Access 2010 the differences between different versions are not really significant other than different connection strings and the support files required by the client in you know in the event that you don't actually have Microsoft Access installed on the machine it doesn't have to be installed but you will need support libraries in order to access that I will show you what those are here if you don't have Microsoft Access installed on your machine you may get an error that looks similar to this don't panic you can still open the Access database and work with it in Visual Basic if you install the redistributable for the Microsoft Access database engine ok and you will want to make sure that you have the correct version and also if your Access database was created in office a 64-bit version you won't be able to open it with 32-bit and vice-versa I don't believe so make sure that you have the right one if you hit the download link here you'll have the 64-bit version and the 32-bit version this is what I'm using in this tutorial so if you download the source code in the description make sure that you either have the 32-bit version of access installed or you download this free file from Microsoft I will provide a shortcut in the description there so you can get a hold of that alright anyway let's go ahead and get started here I'm going to begin by clicking new project and I want to windows forms application and you can call this whatever you want let's call mine MS access and hit okay that will generate our project template I can just go ahead and stretch this form out a little bit and I'm going to add a few items to this screen so I'm going to be showing you how to load database items into a combo box use a text box for searching and filtering and also I want to show you how to load records from your Access database into a data grid view so I'm going to go ahead and drop it data grid view onto my form and we want to set a few basic properties by default it wants to offer to use the wizard and set this up I like to code everything I prefer not to use the wizard so I really have a strong understanding of how everything is working together if you like using Wizards that's absolutely okay but that knowledge may not apply to this particular tutorial so let's find a combo box next and add that to the form and we will change the properties of some of these controls in a little bit next up I'm going to add a text box find that and you can click on that add that right in here and this is the field that we'll use for searching our database this is a nice little feature show you how to you make a nice little search feature and next I want a button I'm going to throw that in right here oops got a little bit small all right so probably got a little more space in there than I really need then really matter let's change some of the properties on these controls first up I want to make this a read-only control so the user doesn't type it in and you'll kind of see why later on to do that select the control like left clicking on it and go to the drop-down style property and when it says drop-down change it to drop down the list and that just makes it a read-only drop down filter next step actually let's change the name of the combo box I'm going to call this one CB X users combo box users and what I'll be doing is just pulling user names out of my database and populating this and you can use these these are great for creating dynamic queries and filters and whatnot for your database so just something you know that I want to show you how to do and you can apply that you know in any way that you see fit next up I'm going to take this text box here and I'm going to change the name of it to txt find this will be our search bar and I'm going to change the name of this button to CMD alright classic command button find and I'll change the text as well if we scroll down find the text property of that button and change that to find and finally let's take our data grid view here and change some of the properties the name of the data grid views is really long so I'm going to shorten that to dgv data okay this is where we're going to display our query results another property I want to set on here I'd like to be able to expand this with the form so if you find the anchor property you can change the layout here it's right there drop that down and just take these little boxes right here and what that will do is it will anchor it to the form boundaries so if we were to run this didn't like one of my properties apparently yeah I think one on once while you do have some issues with those let me try that again click on that select the anchor take these guys click off of it is it happy try running it and there it goes not sure why it does that now it should stretch with the form so that's pretty handy yeah don't panic if you try you know another thing if you are in debug mode and you attempt to change properties like that it will throw the same little error you know invalid but just make sure that all of these are ticked and then click out somewhere and it seems to save it hmm a little better all right now we need something very important and that would be our Access database now I haven't even added one you're going to need an Access database from somewhere I'm not sure if you can actually create one of these on the fly with vb.net I haven't tried doing that I you know I'll provide a link to the project source so you can actually have access to my little Access database in this project or you can create your own if you have Microsoft Access on your machine so I'm gonna add a new item actually I think I want an existing item sorry about that and what I need I haven't actually saved this project sorry I'm gonna get myself together here I'm going to save this so it has its own folder I'll try this again add existing item there we go that looks a lot better puts us in its folder it just hadn't been named yet so now I need I'm going to go ahead and just copy from another folder my Access database I'm gonna bring that in there it's going to paste it into this window here and you can't see it because right now it's restricted to these types of files I'm going to go down select all files and there's my Access database you notice there is no icon associated I don't actually have Microsoft Access installed on this computer I created this on another machine just kind of wanted to prove to you that you can access a database without having it installed so once you do that it brings up this wizard and you can choose a database model again I don't want to use the Wizards I want to know exactly how everything is working I'm just going to cancel out of that you should have this nice little Access database container now if you double click on that because we don't actually have all of the tools installed I don't think that you can really change the properties of the database you should be able to change any of those with code or by running queries so like if we click this we get access to these tables and you can run you know selects and inserts and different SQL commands against that table that database so there is a lot of power there still but you pretty much have to do it all yourself without the the ease of having the Access database program I'm going to go ahead and close out of that what I have is just a small single table database here with a few users some passwords and plaintext which is not always really not a good idea but just for the sake of the demonstration really all we want to do is connect to the database and pull out records so I'm going to add if you've seen any of my other database tutorials I like to create my own control class for accessing the database and running my queries against it or commands so what I'm going to do is add a class and I'm going to call this class a DB control not VB click Add and I will get a blank class template and now this is where the guts of our program are going to be first thing we need to do is provide access to the system data LA or oledb namespace and this gives us access to that the ACE driver for accessing either you know Microsoft Excel documents or Access databases yeah you can actually use Microsoft Excel as a database with Visual Basic it's kind of cool not always practical but it's neat so I'm going to say imports system dot data dot oledb and that's the only namespace we really need access to here so first up we want to create our database connection and this is we'll just say I want it to be private I don't need to access it from any other classes unless I wanted to provide some sort of overriding features which is totally okay I'm going to say I want to create our DB connection as a new oledb connection and my connection string I'm just going to hard code in here we want to use provider equals Microsoft dot s dot o le DB 12.0 and we'll end that with a semicolon and I'm just going to drop down a line here ampersand and an underscore and continue my string and now we need to provide the data source so my data source will be equal to the name of my database so sample accdb so I actually point to your database file and the reason I'm not providing any pathing information is because I'm using relative pathing there's no you know you using anything else like you know a static path like C colon backslash someplace on your hard drive you've got to move that to another machine and that directory doesn't exist you run into some problem so this just says you know look only in the folder that my application Exe file is launching from so in this case in our debug or Visual Studio it's going to be in the debug folder in bin so wherever this Exe file is that's where the access database needs to be you'll notice that is currently not in there we haven't built the project so it hasn't added it if we click on that database there is one important thing you probably want to select copy to output directory a lot of times people have run into a little issue where there they make changes in their application to the database it updates the database copy that's in their debug folder but this does not reside in the debug folder this is the master copy so if you set it to always copy and as soon as you hit the debug button and it builds the project it will overwrite whatever is in your debug folder with the master copy so you wipe out your changes that way I it's best to just make changes to the master copy and then set this to copy if newer so you can experiment in the temporary one the one that's in the debug folder and it will only overwrite with the master copy if the master copy changes okay so we set that to if newer copy of newer and go ahead and save my project here I hope that made sense so once we have our DB connection set up we need to create a variable a temporary variable here for our database command so I'm just going to say prepare DB command okay and this is just going to be private DB CMD as an oledb command now if you followed any of my other tutorials this you'll notice that this DB control class for example is nearly identical to the sequel control class or MySQL control class that we've used previously and it really is the only actual difference that you'll see is in the connection string or in the namespace I'm sorry the oledb so now we want a place to store our data so I'm going to say DB database data storing and handling actually would probably be a better description I'm going to say public and the reason I'm making this one public is because I need access to this from my main form so I'm going to call this one DB DA and this is going to be my data adapter as oledb data adapter and the data adapter is used for executing commands against your database and performing other actions like filling our data tables or data sets and I'm going to create our data table or data set next and I am just going to use a straight-up data table instead of a data set as I usually use you can use either one they both work so this just takes away a little bit of coding in the future saves us some lines um next step we want a simple way to manage query parameters this is just it's not something that's required but it's something I like to use query parameters are very important for querying and updating your data for reasons you know there's various reasons like handling data types and also preventing sequel injection attacks and things like that so I think it's very important to learn how to use these and they're really surprisingly simple as I'm just going to create this as a list collection so I'm going to create a new make sure you put new in there list collection of oledb parameters okay and we'll create a little sub down below for managing these simply and finally it's nice to be able to keep track of some query statistics record counts and especially the exception very important to manage your errors in your code so say query statistics statistics knew I spelled that incorrectly and I want these to be public so the first one is going to be the record count hmm I'm just going to make that an integer and next up I want a string to hold my exceptions and or errors public exception as string that just lets us know when our query or application through an error in this what we're running for a query or command so so this is where the magic happens the execute query statement and with these oledb commands there are a lot of different methods for running queries and you know executing non queries versus queries I like to be able to just handle them both at the same time instead of having to code separate subs for updates and deletions and you know straight-up queries that are supposed to return data so this sub is going to do it all so I'm going to say public sub xx query and I'm going to add one parameter that's just going to be the query string as a string okay very simple first thing we do when we execute this is reset our query statistics okay and I'm going to do record count equals zero and exception is just going to be a blank string okay I'll show you how we use that later on here and here we're going to want to use a try-catch statement to isolate any errors and keep our application from crashing hard it's nice to manage your errors as smoothly as possible so we're going to just do try and it should generate the rest of the statement for you will say open a connection it's the first thing we need to do and to do this is very simple we just say DB con dot open if it is unable to open the database if it's unable to perform this very basic command it's going to fail and it will report the exception in this string and we will capture it from our main form next up we will create our database command and to do this also we have to do is say DB CMD equals a new oledb command and we want to execute the query string is provided in our parameter and we want to execute it against our database connection DB con again nice and simple and once we've created our command any parameters that we supply during query time should be added to that before it is executed so I'll say load params into DB command okay so I'm going to say params dot for each and this is this is going to be a nice simple little one-line lambda statement if you're running visual basicnet 2008 these lambda statements do not work the same so you'll have to code it you can still do it you just have to code it differently instead of one line you have to do a standard for each expression so we'll say for each sub P and that's going to be our parameter DB CMD parameters dot ad P okay now I will kind of try to explain what this is doing this is going before each expression here loops through the parameters list that we created it up here finds every parameter in the list and executes this lambda are nameless sub routine passing the parameter that it finds on each loop into this sub and then we run the parameters add method for our database command and add that parameter to it being so I hope that kind of makes sense lambdas are absolutely phenomenal especially when it comes to minimizing the number of lines of code you need generally what you might have to do again like if you're running Visual Basic 2008 for example you could write this as for each P as oledb is a command I wonder if it's like DB per mo there you go Oh le DB parameter in DB CMD parameters okay so that well I'm sorry we wanted that we want to loop through the list okay go in the wrong direction here so essentially this is how you would do it if if you didn't want to do a one-line lambda here then you just say same thing we said before DBC MD dot parameters dot add P and it would accept that but instead we condense that down into a single line it's just a very simplistic expression so once we've added our parameters in from our list we then want to purge that list so next time we run a query it doesn't have the same old parameters that we use before then we can get errors if we don't do this so next thing we need to do is clear params list and to do that is very simple to purge a list you just say params dot clear boom all the records are gone from that and next up finally actually we're going to execute our command and fill our datasets so execute command and fill our dataset okay I guess in this case it's a data table so we need to create a new data table first we're going to say DB DB data table equals a new data table and then I'm going to use my data adapter I'm going to say DB da equals new oledb data adapter remember this is where the action occurs and I'm going to execute the DB command with that data adapter okay and once I do that then whatever was piped in here could be a select expression or an update command or anything else it gets executed at this point and then we can use the records to fill let's say record count equals DB data adapter fill and what do we want to fill we want to fill up our data table so we're going to say DB DT and once we do that all of our query results are loaded into this data table and they are ready then to output on to our form okay so that is what's occurring there as soon as you hit the fill it pipes that data into our data table and really that is it next step we want to capture any errors so we're just going to say exception equals e^x message okay that's very easy and once you're done doing all of your work because I like to work in offline mode as opposed to online mode you probably want to close your connection to the database and that leaves it available for others if you're in a multi-user environment should unlock any records that you had locked and what none so close your connection it's always a good idea you don't want to leave your database connection open it can cause problems so we're going to say if the database connection state is open then DB con close always close the door on your way out and that's it we are ready to execute queries and the beauty is once you've created this you can reuse it for any number of databases or you know it's it's really nice portable and reusable code there's one last thing that we need to do here and that is to manage our parameters list okay right here we're using our parameters list and we're clearing it but we don't have a way to actually add parameters in that very simply so here we'll say include query and command parameters okay so we're just going to create a very simple little sub here we like simple right ad pram and we have to supply two values here we have to give it a name as a string and the value and the value needs to be more nebulous than just a string the value needs to be an object which means it can really be any datatype and when you're passing different values into your database sometimes you've got strict controls on what goes into what fields is that a boolean value is it a string and integer or any other type of data so this needs to be very flexible so we can add any type of data to our you know as a parameter for working with the database so here I'm just going to create a quick variable I'm going to say new per am as a new oledb parameter and we are going to provide a name from the name and the value and when we're done we're going to add it to our list so we'll say params that's our list dot add our new parameter and then it's done so this class is is absolutely complete now and if we can find our database it might I'm not sure haven't actually copied it to the ran a build on it I probably need to do that before I actually run this or might throw an error at me might try accessing the data of course I'm not actually calling my class yet so but the class is ready to be called so now we can begin the part where we actually connect to the database and start doing some queries so let's just run a quick query when the form starts up you can select your form and press f7 or double-click on it I want the form 1 event so I'm going to grab that because I want to find the form shown event this is better than using the form load event form load event kehna now some things execute before the form has fully initialized and you know you can get different errors and sometimes it suppresses errors that you should see so I prefer to use the shown event in place of that all we need here to access our new fancy DB control class is a variable up here so I'm going to use a private and I'm just going to call this one access as new DB control wow that was hard ok so from here we can run a query and let's just test this out first see if it's going to blow up on us for not actually copying our database into our directory yet just going to say access dot X acute query and I'm going to do a very simple query here I'm just going to say select star from members okay Wow ultra simple query this selects all values all records not a good idea in a live environment for performance reason I'm going to go ahead and run this well it didn't explode so this executed properly with no errors at least I think there was no errors we didn't actually check form what we can do is we can say if string dot is null or empty actually we want to see if it's not empty if there's nothing if there's nothing in the exception or was that in our DB control class if there's nothing in the exception that means it executed successfully okay doesn't necessarily mean that any records will be returned but it did execute without errors so if not string is null or empty means the exact opposite of that it means that there is something in there there is an error that was detected so we'll say then message us and tell us what the error is so we'll say access dot will be our message and I'm just going to tack on a little one-line just to keep this nice and small whoops I forgot to actually specify the string that I want to check here XS dot exception helps if I can type alright so I'm checking this string to see if it's null or empty if it is empty good if it's not empty bad okay if we get an error we don't want to keep trying to execute more code on our broken data source so we're just going to exit out of the sub so let's run this see if we get an error no errors that's a very good sign alright let's force an error into this let's see our table name is members I'm going to say members X so I'm going to try selecting data from a table that doesn't exist boom didn't like that okay so Microsoft Access database engine cannot find the input table or query members X all right so we know that our query exceptions are working properly and you notice it didn't crash our application that's because we use the try catch you most of you are probably already familiar with the try catch statements but that's kind of how that happened how that works so you can handle those errors gracefully and then just exit out and you know discontinue writing any code against it so let's take this a step further let's say you know some of you would like to know how do you get the data from the database into the data grid view well this is surprisingly simple all you have to do is fill the datagrid we're what we're going to do is set the data grids data source property to be equal to the database data table that we filled with our data adapter in our DB control so this guy right here after this fills it the data gets pumped into this table and from there it can be piped out into this data grid view very simply so just going to say dgv data that's what we named it data source equals access dot DB data table let's try executing this now look I forgot to fix my table Hey look at that we can see all of our data well that concludes this tutorial all right just kidding there's more let's have some more fun with this that's pretty much how you access your data I mean you could quit watching at this point if you want but I recommend sticking around and you know enjoying the show so what if we want to fill that fill up that combo box with first names or the user names you know we want to create a dynamic query or just create a cool list of just user names and put it into this drop-down what we can do is we can fill the combo box by saying for each which I could use a lambda here I can't without creating my own extension method on the data the rows so for each R as data lips that's got to be a data row in Access dot dot a table dot rows will say CB x users that's our combo box dot items dot add our being our data row and the name of the column that we want to add in this case it's going to be the username so we put that in quotes just like that now look we have successfully pulled in all of our users and we could complicate this query a little bit too you know we could say I only want to see active members or you know you want a specific user ID or something like that or you just want to sort it you can filter this you could sort the query right now they're not alphabetically arranged so we could come up here and say order by username I could even throw in an ascending value run that and now they're in alphabetical order both lists are because they're both pulling from the same data set it could also reverse that now it's going top down awesome okay so now what to do um one thing I do like to do I'm going to set this back to an ascending sort order you notice that this starts up and you know it doesn't show it we don't really have any indication that this list was populated so what if I want to grab the very first value that it found in the event that it did find some values this is pretty easy to do we don't want to go in power for each loop we want to come down here say display first name found and I'll say if access dot is greater than zero and the reason I'm doing this is if we try setting the index value on the combo box when there are no items in it it's not going to be happy because it's like an array and you try calling something from an array that doesn't exist it's going to crash on you so we want there to at least be one record in there before we attempt to do this then combo sorry I got cut off there combo box users dot selected index equals zero and that should give us some indication that data was found see grab the first username it found popped it into that value you could run all sorts of other things you could do a selected index change event to you know when you select a person it will fill you know text boxes with that specific users information and I may actually do that in an upcoming tutorial for creating dynamic searches which would be really cool in this tutorial I intend to do a very basic wildcard search based on the user name so there's still more to come here hmm let's go ahead and get started with that now one one other thing that I really like to do is find any way that I can to shorten my code because I hate it when just have massive you know numbers of lines of code and you know if I can just shrink it in any way possible keep it more readable that's something I like to do and one very very silly and simple thing that I like to do is shorten some of these expressions or you know make a reusable function or something to shorten them so I'm just going to do this private function because we want to return a value it's going to say not empty and I want to check the text as a string and return a boolean value so as boolean I just want to see if the string is empty or not okay and it's essentially this chunk of code here I just think that's that's long if you have to do this you know we're doing this one time here maybe two as we go along but if you have a full-blown application that can you know that makes a lot of characters extra work in my opinion so I want to just return exactly what I'm doing here so I'm going to say return not string dot is null or empty and I'm going to use my text for my parameter here and just pipe that in and that should give me the same result as this yeah it just knocks off a little bit of length so I can just say not empty all right and that should still work yeah good good all right fix that check it out one more time oh yeah that's nice okay so what do we want to do next we want to be able to perform a search wildcard search on our database and return specific values this is actually very simple and kind of fun so we're going to say private sub search member we're going to create a member search not a member and we want to search by the name as a string and this time I'm going to show you how to use the command parameters that we created and these beauties are nice for preventing sequel injection attacks some databases have built-in protection against signal injection our SQL injection some databases don't but what it does is it prevents users from tampering with the database by injecting or using prematurely terminating strings and using parameters does this automatically by bypassing you know it literally pipes that to all of the text it can't break the string pipes it into the query command so it's kind of hard to explain definitely worth researching some go look it up you can get I actually have a tutorial from before a sequel server tutorial where I actually demonstrate it to some degree so so we want to add parameters and run our query to do this we're just going to say access to add / am colleen upon our method that we created to add parameters to our parameter list and the way we generally do this in most databases I don't know if it's it's certainly not a requirement I don't think this creates a database variable these parameters are like database variables so what I'm going to do is do at user for the name of my parameter and the value that I want to supply is my search string any search characters that I want to run through the database however I want to use wildcards and to use wildcards we're going to use a percentage a % and then you concatenate on the text that you added so in this case it's going to be whatever is supplied in the name parameter so say name and we're going to also end it with a wild-card so what this says is anything ignore whatever whatever the user name starts with doesn't matter and whatever it ends with doesn't matter as long as it finds names somewhere in that string then it's a successful match okay so now we can execute our query using this and another beauty another beautiful thing about parameters is you don't have to break your query strings and add in you know like a textbox value or things like that makes it kind of sloppy so I'm going to say access dot X acute query whoops and I'm going to say select I only want to return three different fields you could just do star like we did above if you want to return all values in this case I'm going to do a little more a little more clean query here I'm just going to select the password username and the email address and after your final line here because I'm breaking in the lines make sure you put a space otherwise you get a run on and it will look weird because I'm dropping down here it's just for you know because my screen is kind of small here I like to shorten my strings a bit so I'm going to select from the members table also adding another space at the end of my string and for my filter I'm going to say where username equals or actually I don't want equals I want to use the the like the like expression allows you to use wildcards sort of fuzzier search so and here's where we call our parameter and we just call it by its name just like it was a variable and it can be called right inside of our string as opposed to you know having to break the string and concatenate on values from other text boxes and whatnot so much cleaner way to do this and it stuffs everything in there it doesn't matter if the if the end user attempts to hijack your application or tamper with your database they can't really do anything with that parameter being injected like that so hmm alright so once we've got our query ready we want to report and abort on errors similar to what we did above so we can just say if and we use our handy dandy little not empty on our access dot xn family if you wanted to even hard code that into this not empty this just allows you to use any text whatsoever but realistically we're only using it for specifically for the access exception you could just have a very basic you know if not empty it's not very descriptive though so sorry I always get off on tangents here and end up burning a lot more time than anticipated whoops I already have then not empty if the exception is not empty then report it message box access dot and then if you use the : sign that just adds two lines that would never be separated by pressing enter just puts them on the same line those can be good and they can be bad if they make your code more readable than good if they make it less readable than bad so combo box fill it we want to put our results in the combo box same as we did above so you can just copy that little line there drop it down because it's identical and now we have our search so let's go ahead and add that to our command button I'm just going to double click on the button to generate our click event and I will call search member and the member that what I want to use for the name is txt fine dot txt so whatever we put in there it will search user names that equal or contain that value because we're using the wildcards so let's just give this a try hmm so I'm going to take you know I want to find everybody that has pizza in their name let's just try that hit find yeah it worked so pizza dude worked what if I wanted everybody that has an A in their name find boom there they are so it's working beautifully we have a wild-card search we don't even have to search for a full name just say hey I want to find all the dude the dudes in here well just pizza dude I want to find all the Dragons in here oh it's just dragon girl I don't want to search for anything or just leave a blank Oh grabs everybody so very simple searching that's really I think all I wanted to show you in this particular tutorial there are a number of other things that we can do with our database but this will get just started working with Microsoft Access and really hope this has been a helpful - helpful tutorial for you leave me comments suggestions give me thumbs up if you liked it thumbs down if you didn't I guess appreciate you watching and supporting the channel thank you very much take care everybody bye bye

Channel: VB Toolbox

Views: 119,013

Rating: 4.8827224 out of 5

Keywords: VB.NET Tutorial, VB.NET Programming Tutorial, VB Tutorial, VB Programming Tutorial, Visual Basic Programming, VisualBasic Programming, VB.NET Database Tutorial, VB.NET Microsoft Access, VB.NET Access Database, VB, VB.NET MS Access, VB.NET Microsoft Access Tutorial, vb.net access database tutorial, VB.NET Database Search, vb.net projects with source code

Id: q19OXha1jDw

Length: 55min 17sec (3317 seconds)

Published: Thu Sep 10 2015