How to use ADO and VBA to Read from a Database

in this video i'm going to show you how to interact with a database using excel vba and ado first we'll have a quick overview of what ado is then we'll write code that will read from a database and write the results to a worksheet using a record set then we look at two vital parts of ado closing the connection correctly and using late binding make sure you download the code for this video from the description below if you like this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it ado stands for activex data objects and has been around since the 1990s it allows us to read from databases and from other data sources the reason we use ado is so that we have a common interface when we want to manipulate data from different sources if we didn't have ado we would have to write specific code for every different data source when we use ado the first thing we do is create a connection to our data source we give video a connection string and this connects us to the database once we are connected to the data source we can run one or more queries to retrieve data each query returns the data to a record set object and once the data is in the record set we can easily write it to the worksheet this is the database that we're going to be using and when we run our query we're going to read all this data back to our worksheet so let's go ahead and get started on writing the code the first thing we do when dealing with an access database is we must get the file name so the file is in the current folder so we use this workbook dot path and then we use the file separator and then we use the file name now the file separator forward slash it's not compatible everywhere so instead of using this we use application path separator and vba will substitute the correct one when it runs when we connect to a data source we need to provide a connection string how do we know which one to use we can go to the website connectionstrings.com which has a large repository of connection strings for all different data sources we click on access and you can see the different strings are provided we're going to use the standard one we copy it and paste it to our code let's split it into three lines so we can see it clearly on the screen we use the underscore to split the same code line over a number of screen lines you can see where the file name is in the example connection string we'll replace this with our database file name now that we have a connection string we can use it to open our connection we create an ado connection object and then we pass the connection string to the open method anytime we're finished with a connection we should always make sure that we close it we put a breakpoint in the last line of the sub and run our code if the code reaches this line without displaying any errors then we are pretty sure that our connection has worked now we're going to write the code to retrieve data from the database and write it to our worksheet you may be surprised how little code we need to do this we declare rs as a new ideo record set we also declare a string to hold our query this query uses the sql language which is used to query databases we write select asterisks from foods foods is the name of the table and astrix means bring back all the fields this query will retrieve all the data from the food table now we use the open method of the record set this takes a query and the connection as parameter and this will bring back the data to the record set object once we have the data in the record set it's very simple to write it to the worksheet we use sheet result so i'm going to use this with statement here to make our code more readable the first thing we want to do is clear any existing data in the worksheet we simply say dot cells dot clear contents so this will clear the range to write out the data we get a starting range on the worksheet to write this data range has a very useful property called copy from record set and all we have to do is pass it the record set let's open the worksheet and run the code we run the code by clicking in this sub and pressing f5 you can see it brought us back all the data from the database and wrote it to the worksheet imagine we want to only retrieve the records where food type is beverage to do this we only need to change the query all the other code remains the same we go to our query and add a where clause this will filter the data for us based on what is in the food type field we run the code again and you can see that it only returned records where the food type is beverages let's do one more query to show how powerful sql is this time we want to retrieve all the food types and their average calories instead of asterisks we have food type and calories and we want the average calories so we use the aavg function we use a group by clause the shorter fields that we will be summing let's run the code and you can see we got back the results we expected you may have noticed that we haven't been bringing back the field header so far let's go ahead and write the code to do this now the first thing we do is to declare the variable i as long because we're going to be reading true to field using a for loop we say i equals 0 to record set fields count and -1 and this is because the list of fields is zero based so it starts at zero we use range a1 and then we use offset so offset is the count from the current range and we use it to move to whatever the current column is to get the current field we use record set fields and we then use the name property of the current field to bring us back the name now at the bottom we're going to change the one to a two so our data starts at row two we run this code and you can see that it brought us back the headers of the fields as expected if we're using the record set multiple times we don't need to pass it to connection every time we open it we can simply write the code a slightly different way so if you look at the parameters here open actually takes query as source and it takes connection as active connection so we can actually set these parameters off the record set before we open it this is doing the same thing but we're just setting it before we run the open so we set source to query an active connection to connection and then we just open it without any parameters when we run the code you'll see that it works exactly the same way the difference is we don't have to keep setting the connection now if you're looking at code on the internet with ado you might see some extra lines here like setting the lock type and often the lock type is set to read only and then the cursor type is set to the default which is open forward only now these are the actual default values so you don't need to use these two lines unless you want to change the values to something different it's very important that we close our record sets and connections any time we're finished even if there is an error so i'm going to show you the code that we need to do to deal with this so first we do on air go to eh so this means if there's an error it's going to go to the place labeled e h now we put an exit saw before this so that we don't hit it if we don't have an error i'm just going to have a message box error description here and now i'm going to create a second section cleanup so no matter what happens we'll go to cleanup if there's an error we'll report there and then go to cleanup otherwise we just go straight to it so in the cleanup what we want to do is we want to close our record set so we first of all check that record set is not already nothing and if it's not already nodding we need to check if it's open now we do this the way we do it is slightly more complicated than maybe what you've seen we use a bitwise operator so we're basically saying if our state and 80 state open equals 80 state open and the reason we do it this way is that there can be multiple states and we want to say if it's only state open and so if this is the case then we close it and obviously we set the record set to be nodding now when we're doing the connection we do exactly the same code as this the only difference is that we replace record set with our connection variable so we can do a ctrl h and this allows us to do a search and replace so we research replace with connection find a whole word only and match the case you can see that we've replaced let's remove this code so that we can see what we're doing a bit better let's test out this code and you can see we'll put in some breakpoints so we can see where the code stops now when we run it normally it stops in cleanup and we simply go through and close the record set same thing for our connection it's open so it just closes it but what happens if there is an error so let's change the code so that we've got an invalid finally inverted database and when we run the code now it stops in the error handling section so we display the error and after the error gets displayed we go to the cleanup section but our record set isn't in a state of open so we don't close it we just set it to nothing and the same thing with our connection our connection hasn't opened because the file name was invalid now don't worry if this code is a bit complicated or seems complicated you just copy it and use it in your own projects the way we've used ado in this video is called early binding when our applications will be used on different computers it is better to use late binding to avoid conflicts first of all let's rewrite this code slightly and then we will change it to use late binding we can rewrite the dim statement for the connection to be a dim and a set line the code runs the same but using set gives us more flexibility with creation of objects when we run this code it runs exactly as the previous code so now to convert this code to late binding what we do is set the type as an object then we use create object and we put the object name in quotes we do the same for the record set variable set it as an object and then we use create object now that we're doing this we no longer need a library reference so we can turn it off we run the code now and you'll see we got an error it doesn't recognize state open anymore and this is because it's not part of the library we can create our own genome like this we go to the top and declare enum and then set 80 state open to 1. we run the code now and it works just as it should the difference is we're now using late binding and if we run on a different computer we are much less likely to get conflicts if you want to learn more about ado and worksheets then check out this video on the screen if you like this video then please hit the like button and if you'd like to get notified of my upcoming videos then hit the subscribe button see you on the next video

Channel: Excel Macro Mastery

Views: 28,393

Keywords: #VBAADO #VBADATABASE #ExcelVBAADO #ExcelVBADatabase

Id: U_Eahf6O59Q

Length: 10min 30sec (630 seconds)

Published: Fri May 07 2021