Excel VBA Introduction Part 30 - ADO (ActiveX Data Objects) Querying a Database

welcome to this YSL tutorial in this video we're going to talk about how you can write Excel VBA code to query a database using ActiveX data objects we'll start the video by talking about how you can set a reference to the a dough or ActiveX data objects library and then how you can use this to create a new connection object we'll spend a bit of time discussing how to set the connection string for your connection and show you several different methods of how you can do that and we'll create connections to both the Microsoft Access database and a Microsoft sequel server database once we've created the connection we'll talk about how you can create a new record set load records into the record set and then copy that data into an Excel worksheet towards the end of the video then we'll start talking about how you can write SQL queries in VBA so this links in quite nicely with the video series we've made on exactly how to do this right at the end of the video we'll show you how you can then ask your users to provide inputs which modify the results of the queries so essentially designing your own little query tool using Excel VBA there's quite a lot to do and quite a few technical ideas involved let's get started for the first example in this video we're going to write a routine which connects to a database loads a table of data into memory and then simply copies that data into a new worksheet in our workbook we'll demonstrate this for both a Microsoft Access database and then for a Microsoft sequel server database as well mainly just to show you how simple it is to get a doe to talk to essentially any kind of database for this video I'm going to be using Excel 2013 but it doesn't really matter which version of Excel you're working in will write the code so that will work with any version essentially so to get started let's head into the the VBA editor and I've already made a start by creating a module for myself in there I'm going to create a new subroutine called copy data from database and then we can start writing some code that will do exactly that the easiest way to write code using a doe involve setting reference to the correct object library before you start writing any code so let's head to the Tools menu and choose references and then we can scroll through the huge list of available references to the section that has Microsoft ActiveX data objects listed sounds very exciting isn't it and it's not that exciting to prefer and these are the Aido object libraries you'll always have a number of different libraries installed depending on which version of Windows you're working in if you're interested in the history of these versions and let's face it here is an interested in the history of a doe a quick Google search will turn up this help page for you and so you can get an idea why each version exists those pages a little bit out of date it only lists up to version 6.0 which was released in Windows Vista and the versions prior to that were from Windows XP and earlier so obviously it makes sense to go with the latest version we have available so how'd you tap back to my VB editor 6.1 was released in Windows 7 I believe so that's the version I'm gonna use for now a little bit later on we'll make sure we can edit the code so that will allow us to talk to any version of a doe regardless of which version is installed on the user's computer but for now let's go with the latest version we have available so in my case that's the 6.1 library you'll might be different but click OK when you've done that and that gives us access to a whole range of extra objects and some methods and properties if you won't see what they are you know to the View menu and choose object browser and in there if you use the drop-down list at the top of the window to choose the adodb library this is the one that you've just referenced this will give you an idea of all the new types of classes and constants and all sorts of other things that you've made available to your VBA project so close down the object browser and what that lets us do now is create a variable which can hold a connection object declaring a variable to hold a connection object is fairly straightforward let's start with dim I'm gonna call my connection movies con because we're going to be connecting to a movies database as what I want to store or sell a reference to is a type of object called a connection which I can just reference by stating literally the word connection but I always think it makes sense when you're referencing classes from a different object library to precede the name of the class with the name of the library to which that class belongs I'm going to say aid ODB you'll see the little icon for a library there little set of books followed by a full stop and then you can look the connection object there okay so that's the the variable that will hold the reference to the connection what we need to do next is create the new instance of the connection we can create the new instance of a connection by simply saying set movies con I press ctrl + space there by the way on the keyboard to display this intellisense list set movies con equals new aid ODB dots connection and once that's been done we've got full access to all the methods and properties of our connection by simply referencing our variable movies comm if you type in a full stop you can see the full list of all the things you can do and all the properties you can change on that connection object the most important property to start with is the connection string property so let's have a look at how to modify that next actually before we start looking at how to set the connection string I wanted to mention a couple of other methods you could use to create a new instance of the connection so I'm going to make a quick copy of this subletting and paste it in down below and I'm going to change the name of this so that it's called copy data from database Auto instancing mayor voters mentioned this in previous videos the idea is that rather than explicitly setting the movies connection object to be a new instance when you want it to be created you can actually incorporate the new keyword into the variable declaration so use a dim movies con as new IDO DB connection now smart misleadingly that declaration didn't actually create the new instance of a connection what happens is that the vb editor will wait until you first reference the variable movies con and whenever you reference that variable the editor checks to see if it references an object or not if it isn't already reference an object then the vb editor will automatically create a new instance for you which sounds brilliant and really convenient it saves you an entire line of code there are a couple of small downsides to it though first of all it's not just the first time that the variable is reference it's every single time you reference this variable in this subroutine that the VB editor checks to see if it references an object or not so it adds a small overhead to your procedures when you're using auto instancing variables on top of that it means you can't let your own explicit tests to say things like if movie con movies con is nothing then you might want to add no display a message that says the connection doesn't exist but with this type of aerial with an auto instancing variable this test will never ever work as soon as we attempt to run it I'm going to use the f8 key to begin stepping through as soon as we begin to run it you can see at this point that the movies con is not set that's what the little message tells us but as soon as I press f8 the auto instancing variable automatically creates an instance of it so this means that we can never check if it's nothing CC is now actually created and instance object so a couple of very small downsides to using auto instancing variables which means I don't tend to use them myself in the real world I tend to use the explicit setting of the new instance like we've done in the first example here one other way to create a new instance of a connection object is to use a VB a function called create object so what I'm going to is I'm going to remove this subroutine that I just used to demonstrate Auto instances in variables and instead I'm gonna copy and paste this original subroutine one more time I'm going to change its names so i other words using Create object to the end so it's got a unique name and then what I'm going to do is change the word new to a call to the create object function if I open instead of round brackets after that name you'll see it's got two parameters the first compulsory parameter is called class as string so essentially this is the name of the class of object that I'm trying to create but passing is a string of text rather than a direct reference to that class of object so this code here will do essentially exactly the same job as this code here so what is the point you may ask well the point of using create object is to handle the situation where you're perhaps passing this code to somebody else who has a different version of the aid ODB object library installed in their computer if you remember earlier I headed to the Tools menu in charge of references and we checked a reference to this specific version of the ADA object library well what happens if you send this code to somebody who does not have that version installed that means that this code will not work so what I'm going to do at this point is I'm just going to uncheck that reference and click OK that means at this point neither of these two subroutines will work as soon as I attempt to run it I'll get a compile error saying that it doesn't understand what this type of thing is a Nadeau DB connection now that will be true in this case if I just reset this everything that's still true for this example at this point as well becomes trying to reference directly a Nadeau DB dog connection so instead what I'm going to do is we remove that keyword and replace it with the more generic object so what I can find now is if I attempt to remissive written using the FAQ to step through we'll find that this will work up lutely perfectly so the idea behind using create object is to make your code essentially version independent you could send this subroutine now to set it to anybody else with any other version of a dough and that would allow them to run the code one big huge downside to using this create object function this technique is called late binding by the way so late binding is when you set up when you don't set a reference to an object library you can probably guess what the other type of binding is called that's right early binding there you go so that's when you do set of reference to the object library the big downside to late binding is that as you saw earlier on we got the intellisense list to show us all the things we can do to our connection in this case with late binding you don't get any help whatsoever so for that reason for the rest of the video I'm going to be using early binding techniques while we write the code and a bit later on I'll talk about how you can convert the early bound version of your subroutine into a late bound version of this ability so that it will work for other people so I'm just going to remove that copy this everything all together for now I'm going to go back to the Tools menu and choose references recheck the reference to the Microsoft ActiveX and data objects 6.1 library there it is check the box click OK and now all the code will work again are just that that quick common there as well okay so having talked about the various ways to create a new instance of the connection we now need to set the connection string property now the connection string tells the connection essentially what kind of database to connect to where the database is stored any security parameters such passwords and so on and so on so it can be quite a long complex string of text you certainly don't want to be typing these things out yourself so where should one go for all one's connections room needs why connection strings com of course a quick Google search for connecting two things before to turn this website as the top hits every single time so if I just quickly head to this site it is the single most humourless website on the whole of the Internet I'll just warn you that now except for the name actually which is written out as a string which i think is quite cute there's a bunch of articles that explain a little bit more about how connection strings work and but I think I know about as much as I ever want to know about connection strings at the time being all you really need to do to find your connection string syntax is to look in the list on the right hand side and this is a list of all the various different types of databases you can create a connection string to talk to these are designed purely to work with the aido object libraries so if you can't find your database type on there then I'm afraid you're a little bit out of luck but we can certainly find the connection for our Access database that's what we're going to try to connect to first an Access database created in Access 2007 so if I click the access link well I'll be taking to you then is a page full of all sorts of different examples of access connection strings now there are many many many different types using various different parameters and and for various different situations you might find yourself in with your databases and there are a few descriptions about what each one is and there are quick links as well to tell you two specific versions for access as well now the type of database we're going to connect to I can just quickly show you this I'm showing you this in Access 2013 although I originally created the database in Access 2007 I'll make this available for download as well put a link in the description at the bottom of the video just in case you want to download this for practice for yourself so you can see that in Access 2007 2013 file format with an ACC DV extension so heading back to connection strings comm essentially all I need to do is copy this original standard basic copy of the connection string I don't have a password on the database and although ever did once put a password in you can see there's another example here that allows me to set a password now obviously there will be certain parts of the connection string that I'll need to change to make it specific to my particular database such as where it's stored but for now all I'm going to do is take that string select it copy it and then head back to my VB code and paste it in into that set of double quotes just after movies conduct connection string equals let's just tidy up a little bit as well as I've copied it on across two separate lines I need to make sure that it's all in one single continuous string I suppose I could concatenate it at the various different paths couldn't I to make it fit on a single screen with I'm just going to use the continuation character here just to bring this down to the next line to make it a little bit easy to read can I squeeze it all into one single line not quite I think just for the time being I'm going to leave it as this I'll just scroll across a little bit so you can see the full string but there we go there's our basic connection string in our VB code what we need to do next is change it so that will point to our specific Access database okay so I've stored my Access database in a folder on my desktop called databases conveniently and that's the path to the database so I'm going to just copy that path from a Windows Explorer window just for the sake of convenience for the time being and then that's my VB code and I'm going to replace this part of the string the connection string with the thing that I've just copied from the Windows Explorer window now I also need to reference the name of the database here as well so after the folder name databases I can type in a backslash and then the name of the database was called movies but accdb and that will generate the correct connection string to connect to my database this is hopefully obviously the biggest part that can go wrong when you're using a doe the connection string get the connection string so what I'd like to do just now is to test whether or not my connection string is working properly and to do that I'm just going to simply try to open the connection and then immediately close it back down again so to do that I can now say movies con so movies con I'll try that again movies con and dot's open there's nice simple and then movies con dot close it's always important to close down your connections once you finish with them so having done all that let me just give this quick subroutine a quick run and as long as nothing goes wrong as long as I don't get any error messages but just step through that to prove that it is actually doing something as long as you don't get any error messages that's an incredibly good sign so that means that our connection string is successfully pointing to a valid database now that we've connected to one database type I want to show you how simple it is to change a dough to essentially connect to any type of database so I've also got a copy of the movies database as a sequel server database and this is roughly what it'll look like there it is movies sitting on a specific instance of sequel server on this laptop so what I am going to do is write a connection string or more accurately copy a connection string that will connect to this specific movies database so I'm going to return back to connection strings com this will become your best friend in this website you'll have this bookmarked almost instantly I think after watching this video will then have a scroll down list to find the sequel server connection strings and I'll click on that option now there are a lot more types for sequel server an enormous list actually compared to the access list and finding the specific ones you means you need to know a little bit about sequel server itself I've got a specific instance of sequel server created so what I'm going to do is I'm going to scroll down the list the top of the list basically talks about the dotnet sequel server connection strings and I'm not connecting to a dotnet programming Internet so I'm going to scroll down the list a little bit further to find the sequel server Native Client section okay so there's a couple of basic examples again just like the waterfall access and there's also one here that talks about how to connect to a specific instance of sequel server so what I'm going to do here is copy this string again just like I did for access and again there will be a couple of small things I'll need to change so specifically the server name and the instance name and also the database name if I go back to my VB editor though having copied that string I'm just going to comment out these two lines first of all and replace these two lines with a reference to set the movies con again and connection string equals and I have another space underscore to continue writing this on the next line then I'm going to open close in double quotes paste that connection string in oops and I also need to make sure that's all fits onto one single line okay so the provider is this the provider that I copy there was for Siebel server 2012 which is the the instance that I'm I'm connecting to or the version of sequel server that I'm connecting to again there are different links for different versions of sequel server which you can see at the top of the connection strings page all I need to do though is I need to change the name of the server and the name of the instance and the name of the database so again just having a quick look back of my sequel server management studio the name of the server is the name of the laptop there inventively called dell Vostro - you'll probably work out what kind of laptop I'm using to do this and then a back slash and then the name of the instance SQL 2012 so can I copy I don't think I can copy that text from there no I can't so what is you should have to write this out so I'm going right out Dell Vostro - backslash SQL 2012 so let's do that this is the the server name essentially and the instance name is SQL 2012 the database name as we saw just quickly looking back at management studio is the name movies' again so back in the vb air to replace the word my database with the name movies' okay so once again we want to quickly test this I'm gonna use the f8 key to step through so we'll set we'll set the connection string to the sequel server connection string now and then if I try to open the connection it should successfully open as long as it doesn't fail Suns I don't get any any error messages that means that the connections worked I'll make sure to close the connection down again as well by pressing f8 again so that genuinely is how simple it is to make a don't talk to any type of database a bit later on in the video when we're talking about writing sequel instructions so actually writing out our own queries in VBA there may well be a few other tweaks that we'll need to make specific to the syntax of SQL that's being used for your specific kind of database but other than that ADA is just beautifully generic so now that we've got a way to connect two different databases well we should probably move on and start talking about how to actually get some data just before we move on I want to mention one technique you can use if you're having trouble building a connection string using connected rings comm what you can do instead of trying to construct it yourself is actually get Excel to build a valid connection string for you using one of its wizards if I head back into Excel and head on to the data tab of the ribbon there's a tool that lets me get external data and I click on this tool there are various options in here for importing data from various types of databases so of course as an access option and then there's also an option for sequel server and various other things as well if I click on the access option for now this will launch a wizard letting me import data from the Access database so let me head back to my desktop and find the databases folder and choose the movies database so if I select a table just for the sake of progressing through the wizard and click OK on this stage of the the wizard I've got option I've got a button here that allows me to view the properties of the connection that I'm creating if I click the properties button there's a definition tab on the connection properties dialog and that shows me a complete connection string successfully connect to that database now it will be enormous compared to the simple connection string that we generated using connection strings com but all we need to do here is copy the entire string from the dialog box and then close the wizard down I'm going to cancel out of both dialogs switch back to the VB editor and then I'm going to comment out the lines that connect through sequel server and have another line which sets the connection string for our movies comm so movies conduct connection string equals and let's put this on another line there's no way I'm going to get the whole thing onto one single screen with because it's so long but I'll just paste in the entire string into that set of double quotes so there it is now technically because we've copied this from a dialog box and we know that this will work to connect to our movie database technically this should just work but unfortunately if I try to run this ability now I'm using f8 to step through we'll find that when we get to the point where we open the connection it fails now the reason for that is because of exactly what text we've copied out of the connection string dialog box if I just end the subroutine the problem is all nouns at the fact that this connection string incorporates double quotes an in VBA when you want to incorporate a set of double quotes inside a string so inside a larger set of double quotes then if you put two double quotes in a row that only generates a single double quotes in here in the final string which is a bit annoying the solution to making this work is to actually double up your double double quotes so essentially you put four sets of double quotes in a row and that will put in two double quotes in the final string and the problem is there's loads of different examples of that in this entire connection string so I'm going to have to go through the entire thing by hand essentially and find every single instance of a double double quote and make them into four double quotes instead which gets a bit tedious admittedly but it's important to do otherwise the connections being simply won't work so let's keep going through sorry this bits a bit boring apologies for that there's another one I'll make sure that I get them all otherwise the whole thing will just fail I think that's looking pretty much like we're there yep there we go there's the end of the string okay so having replaced all the double double quotes with quadruple double quotes we can step through this everything again using the fa key and this time the connection opens successfully and it works if you want to use the same technique to generate a connection string for a sequel server database then the wizard is slightly different if I switch back into Excel and head back to the data tab choose get external data this I'm going to go for from other sources and then choose from sequel server now the first step of the wizard I'll have to type in the name of the server and in this case my instance name as well so if you remember my inventively named server and del Vostro - backslash SQL 2012 which is the name of the instance then I can click Next then I can choose which database on that instance or that server I want to use so I'm going to choose movies and I'm going to choose the actor table to begin with then click Next now this stage actually asked me to save the data connection file so I'm going to give my connection file a sensible name at this point so I'm going to call it movie connection sounds sensible and then I can click finish to save that file this gets me back then to the same place I was with the connection for Microsoft Access if I click the properties button and then choose definition this will give me access to the connection string again so again I can copy this and then head back to or cancel out the dialog box first and cancel out the next one back to the VB editor and again I could replace this connection string let me just fill the bad one that I just invented there and inside another set of double quotes pasted in the connection string for sequel server I think we're a bit looking in this case in that this connection string doesn't include any double quote characters so as I scroll back through the page it's a much shorter connection string as well than the one for access so with this one we don't have to make any modifications at all if I just use the f8 key to step through just to make sure that it works the connection trinket set and then if I open the connection it's open close it down again and everything works perfectly okay so that's probably enough time talking about connection strings I'm kind of starting to bore myself actually so I'm going to do is I'm going to delete the auto-generated connection string that I got from the wizard earlier on remove that altogether I'd still like to be able to switch between the different connection strings for access and sequel later on in the code just to demonstrate the differences the certain differences between these two database types so what I'd like to do is make a copy of these connection strings and store them as constants are available to this module what I'm going to do up at the top of the module just above my subroutine and below option explicit I'm going to say Const the first one I'm going to call conn stro access as a string and I'm going to make that one equal to the connection string that I've created for Microsoft Access so in fact what I'm gonna do here is just copy this connection string and place that at the end of the constant declaration there we go on the next I'm going to create another constant Const construe sequel as a string and I'm going to make this one equal to as you guessed probably the air connection string for sequel so copy and paste that one at the end so there we go so we've got these two connection strings now stored and available to the entire module I'd put a capital S in the name of that one I miss that one there I can now delete one of these two sets of lines which sets a connector shrink and then delete the line which has the connection string for access what I want to do then is make sure that this line isn't commented out anymore and what I want to set the connection string for this particular version in the subroutine I can simply call on one of my two constants I can say come Stroh I'll go for access to begin with and the next few bits of code will rights will be talking to the Microsoft Access database but now we've got a convenient way to switch between the two just by modifying a single line of code a bit later on the next task is to open up a set of records from the database load it into memory and then make that available to be pasted into Excel so to do that we're going to declare a variable that's going to hold the set of records that we open up so at the top of the subroutine I'm going to say dim movies data as an aid ODB dot records set so a record set class allows you to hold a complete set of records in memory which means are available to the rest of your sub routine just like with creating a new instance of connection we need to create a new instance of a record set so down below after we've created a new connection I'm going to say set movies data equals new adodb dot record set so that essentially creates if you like to think about it as an empty box that we can fill up with a set of records so that's the next stage how do you actually populate a record set with records from the later base populating a record set actually involves changing several properties of the record set object so we're going to do that after we've opened up our connection let me just make a quick change the way the code is laid out I'm going to take away that blank line and give ourselves a few blank lines between opening and closing the connection so obviously hopefully for obvious reasons you can only populate the record set while the connection to the database is actually open because we're going to be setting several properties of the record sets I'm gonna use a width statement so I'm gonna say with movies data spelling movies properly would help me with movies data and then a few blank lines and we're going to put in an end with statement down the end okay so the first thing we're going to specify for our record set object is which connection it should use so there's a property of a record object called active connection you might have lots and lots of different connections to different databases open in the same subroutine so we need to specify which particular connection this record set will use so we say add - connection equals then we need to simply pass in the name of our movies connection variable so that successfully tells the record set which connection to which database to use the next thing we can do is specify what the source of the record set is the source of the record set actually specifies which precise set of records will be loaded into the record set so this can be one of several different things to begin with we're going to stick to entering just table names into our record set source property so if I just quickly switch back into access and show you the names of the tables in our movies database the one we're going to go for is called TBL actor these are exactly the same as the names for sequel server as well so if I go back to the sequel server just so you can see later on when we switch to the sequel server version the same table name TBL actor is listed there as well so back to the VB editor the source for this record set in this case will be TBL actor and it is a string and that will load all the records and all the columns from that record from that table into that record set now there are a couple of slightly more complex properties of our record set that we also need to set so I've set the source the next property we're going to set is called the lock type I'm going to make this equal to the option called ad lock read-only so you're always provided with a an Intel Easons list which provides you with the illicit valid options so AV lock read-only now that's fairly descriptive it kind of says that we can only read the records meaning we can't change any data that's in the record set that gets loaded now of course there are three other choices there with interesting sounding names optimistic and pessimistic I just select ad lock read-only if you're interested in finding out a bit more about what the other options do then the best thing to do is rely on good ole Google so if you google for a doe record set or lock type and then it's probably the Microsoft developer Network link that you're interested in gives you the most technical description of the information if I head to that page this describes in horrific detail what each lock type does essentially these control exactly when records get locked when changes get made as we're not going to try to modify any data in this particular example we certainly want the read-only option about new this is the default anyway so if you don't specify this it defaults to a read-only lock type but I think it's always worthwhile being as Excel as possible when you're writing relatively complex code like this so there's the lock type set that's one more property to change the final property that we're going to change is called the cursor type and again you get a list of valid options when you type an equal sign so I've got four choices here now again probably the best way to find out the description of what these four choices mean is to rely on good or Google again so if you search for cursor type rather than lock type you'll again return on page four the Microsoft developer Network but you'll actually get a slightly better description if you use the w3schools option so on the help page it shows you a table that describes all the various cursor types now basically the cursor type describes or sets how dynamic our record set will be essentially how often our record set will go back to the original database and check for changes that have been made since the record set has been opened so this is important if you're going to open a record set perhaps when your workbook first opens up and keep that record set open through the duration of the workbook being open if you think about other users perhaps changing data in the database you'll probably want to make sure that your cursor continues or your record set keeps going back to check if things have changed if you're going to do that then the dynamic cursor type is the most sensible ones use it detects all additions changes and deletions by other users of course that comes at some sort of expense in terms of performance so if your cursor of your records there he has to keep on going back to check the original data then that's going to slow down the performance of your code the opposite end of the spectrum then is the static record set so that opens up essentially like a read-only copy of data into memory and it will never go back to check if anything changes there's actually even a slightly more efficient version of that the ad open forward only cursor which is exactly the same as a static one except that we can only move forward through our record set and that's we're not going to be moving through our record set at all for this simple example this is the one we're going to use this is the most efficient one in terms of performance so back to the VB editor and now that we know that we can use the basic forward only cursor type I can press control in space again and choose ad open forward only and that's all the properties of our records set defined all we've got to do now is specify that we want to open up the record set and read some data from it now opening and closing the record set is just as simple as opening and closing a connection so after we've settled it all of its properties we can simply say dot open to open the record set and load the data into memory and then further on down the subroutine after we've finished with the record set so I'm going to give myself a couple of blank lines we can say movies data dot close and it's as simple as that make sure that you close a record set before you close the connection so that's all the code that we need to actually open and close the connection to the database and load a set of data we should quickly give this on a quick test to to make sure that all the things will actually work before we try to read data from it some use the f8 key to step through this ability just make sure that essentially you don't generate any runtime errors and the most important lines of course as we saw with it when we try to open the connection to the database and now again when we try to open the record set itself so that seems to avert a plea make sure that you do close those objects down again and then we can work out how to get the data from the record set into cells on an Excel worksheet reading data from a NATO record set into cells on an Excel worksheet is actually really straightforward because Excel VBA provides us with a native method that allows you to do that very easily so we want to make sure that we create a new worksheet that will hold our new data so I'm going to insert a worksheet into the worksheets collection as a worksheets not add I'm not particularly bothered about exactly where the work she goes so I'm just going to use the add method without specifying any of its parameters there and after we've done that I want to copy the data from our record set into cell or starting at cell a2 so the reason I'm choosing range a2 which you'll see shortly in the middle I'm going to say range a to dot and then there's a beautiful method called copy from record set and this method is designed exactly for copying data from a NATO record set and so if I type in a space afterward you can see there's a single compulsory promoter called data as unknown the what it actually wants is the name of recordset object so we can simply pass in our movies data record set there are a couple of extra optional parameters there as well macros and max columns which I'll come back to shortly but for now all I would like to do is run this subroutine to make sure that it actually generates a new copy of a worksheet reading data from our Access database just to go back into Excel quickly first of all you can see that I've got three worksheets which are pretty much empty there's a bit of data on sheet three so we're going to insert a new worksheet which will end up to the left of sheet one with a copy of all the actors from our database so I switch back into the VB editor and simply run the subroutine run fairly quickly back to excel there's our list of data from the movies database now obviously there's a few slightly rubbish things about the data that's been generated first of all we don't get the column headings in Row one so we need to talk that out secondly the third column a column C is actually meant to be the birth date of our actors the formatting of the birth date hasn't been transferred to all we got is a row underlying value of the date we could just form a lot ourselves manually in Excel but we'd like to make that happen in codes we're going to add a bit of code that will do that as well I guess one more small British thing is that the column widths aren't set correctly so we want to make sure perhaps that the column widths get set we can solve all of those things with a fairly simple bit of code and that we can add to our subroutine later on before we do all that as you want to make sure and reassure you that this code will work perfectly for a sequel server database without changing much at all apart from the connection string so if I go back to the VB editor again and I'm going to replace our access connection string with a reference to our sequel connection string instead so remove access and instead use our sequel connection string and if I run this subroutine having changed just that single piece of information what we ought to find is that again it runs fairly quickly if I switch back to excel I've got another worksheet with another set of data stored they're slightly different data stored in the sequel server version of the database compare to the access version of the databases in a slightly different order but the important thing is we've got all the same data just by changing that single bit of information the other really cool thing as well is that if you know the names of the other tables in your database then you can change the data that you get access to just by changing the name of the table so there's a table called TBL director and if I run that subroutine now that will read all the directed data from the movies database or the sequel server version of the movies database and if I go back to the VB editor again and switch the connection string back to an Access connection string that will read all of the directed data from the access version of the database into yet another new virtually again the order of the records is slightly different and the actual information in our two different databases is somewhat different the important thing is that we're connecting two completely different types of databases and opening completely different tables just by editing a couple of simple strings of text now I'd like to make this subroutine a little bit better by adding in the column headings from the data set that we open up so to do that first of all I'm going to go back into Excel and I'm going to delete all of the extra worksheets that we added in earlier on so I'm going to select sheet 8 in my case and then hold down the shift key I'm cooking sheet 5 and then right click and choose to delete all of them click delete just to tidy up a little bit now back in the VBA editor we're going to write another little section of code that is going to loop over the fields collection of our movies data record set and list out the name of each field at the top of the new worksheet you would create looping over the fields collection of a record set is just like looping over any collection of objects in VBA we'll need a variable which can hold a single instance of the type of object whose collection will looping over so I'm going to go to the top I'm gonna say dim movie or movies field as what I'd really like to say here is a DB dot field but slightly oddly you won't see the field keyword appear in the intellisense list it used to in previous versions of the arrow object library but it seemed to have been deprecated I'm not quite sure why anyway I'm gonna ignore the advice of the intellisense list I'm just gonna hit escape here and you will see that if I type in the word field with a lowercase F if I click on the next line it does still recognize that there is a definition for the field class so we're kind of cheating a little bit here and using what could well be an unsupported class these days but I'm gonna use it anyway I know this will work for these examples so there's our variables will hold a single instance of a field object what we can then do is after we've added the new worksheet we're going to write a little loop here before we take the data from the record set and place it in to those columns on the spreadsheet so after we've added the worksheet we're going to say for each and I'm going to refer to the variable we've just declared movies field in movies data dot fields you'll see that the fields property is listed for for a recordset object which returns a collection of field objects again why the field class isn't listed in the intellisense anymore I'm not entirely certain but if we write this loop and then we'll say well closely by saying next movies field and we'll do this in a nice simple way we know that when we insert a new worksheet we have range a-1 selected so I'm going to say active cell dot value equals movies field dot name so you see we get the complete intellisense for all the properties of a field object so the name of course is the name of the column then we're simply going to say to move one column to the right so we can say active cell dot offset 0 comma 1 dot select once we've finished looping I'm also going to go back and make sure that we select range a1 again so if we have a particularly large data set that has lots and lots of columns we don't want to end up all the way up the right-hand edge of the worksheet so I'm going to say range a1 dot select just before we then copy the data from the record set and there's our code written out so if we test this one again this time I'll stick with the Access database and the director table if I run it have a quick look back into Excel now we get a version of the record set with all the column names listed out and the dates have been formatted correctly as well as if by magic if I just quickly switch back to the VB editor and do a couple of other things let's change to a sequel characters ring and also change the name of the table we're using for the actor table rather than direct stable and then run it one more time and just to prove that this will work again with a new set of records I just realized that I've got a slightly different version of the outer table with an extra column in there which isn't populated for many adapters the date on which the actor died if they have died yes this is for a slightly different version of the database there's not much data that's in there actually there's one there's one quick test version with a date of death so um there's a quick example of how you can loop over the collection of fields of a record set and list out their names to populate the column headings one more change that it might be nice to make would be to make sure that all the column widths end up in the correct size once we finished copying the data from the record set so we can do that very very simply by adding a single line after we've finished copying from the record set we can simply say something like range a1 dots current region dot entire column dot auto fit so that will apply the auto fit method to the original data that we've just pasted so if I give this one one more test we'll stick with the same local set in the same day actually I'm gonna switch back to the Access database not that it really matters just for this a quick example and then if I run this everything again will find if I switch back to excel we've got now a nicely widened column with the correct data and the formatted and the column headings I can't get much better sip rating than that just before we go any further in terms of talking about more complex things we can do without our record sets I want to return to an idea I mentioned earlier on in the video which is a way to make this subroutine work for the garlis of which version of a doe the user is running on their computer so if you remember we looked at the idea of the create object function and a technique called late binding what I'm going to do is create a quick copy this entire subroutine and paste it in just below the existing one and I'm going to modify its name so that it's called create copy data from the database late binding now when you want to convert your subroutine into a late bound method there's three main things you'll need to change first of all any reference to a specific class of object from a specific object library needs to be converted to the word object so I'm gonna start by doing that now I'm going to change a doe DB connection as object adodb record set as object and a doe DB dot field as object the second thing that you'll also need to change is the keyword new needs to be converted into a call to the create object function so replace the keyword new there with a reference to create object open some round brackets and then the class of object you're trying to create must be converted into a string so just wrap that up instead of double quotes and closer parenthesis and we'll do the same thing for movies data change the word new to a call to the create object function there is one more thing that we'll also need to change which is a little bit less obvious anywhere where we have referenced any constants that belong to the a do object library so things like ad lock read-only you remember we saw this convenient list of options available to us when we set the lock type and likewise with the cursor type now these constants are convenient keywords that actually represent numbers and these keywords only means something as long as we have a reference to the aido library set so if I when we to find out what the underlying values of these constants are is to use the f8 key to begin stepping through the subroutine if I now hover the mouse over each of these constants it will tell you what the underlying value is so 80 locked read-only is equal to 1 and 80 open forward only is equal to 0 so we'll also need to do is convert these into their underlying numbers to replace read-only with number 1 and forward only with the number 0 I think that's everything that we need to do to make this a late bound version of this ability so if I just reset the subroutine at this point what we can do is attempt to test it and the only way we'll know that this works for sure is if we first of all remove a reference to the Aido object library so let's remove that reference from tools references and click OK if I attempt to run the first 17 now it will fail immediately because it will not understand what an 800 DB connection is so I can click OK and reset this ability if I try to run the light binding version what we should find is that it works in exactly the same way as it did before but now without relying on the fact that we have to have a reference to the Edo object library set so you could send this ability in this project to anybody with any version Vado and it will still work now as i mentioned earlier on the disadvantage of using late binding is you have absolutely no link or no option to use the intellisense if I try to refer to one of my variables like movies comm and typing a full stop I don't get any intellisense whatsoever so for that reason for the rest of the video I'm going to revert back to using early binding that means I'm going to remove this subroutine altogether so let's get rid of that and I'm going to go back and check the reference to the a do object library so tools references scroll through the list again to find a Microsoft ActiveX data objects and check the reference again to make sure that everything works ok so now that we've cleared all that up let's look at some more complex things we can do to set the source of our adodb record sets now before we start talking about more complex record sets I want to stop and think a little bit about adding some error handling to this editing some of the things that we're going to do with our record set source are going to make the program fail when we try to open the record set I'm gonna do that deliberately by the way I'm not just pre-empting my own silly mistakes so because I'm trying to demonstrate some explicit runtime errors when we try to modify the source of our record set we don't want to be left in a situation where our movies connection is still open so we're going to add a little bit of error handling code so that after we've opened up the connections you don't even go wrong after this point then at least the connection will still be closed down so we've covered our handling in a lot more detail in a previous video in this series what I'm going to do is after the connection has been successfully opened I'm going to add an on error statement which says go to close connection what I then need to do is create that label in my code so I'm just going to copy that keyword close connection that I've just amended scroll down to the bottom of the sibley team and just above the line which actually does close the connection to the movie database naturally I'm going to paste in that keyword that I've just invented again this isn't a built-in vba key word by the way this is just something a label that I've invented this could be anything at all essentially if I type in a colon after his name then mutant next line you will outdent itself to the left-hand edge so after this point in my subroutine if anything goes wrong at all my subroutine will immediately jump to this label and then run any code through to the end of the subroutine so that will successfully make sure that we always close down the movies movies connection or the connections in the movie database we should also do something similar after we've opened up the movies data record set why not if we fail after opening up the record set anywhere between here and here then the movies data record set is left open as well so what we can do is immediately after we've opened up the record set we can write another on error statement that says on error go to then I'll create a new label called closed record set so again I can copy that label and paste it in just above the line which does actually close the record set type in the colum and there it goes so after this point in the subroutine if anything goes wrong between here and here then the sublet team will immediately jump down to this section and close down the connection from this point in the subroutine onwards if anything goes wrong the subroutine will immediately jump to this label and it will remove data close and then movies connection closed so this on our estate will make sure that both the connects or the record set and the connection get closed what we would ordinarily do as well is after we have finished with our custom error handler is we would switch the error handler back to normal error handling status and we do that by saying on error go to zero so that line simply resets the error handler that will make sure that if we run through this is the program naturally then after this line of code everything else will happen according to normal error handling routines there's not particularly important in this case because we're only trying to close down the record set and close down the connection but just as it's good practice I'm going to put that in there as well so now that we should have some a bit of a safety net when things go wrong let's start trying to modify the source for our record set now the really cool thing about the source property of a record set is that as well as just being able to pass in the name of a table you can actually replace this with a complete SQL select statement so essentially you can write your query as a string of text directly into your VBA code now I've made a complete series of videos on how to write sequel queries which I'm not going to recommend you go away and watch the whole thing at this point so I'm just going to cover a few the basic things from that what we'll do to start with is for a place their call to the simple table name with a query that will essentially do exactly the same job so instead of just saying TBL actor what we would do is say select star or asterisk from TBL actor I'm also going to add in a semicolon at the end of the table name now in both access and sequel server the semicolon is an optional character it's entered the statement terminator it's completely optional as I say in both access on the sequel server but in other database engines the semicolon is required so I'll add it in here for the sake of completeness now if I run this subroutine we'll end up with exactly the same set of results as though we had just used the name TBL actor just to quickly prove that I'll run the subregime and then have a quick look back in Excel and there's a new sheet with the full list of records from that table so obviously that's a bit more work to essentially get exactly the same results as just putting in the name of the table but the beauty of writing the Select statement is that was much more power in what you can return so for the next example let's say that we only want to return a set of columns from our table rather than all of them this means that we need to know a little bit about the names of the fields in that record set so I switch back to excel as we listed out the field names earlier we can see what those are so I only want to include the actor name act or do be an actor gender fields in the next query if I head back to the VB editor to do that we just need to replace the asterisk with a comma separated list of the field names now these aren't case sensitive SQL is a case insensitive language but I'm quite pedantic about matching case I'm going to make sure that the casing is exactly the same as it's listed on my worksheet but as I say this is completely unimportant the whole thing can be written in all capital letters or all lowercase letters and it genuinely won't make a difference so having done that if I run this opportunity s another new worksheet and this time it will have just the three columns that we specified what I'd like to do next is sort the results of the query so that they come out in alphabetical order of the actor's name so to do that I can add an order by Clause the end of the select statement so after the from TBL actor before the semicolon I can say order by two separate words then the name of the fields I want to order by so I can say act or actual name it's important to note that you don't need to have included this field in the Select list so I could sort by actor ID even though it doesn't belong to the Select list because it does belong to the table in the from clause so I'm going to order by app to name that will actually sort in ascending order automatically you can optionally add the letters a SC to sort in ascending order and de SC for descending order obviously I'm going to place it with a SC and then if I run the server tune again I'll end up with yet another um output but this time the actors names are listed in alphabetical order so hopefully you can see from this that it's fairly easy to write complete SQL queries directly into your VBA code is just a of literal text it's worthwhile mentioning that this works happily for Microsoft Access and also sequel server so if I change my connection string and then the civil attune again once again we'll end up with another list of values from the sequel database rather than access one so so far everything works in ways regards whether you're working in Access or sequel server now writing out your SQL select statements directly into your VBA code is a really powerful technique but of course it does rely on you knowing quite a lot about how to write SQL in the first place which of course I suppose if you've already watched my video series on how to do that you will have any trouble with whatsoever however if you're more of a VBA developer than a sequel developer then under something select statements and how to write them can be a bit of a challenge so I'm going to show you a quick way that you can get your select segment to be generated automatically which we can then just copy directly into your VBA code as we were looking at an Access database we can use Microsoft Access to do this I'm going to switch back into my database for Microsoft Access what I'm going to do is I'm going to create a brand new query using the create tab in the ribbon and choosing query design when I do that I can get to choose which tables I want to include in my query and go for a fairly complex query so I'm going to involve more than one table I'm going Surt the director table and the film table and then close that window down I'm going to include a few fields from now so I'm gonna go for the director name and let's have the film name the film at least eight maps the film run time in minutes as well then I think I'll sort my query by the director name as well so I'm gonna sort in ascending order of director name and there's a query which if I were to run it I'd see a list of results but I want to get into my Excel spreadsheet so if I go back to the design view in Access you can actually see the SQL that's been generated by your query designer by changing the view to the SQL view so you switch into this you'll get a very fairly fairly horrible looking set of codes not laid out particularly neatly or formatted very well but what you can rely on is that if you simply call P this entire set of text to the clipboard hair back to the VB editor and replace the current source so I'm gonna news is remove everything there between my mandible goats I'm going to then simply paste in what I've just copied that will mean as well of course that I've got to put all this into a single line making sure I've got correct spacing as well okay so there's the complete select statement written out as a single line of text including some join code which is always a bit awkward to write out yourself anyway if I then simply run this subroutine what I'll see back in Excel is the results of that query so the Raptors and films sorted in ascending order of director name with the film release date and the film more than 10 minutes as well so that's priority needs as well the fact that you can actually get access to write out your SQL code for you now you can use a very similar technique in the sequel server to get your SQL queries to be generated for you so if I switch back into sequel server management studio making sure that I've got the movies database selected I'm going to choose to create a new query and all you get then is basically a blank page and sequel expects you to write out your queries from scratch by hand but we're not going to do that we're going to cheat we're going to right click in the background of the query page and choose design and query in editor and if I do that it launches the query designer which is very similar to accesses query designer let's generate a different query so I'm going to include the app to table the cast table and the film table then I can close down that window and I get access to all three tables involved in the query let me just rearrange things a little bit just to make things a little bit more clear what I'm going to do is start checking boxes for the things I want seeing don't click the film name the actor name and then feel called cast character name so that shows me there then the name of the character that each actor played in each film again you can see that the UM the Select statement has been generated for us automatically in the background if I want to apply a sort I can do that as well I'm going to sort by film name perhaps so sort in ascending order film name and then the abs order by clause all I need to do now is select all this text once again copy it to the clipboard head back into VB and then replace this version of the query with the new one that I've just copied so again in SF double quotes I can paste in the query that I've just copied I'll need to do a little bit more tidying up than usual so the queries written across several different lines so I need to backspace off all the air the line breaks to make sure this comes out as a single query I could also just I suppose concatenate the various different lines together primarily this way now that I've started so eventually we'll get to the point where the entire query is written out as a single line enclosed in one single set of double quotes so the spacing doesn't actually matter but I want to tidy up a little bit so we go open double quotes there and then right at the end of line close double quotes so if I run this subroutine oh I'm going to make sure that I'm going to use the sequel connection as well rather than the access connection so make sure that I'm using this equal connection string and then run the subroutine one more time and back in Excel I've got the list of film names the actors who played a role in them and which character they actually played so that's probably the most convenient way to get your sequel queries to be written out for you automatically of course if you know sequel quite well already you're welcome to write out your your queries yourself by hand but at least you've now got a choice for the different ways to do things now unfortunately you can't always rely on the query designers to return a valid SQL string that will work with an a doe connection and that's especially true when you're using a connection to an Access database and you're trying to add criteria to your query using the where clause so I've reverted to using the access connection string here and for the moment I'm just going to replace my entire selection with a set of double quotes so I'm going to paste in a new version of my query in just a moment into that section I'm going to use the query designer and access generate a really simple query so heading back to access I'm going to create a new query in design view and I'm going to include just a single table the film table then I'm going to include a single field the film name field and then I'm going to add a simple criteria that will show me all the films whose name begins with letter A to do that I'm simply going to add an expression to the criteria row of the film name column and the expression is going to be a followed by an asterisk so in Access the asterisk is one of the wildcard characters and it represents any number of any characters and when I hit enter here to enter the expression it will be changed ever so slightly to add the word alike and then enclose a asterisk in a set of double quotes so if I run the query I'll get a list of films whose name begins with letter A and if I look at the SQL view that will give me the SQL select statement that generates outside a result in Access now I'm going to copy this query just as I did last time and I'm going to paste it into my VBA code inside that set of double quotes and again I'm gonna have to tidy up by changing everything go okay with the line break so that everything fits onto a single line but I've got a small problem with this at the moment if I click away from this line I'll generate a syntax error and the reason for the syntax error is that I've got mismatched sets of double quote characters in my string so just like we saw last time with the connection string if I wanted to replace the double quotes one thing I could do is essentially double up the double close so I put double Kosovo quotes a asterisk double quotes double quotes that will solve the syntax error now that's one solution but there's actually a better solution for SQL select statements rather than using double quotes to enclose text SQL actually prefers single quotes in sequel server in fact you have to apply a special setting to that to the server to allow you to use double quotes to delimit text single quotes are the preferred way of doing things in SQL so having done that we've got a valid or at least no syntax errors so we've got what looks like a valid select statement however if I run this subroutine and have a look back at the the Excel spreadsheet what I'm going to end up with is essentially a blank worksheet it's got the film name column clearly so it's actually successfully executed a query it just hasn't returned any results and that's a little bit strange now the reason for all that is just in the same way that the double quotes are a microsoft access way of delimiting text sequel prefers single quotes the wildcard carriage that we've used the asterisk isn't actually the way to do things in SQL instead of an asterisk the wildcard for SQL is actually a percentage symbol so if I replace the asterisk with a percent and then run this subroutine again when I look back at Excel again now I will see a list of the films that I expect to get so this is one of the slightly odd things about about using a doe even if you're connecting to an Access database you're not using exact the same dialect of SQL to talk to access when you're using a doe in Access if I try to replace the asterisk with a percentage symbol and then look at the M the result of the query I don't return anything and that's the opposite is the case when you a doe so an ADA you must use a percentage symbol in access you must use an asterisk so that's some complication you're going to run into something you must know I suppose you just have to know this and make sure that you handle this by writing out the correct wildcard characters in your queries now you also have to be quite careful when you try to apply criteria to date fields in your select statements so I'm going to switch back to access for the moment and I'm going to add in the film release date field into my query I'll remove the criteria from the film name field and instead I'm lastest show films whose release date is greater than the 31st of the twelve 1999 so essentially I can assure us any films released from the first of January 2000 so when I enter that criterion again a couple of changes get made for me so I get hash marks and closing the date in Access and if I run the query I'll get a long list of films whose release dates are all after the 1990s so since the year 2000 and if I head to the SQL view I'll get sat the query written out for me and again I'm just going to copy this query to the clipboard head back to the VB editor and then I'm going to paste in that query in place of the one that I've got currently so I'm going to replace everything there with the new query that I've just copied a quick bit of tidying up again so take everything onto a single line make sure the spacing between the the words that I need and there's the basic query now one thing you may have noticed as I was tidying on my select statement is that access has automatically switched the positions of the day and the month in my date so whereas I wrote out 31/12 1999 day month year because that's the correct way of doing things that's how we do things in the UK you can see the access is actually converted it to us format so by default dates are treated in u.s. format in SQL and because the thirty-first can only possibly be the day of the month access converted to month day yeah now as you'll see if I go back to access you can see that it's actually changed at in the SQL view of my query but switch back to design view it's still shown in UK format 31/12 now that's absolutely financially in this particular case if I were to run this subroutine Mac in the VB edit if I run it I will end up with a list of films released after the date that I've specified if I headed back to the VB editor again and if I flipped around the day in the month to put it in the correct position 31/12 then if I run the subroutine again I'll get exactly the same list of films so if I switch between these two worksheets have been generated you can see clearly that I've got the same list of results now that works absolutely perfectly because the thirty-first can only possibly be interpreted as the day of the month it can't possibly be the month of the year if however I was looking for a specific dates let's say I'm going to pick the 3rd of the 8th 2007 so the 3rd of August 2700 represented here if I switch back to the VB editor I'm going to modify my criteria so it searches for the film that was released on an exact date so rather than greater than it's going to be equal to I'm going to try to enter this as 0 3 forward slash 0 8 2007 okay so if i run this subroutine again now then if i switch back to excel I'll see I sin I essentially own it with a blank sheet I get the column headings but there are no films who released a match the date I try to type in and of course that's because back in the VB editor because the 3rd and the 8th could both be either the month or the day SQL treats this as month day year so March the 8th not 3rd of August so one thing to remember is if you're entering dates in SQL and you want to type these out yourself manually then either remember that it's American format us format month day year I never run this up listening M I will actually return the result I'm looking for alternatively what you can do is use a different format all together the international standard way of entering dates in computing there is actually an international standard of what you believe for entering dates and competing so the way you do this in in the ISO 8601 standard is you put the Year first then a - then the month 0-8 then another and then the day so 2007 August 3rd um if I run this ability in again one more time we will see that we get the correct film so we've got that twice then there's a blank one then there's its two original ones so ISO 8601 is actually a sensible idea for entering dates there's a nice Wikipedia entry actually about ISO 8601 home which you're welcome to read about of your if you're interested otherwise this is this is a nice reliable way of entering date information in queries in SQL now dates in sequel server work in almost exactly the same way as they do in Access except for one very important difference it's going to change by connecting string to using the sequel connection string again rather than the access one and I'm going to run this ability in again from start in fact I'm going to use the FAQ to step through this will make it easier to see what's going to happen so I use the effect you to begin stepping through I'll get to the point where I set my source for my record set and there it goes so that incorporates the the date as we've just left it so in ISO 8601 format and closed in a set of hash marks if I carry on using the f8 key to step through what you'll see that happens when I hit the open line is that instead of actually opening the record set we jump to the close connection label so that must mean that something has gone horribly wrong we've generated a runtime error and my error handler has made sure that we've jumped to the close connection label it's going to run that I'm going to make sure the connection does get closed using their fakey and then the subroutine now the problem with the dates as we've entered it not the date that we've entered into our criterion is that in single server you do not use hash marks to enclose a date instead you use the same operator that you use to enclose text and that is a single quote so if I change that so that my date is enclosed in a set of single quotes and then if I run the subroutine one more time using the f8 key just to show you that it will pass through in properly open the record set this time so and I hit open this time the record set does get opened and then we add the new worksheet and add in all the data and then the subroutine ends so going back to this especially now we'll see and yes another worksheet which has the same film as we saw twice previously for the Access database and then there's still the missing one so this is the new new query that's generated using sequel server so that's one massively important difference with Bateson sequel server rather than using hash marks to include a dates use single quotes instead as one final idea for this video I want to talk about how you can actually get your users to influence the data source that's created so we can ask the user to provide some input which will affect the number of records that we've returned from this query to make this work what I'm going to show you first of all what the query itself would look like I'm going to go back into access quickly and I'm going to show you that I've got a basic query here with a film name film release date and film then time minutes what I'd like to do is show a query or show set of results where the film will in time minutes falls between two numbers so the the criteria that can wait to do that involves the between keyword so excite between and let's just say for example 100 and 120 I think I'm gonna add an order by clause as well sort the results so you can clearly see the results come out in there in the range that I've specified so having tuned all that if I run the query you'll see that I get a list of results whether lowest film run time is 100 and the highest is 120 but I might want to change that range of numbers each time I run the query so I'd like the user to provide the values for the number 100 and the number 120 so I'm not going to copy this query just yet what I'm going to do first is head back to the VB editor and because this or generating this select statement is gonna be a bit more complex what I'm going to do is write a separate function that is going to return the correct result so down at the bottom of this module I'm going to create a new function called get SQL string and it's not going to have any parameters but it is going to return a string of text so there's the definition of the function let's start writing the code that will actually return the right result so I'm going to ask the user to provide two separate inputs for this function to return the correct sequel string so I'm going to give myself a couple of variables dim min length as integer comma max length as integer and then I'm going to ask the user to provide values for those by displaying input boxes so I can say min length equals application dots in by box I'm using applications or input box rather than input box because with application dot impacts I can restrict the return type to a number so I'm going to say the called the prompt or set the prompt parameter to say enter the shortest run time and then the type program exists if I type in a comma and say type colon equals I can set this to a value that make sure I can only return a number from the in the box if you want to see what value I have to enter here I can click on the word input box and press f1 on the keyboard to launch the context-sensitive help page and in Excel 2013 this actually goes into the default web browser to show you the results but what you can see here is it if I want to return a number from this inbox I enter the value 1 for the type parameter so heading back to the VB editor I'd say type equals 1 and that make sure that I can only return a number or enter a number into that input box I can do exactly the same thing for max length so to save a bit of typing I'm going to copy that line paste it in change my variable name to max length and then change the word short to the little long so into the longest run time and the type will also be equal to 1 so now that will store the two values that my user has entered I need to incorporate those into the Select statement I'm going to build my select statement in a string variable before I return the final result to the function so at the top I'm going to declare another variable called I'm gonna call it SQL string as a string and then a bit further down I can start building that up by saying things like SQL string equals now I could just copy and paste this from my access query but I feel like typing this one out by hands I'm going to say equals select film name comma film release date comment I want to make sure I get the casing right not that such the important but I'm gonna do anyway film run time minutes and then a space and I'm going to use an ampersand and space underscore to carry on typing this line on the next line of code in the editor so now I can just open up another set of double quotes and say from it's important I've got a space after the word film one time minutes here by the way so there must be a space here otherwise the word film Ensign minutes would be attached to the word from and that would cause them an arrow when I try to open up the the record set so making sure you've got spacing in the correct place this is why might be useful to copy and paste from the access query however I'm going to persevere I'm going to say from TBL film and then make sure there's another space after the word TBL film before I close the double quotes and another ampersand space underscore and then we're film run time minutes between and this is the point at which I want to incorporate the first user inverse I'm going to use ampersand I'm going to say min length then another ampersand space underscore open up some multiple goats I'm going to start this line with a space so that I get a space after the value that's been entered into the min local parameter so I can say space and and then another space close evil quotes another ampersand I'm going to join on the result of the max length variable what I can then do is join on the order by Clause at the end so I'm going to say and space underscore making sure there's a space after the max length value and say order by film run time minutes 80s see and close out with a semicolon whew so when I hit enter now as long as I don't entire second encoders in term red that looks pretty good and that will return then oh sorry it doesn't return yet if I say then get SQL string guitar string equals SQL string then that function will now return the correct statement that can add to my subroutine so all I have to do is head back up to the original stability and replace my source takeaway that entire string of text and replace that with a call to might get SQL string function so having done all that if I just saved this ability in or so the code that I've written so far and then run the whole thing scratch I get to enter the shortest run time I have to enter a number if I try to enter text and click OK it won't work I get a nice bit of validation so let me replace that with a number for the number 90 this time then I can click OK again I'm going to the value 100 is longest run time when I click OK when I switch back into Excel I ought to end up with a list of films whose running time is between 90 and 100 fantastic so if I go back to the VBA editor I'm running one more time let's go for the slightly longer range it's only off of 190 ok and the value 200 perhaps click OK again and now when I switch back to excel I get a list of the longest films so hopefully get that gives you a few ideas about how you can let the users now influence the results of your select statements really the technique is just concatenate in strings of text together making sure that the user enters valid values that can be incorporated into your where clauses obviously knowing a bit about SQL queries helps a lot hence I'm going to mention once again the previous series of videos that we've created on writing SQL queries but it's a nice way to get your users to generate unique sets of results from databases so I think that's about as far as we're going to go with this particular video for getting data from a database in the next part of this series we're going to talk about how you can write similar code to actually start modifying the data that's in a database so in terms of deleting records from tables and then updating existing records as well so for the time being hope you found this video useful and thanks for watching if you've enjoyed this training video you can find many more online training resources at ww-why Zelko UK

Channel: WiseOwlTutorials

Views: 166,590

Rating: 4.9438376 out of 5

Keywords: ActiveX Data Objects, Microsoft Excel (Software), Visual Basic For Applications, Database (File Format Genre), Microsoft Access (Software), Microsoft SQL Server (Database Management System), wise owl, ado

Id: HE9CIbetNnI

Length: 82min 6sec (4926 seconds)

Published: Mon Mar 31 2014