Excel VBA Introduction Part 32 - ADO (ActiveX Data Objects) SQL Statements (INSERT, UPDATE, DELETE)

welcome to this YSL tutorial in this video we're going to talk about writing SQL statements using ActiveX data objects in Excel VBA so the video is all about how you use the a do command object in VBA to essentially write valid SQL statements which can be executed on database we'll start with a quick recap of how ActiveX data objects and connections work and then show you how you can create a new command object and show you a few examples of SQL statements that were going to be using in this video so we're going to talk about the insert statement for adding records update for modifying records and delete for obviously deleting records will show you how you can set the command text property of your command object to be a valid SQL statement and then how to execute the command on the database to perform its action we'll show you a couple of quick ways to generate your SQL statements automatically so if you're more of a VBA programming than a sequel developer there are ways to get your SQL to be written for you which is always nice we'll then talk about how you integrate your sequel statements with cell contents from an Excel spreadsheet so essentially concatenated your SQL statements together to create a form of dynamic SQL will then mention some of the dangers of dynamic SQL and how you can get around some of those dangers before the last part of the video is going to start talking about how you transactions to give you the ability to undo any changes you make with your SQL commands and also then how you can update and delete data for the final couple of examples so there's plenty to do as always let's get started in the previous two videos in this series we've used a do record set objects to get data from a database but also to be able to modify that data as well in this video we're going to avoid using record sets altogether instead we're going to use something called an a do command object and essentially this lets you write SQL statements to wrap the into your VBA code which can then be executed on the database that you're connected to there's pretty powerful stuff so we're going to start with a quick reminder of how to connect to a database in the first place I'm not going to spend too much time on this as we've done in the previous two videos already but back to the VBA editor and I've already got a module for myself I'm going to put a quick new subroutine called connect to DB and then a quick reminder of how to create connection objects the first job is to declare a variable which can hold a reference to our connection object and as ever there are two different ways that you can do this you have to use early binding or late binding there's a late binding method I'm going to say dim movies Khan as an object what I can then do is say set movies con equal to the results of the create object function and I'm going to ask to create a class called an aid ODB dot connection what I can then do is I can set properties and use methods of that connection object if I say movies come not but then with late binding you essentially have to either know or guess every single technique that you want to use so that makes it quite inconvenient as a developer so instead let's use the early binding method that involves going to the Tools menu and choosing references and then setting a reference to the correct object library which contains a definition this aid ODB connection object so we're looking for a library called the Microsoft ActiveX data objects library and as ever there will be several different versions installed on your machine just go with the latest version that you have available that's the most sensible choice so in my case it's 6.1 I'm running on Windows 7 if you click OK you can then replace the word object with a Dodi be dots connection it actually is part of the intellisense now you can actually leave the create object function in there you can use early binding with the create object function quite happily but I would prefer to use the new keyword instead so instead of just saying create object when there's a new aid ODB block connection and once again you can see that it's in the intellisense list the big advantage of early binding is that it gives you access to the intellisense for all the methods and properties of that connection object the downside is that it's version specific so if I check a reference to the 6.18 o library that may not work in somebody else's machine whereas the late binding technique is more version independent so having done all that the next job is to set the connection string property of this connection object to make sure that it points to the correct database now we're going to connect to two different types of databases in this video first of all a Microsoft Access database called movies sitting in a folder on my desktop and it's in the 2007-2013 file format and we're also going to connect to a sequel server database also called movies and that's installed in a named instance of sequel server 2012 so we need to generate connection strings for both of those databases and by far the most convenient way to do that is using the world-famous connection strings com website and if you haven't already bookmark this then you should so let's look for the access connection string first of all I'll click the access link here in the connect section and when the page is finally loaded what I'm going to do is scroll down the list to find I'd have to scroll to Vava this actually the actual connection during our wand is sitting fairly close to the top of the list here for access 2007 to 2013 so this connection string here is valid for all these different versions of access you have earlier versions of access then you're welcome to click on these legacy links here and I'm just going to select and copy all of that text head back to the VB editor I'm going to create a constant for this connection string so I can quickly switch between them later on in the video so I'm going to declare a Const access con stro as string equals and then inside a set of double quotes there pacing the connection string I've just copied now I need to do a little bit of tidying up so let's take this back to a single line and of course I'll also need to modify the source so rather than point this generic placeholder name I want to point to my specific folder and database names I'm going to use a Windows Explorer window to conveniently copy this folder path and then paste that into the appropriate place in the connection string then I'll also need to type in a backslash and then type in the name of the database movies it's not case-sensitive but I can't live with myself if I don't put in a capital M there we go so movies ACC DV right there's nothing else that I need to change about that connection shrink so that's the access one dealt with let's do a similar thing for the sequel server connection as well so const SQL on Stroh as string equals open and close double quotes and then we can go back to connection strings comm go back to the main page scroll down to find the sequel server connections with the page to load there's quite a lot more options available for sequel server so I'm going to scroll quite a long way down this page I'm interested in sequel server 2012 which internally is known as sequel 11 or the native client is known as sequel 11 so there it is I'm connecting to a named instance of sequel server so I'm going to use this option here so I'm going to copy that text again nice and simple back to the VBA editor pasting the connection string into those double quotes and do a bit of tidying up so take it back to a single line and then I've got two different things to change first of all I've got to change the server name which in my case is inventively called del love Vostro 2 then the instance name is SQL 2012 then the database name movies' so just to show you how all that links together with a sequel server management studio you can see that the name of the server and the name of the instance is listed here and then I'm in the database as I said was movies so that's all the connection strings dealt with what we can do now is choose one of the two connection strings to work with I'm going to choose the access connection string first of all so I'm going to set the connection strings my access constraint just as a quick test then what I would like to do is try to open up my movies connection so I'm going to say movies con dot open and then a few lines further now and I'm going to say movies conduct close you always want to make sure that you close down your connections when you finish with them just as a bit of tightening up I'm going to set movies con equal to nothing once I finish with it that happens automatically in VBA anyway when n sub is reached but it's good practice to release your variables when you finish with them so what I'm looking for here is I step through the subroutine is I'm looking for this line not to fail and as long as it doesn't fail that's good then we're good to go let's make sure it works for sequel as well so I'm going to say sequel comes fro use f8 to step through again make sure that this line works it does close it back down again and we're now ready to start working with command objects you now to work with a command object we're going to need to have a variable which can all the reference to one so let's say dimmer movies CMD as an aid ODB dots command and then we can also say set movies CMD equals new aid ODB dots command sets the new instance now just like we did with record sets yesterday we have to tell the command which connections use when it gets executed so as we've opened up the connection let's say movies CMD dots active connection equals movies con so that sets the connection that we're using so you might have lots of connections open to lots of different databases now once we've done all that command objects are actually really straightforward to work with there's one vitally important property of a command object that we need to set and that's called its command text so the next stage is to say movies command dots command text equals and then we have to work out what is a valid SQL command that we can enter into this property now there are three main commands you can use for modifying data in SQL they're to do with inserting new records modifying existing ones and deleting old ones so I'm just going to give you a very quick overview of what they look like in sequel server management studio so it's three separate SQL statements which will allow us to first of all insert records into a table this is what going to start with actually going to choose a table to insert some records in to specify the field names or the column names and then specify the values that will go into those columns in the same order later on we'll look out you can update existing records so you can set use an update statement and set a specific field to a specific value where its condition is met and then also you can delete records from the table as well which is relatively straightforward delete from a table where this condition is true so let's start by looking at how to insert records into a table this just means essentially writing out this SQL statement in your VBA code as a string of text now because it's going to get quite long and complex I don't just want to try to do this in a single line I think what I'm going to do is create a function which is going to return our command text for us and we'll call that function several times throughout the video to get the correct set of text so let's head down to the bottom of the module and let's declare a new function I'm getting myself a few blank lines I'm going to call my function get insert text and it's not going to have any parameters at least to begin with I'm going to return a string from that function so function get insert text as string what I'll do here is declare a variable which will be my sequel string as a string and we can build up our statement inside that variable let's start by writing out the basic insert statements I'm going to say SQL string equals then I'm going to use a space underscore to continue writing this line of code on the next and in a set of double quotes I'm going to say insert into TBL film and then I can open up a set of round brackets to start listing out the field names now in sequel server or in sequel in general it is not case-sensitive the insert into does not have to be in capital letters it's just a convention in using sequel a sequel command words get entered as capitals and tell you whether you follow that convention or not I'm going to stick to it that's what I'm used to so after I've opened up the round brackets what I'm then going to do is concatenate another line so going down to the next line and here I'm going to list out the field names that I'm going to populate so I need to populate the film ID in many databases the ID fields represents the primary key for the table and you can set this up to be automatically numbered it just so happens in our database the film ID column does not have a value automatically ended so we always need to provide one we'll have to do that manually so after the film ID we can type in a comma and then say film name and then another comma and we'll have film release date obviously it's clear here that you need to know this structure of the database quite well and they'll have film run time minutes as well so film run time minutes wine then gonna do is closed round brackets closer double quotes another ampersand another space underscore and the next part of the insert into statement is going to be the values it's going to say values and again open some round brackets close and double quotes ampersand space from the score and then build up the list of values on the next line so let's just say for the sake of arguments that I want to enter the value the film idea of $9.99 so that's gonna be the first piece of information then I'm going to have another color and I'm going to enter the film name gravity now in my sequel string the word gravity need to be indicated as a string of text now in Access if we're doing this in Access you could enter this in a set of double quotes but the problem with doing that of course is that we're already inside a set of double quotes to build up the sequel string what we could do is double up the double quotes to essentially insert a single double quotes into the overall string but that gets really messy really quickly so instead what we're going to do is use single quotes to enclose or delimit text in sequel this makes much more sense is much easier to do first of all and it's also much more reliable sequel actually uses single quotes by default for its text delimiter in sequel server you have to apply special setting to your to your database server in order to use double quotes as text limiters in the first place so it'll be much more convenient for us if we use single quotes okay again the next thing we need to fill in is the film release date and again in sequel server dates are delimited by single quotes as well so they're almost treated as text I'm going to enter my date in a specific format is so it conforms to an international standard ISO 8601 so that the standard shows you entering Gates's year - month - day otherwise sequel server treat states in US formats by default so to avoid confusion I think this is a fairly unambiguous way 20 days a year month day from less specific to more specific makes much more sense finally we just can enter the value for the film once I'm in minutes and I believe that's 91 for gravity I need to close around brackets there closer table coats and that will generate the complete sequel string that I need to insert a record into that table all I need to do now is make sure that the function returns our body so I can say get insert as text equals sequel string and now I can call that function in my main subleasing so rather than entering that basic string there I can call on my function get insert text so we have our command text stored what we now need to do is execute that command so to do that is really straightforward we simply say movies command dot execute and that will try to execute that SQL statement on the database so if I do that and then I'm going to step through the subroutine in fact before I do that I'll just quickly show you in the movies database in the sequel server database at least if I look for the list of films in the film table just to show you that that film doesn't already exist so the last film that was added was 265 so I'm going to close that table down go back to my VB editor I'm going to step through the city scene just so you can see each line happening one by one so I'm setting the command text isn't actually the important line and this will just generate that string of text that comp as long as you return a string of text here that can't possibly fail the important line that we want to see if that works isn't this line here when we try to execute our statement so if I hit FA there looks good to me we can close down the connection and then close down our set movies coming cause nothing I suppose we should have also set movies command equal to nothing as well but that will actually happen automatically when we do ends up anyway so just quick check them back in the sequel server database if I open up the film table again and have a look down at the very very bottom we should find that we now have a new film entered with the valleys that we've specified let me just check that the film Ananse I'm going to come in there it is so there's the new film entered using a sequel command now it worked with sequel server clearly we want to make sure the next if it will work with access as well now couple of things we need to change here first of all let's change the connection string we're using to the access connections ring that's nice in storyboard we've also got to modify the insert text as well and the Access database does use an also number field for its film ID so in fact rather than modifying this existing function what we're going to do is create two separate versions of the get insert text I'm going to rename the first version is get insert text sequel and then copy that function completely paste it in change its name to get insert text access and then what we can do is modify the sequel string so we're still going to insert into the film table and we're still going to populate a list of fields but we don't need to populate the film ID so we can remove that and the first comma likewise with the values then we don't need to populate the first film I devalued that's erm that's going to be set automatically if we try to do that ourselves it would fail so let's remove that we've also got to do something with the date format as well access dates are delimited not using single quotes but using hash marks so what I'm going to do is replace the single quote with hash marks and then we can attempt to run it I need to modify as well the final lines of the of the the function so as I've changed the function names we need to make sure that we reference the correct function name so in the sequel version you'd say get into insert text sequel and the access version can insert text access okay so what we can then do is return back to the main subroutine so that I want to set the command text using get insert text access again just to quickly show you in the Access database that if I open up the film Zabel I'm just going to sort this in descending order of film ID so you can see that again the ones where worries is at the top film in the list if I close that down saving changes to the to the design back to VBA and I'm going to step through this routine using f8 so again setting the command text basically can't fail as long as you've returned a valid string that will simply work what's important is does this line work when we executed the command so we hit f8 looks pretty good to me nothing's failed at least so let's have a quick look back in the Access database open up the film table and there's gravity again so you'll see that the formatting of the dates change based on the the front end of the program you're working in so in access it reverts in my case to UK date format so day month year the important thing is that when we enter the date the correct date serial was entered based on the formats we provided and there's a film run time minutes as well so hopefully you can see from this and so far a couple of things first of all that command objects are really straightforward to work with you declare a variable create a new command set which connection essentially which database to make it use and then you simply set the command text of that command object and execute it so clearly the most important part of this entire process is generating a valid sequel string in the first place the next part the video is going to show you a little bit about how you can get this to be done for you automatically now the biggest thing that's going to hold you back from using command objects is if you're a VBA developer you may not know your SQL commands very well now fortunately I can help out without because I've made a video series all about sequel in fact two separate series one for writing queries in sequel and one for programming in sequel so if you were really interested in learning sequel there's a couple of really nice video series you can watch however if you don't want to bother sitting through all that and Stephan you're bored of hearing me talk to you already what can you show you a couple of ways for getting both sequel server and access to generate valid sequel strings for you so I'll show you quickly first in sequel server how you can use if I go into sequel server management studio if you ever write to click on a table there's always an option to script the table as and they got various options for what you can do so all these options here are going to generate a system-generated select sequel statements so the one we've just been looking at here is the insert into statement so I look for the insert two and I choose new query editor window what will happen in sequel server will automatically generate a basic generic syntax for inserting data into that table now of course there's lots of different things that we need to change to make this actually work probably there's a lot of placeholder text here but you can clearly see that the basic structure is exactly what we've been writing out ourselves there's insert into then name the table then inside a set of round brackets a list of the field names we've seen that we don't need to list out every single field name we can pick and choose the ones we want to modify then after you've finished listing out the field names there's the values section again of round brackets and all these placeholders would need to be changed so you need to pass in the correct specific ID number the correct specific film name and so on and so on so but that the really nice thing here is that you don't have to worry too much about working out exactly what sequel string to write yourself you can get the sequel server and you do that for you again there's a valid way to do that for the later things we'll see in the video so there's an update to new query editor window and there's the basics statement for an update statement and then again fold the delete statement so if I say script table as delete to you query editor window and that will generate the basic delete and the easiest one of the lot okay so that's how to do it for sequel server it's a nice sort of crutch if you're not that happy with using or writing out sequel statements yourself let's have a look at how to do the same thing in Access so let's head back into access and the way to get access to generate SQL code for you automatically is to create a query and it's best to do this in design view you'll get a bit more power than then over the views the wizard so choose query design essentially accesses query designer it's just a handy graphical user interface way to generate SQL code for you so really when you when you build your query using this query designer you're actually just creating SQL in the background so it'll almost a little bit like recording macros in Excel VBA I'm not going to choose a table here for this particular example I'm going to try to do is generate a query that will add records to a table which is what we're trying to do in our VBA code so once I've created the new query in design view I'm going to change it to an append query so if you hover the mouse it says makes the query add verticals to an existing table which is exactly what we're trying to do so if I choose append I can then choose which table I want to append my data to I'm going to choose the film table and if I click OK at this point even though I haven't filled in any fields or select any values at all if I change the view of my query into SQL view then you can see that it's starting to provide out the SQL statement for me so it says insert into TBL film and then it's got the Select keyword I'm going to switch back to the design view because what I can do now is start listing out what values I want to add to which fields so if I set the append to option to film name then the field that I want to pass in actually isn't a field name at all I just want to pass in a string of text so what I'm going to do is enter into a set of double quotes the word gravity preferably with a capital G and then close a double quotes when I hit enter it will do some basics and syntax changes for me but I go back to the SQL view now you can see that it says insert into TBL Filton it's got the first field name in or instead of round brackets slightly different syntax in Access when you do this in the query designer rather than saying values and then putting gravity in a set of round brackets it says select gravity as expression 1 now we can happily modify that if even if we do it in the in the query designer and access if you can modify the SQL yourself and I can replace the double quotes with single quotes as we saw that could have been an issue and I don't need to have the MD as expression 1 I could have the opening closed brackets there I don't mention only 1 the semicolon at the end of the sequel statement in both Microsoft Access and sequel server the semicolon is is an optional statement terminating you don't need to put it in but in most system generated code it will add that in for you it's up to you whether you include it for access or sequel server things will work without them if you don't want them in and with them if you do so that's the basic idea of having access generates the insert statement just to quickly mention if I close that query down and without saving the changes we can do the same thing to getting forgetting the update and delete statements as well so I create a new query in design view I'm going to choose the film table this time because I want to update the film table so I can close our window down and then change the query to an update query so again if I choose to I know change the film runtime minutes field update it to a value of 180 oops 180 where the film one time minutes is already longer than 180 then if I look at the SQL view again you'll get exactly the same syntax generated that sequel server generates for you last little ones have to have a quick look at close that one down don't save the changes new query in design view if I again choose the film table and delete queries really straightforward if I choose delete and then I can simply say delete records from the film table let's say where the run time minutes is longer than hundred eighty for example so again if I look at the SQL view I've got all the SQL code written out for me which I can almost just copy and paste without making any changes at all into my VBA code so there's a quick preview of how you can get both access and sequel server to generate your SQL code for you for the rest of the video now we're going to assume that we know enough about a sequel to write it out ourselves but always bear in mind that you can always rely on both sequel and access to generate it for you if you're not happy doing that yourself okay so now that we've seen how to add explicit values into a new record in the database let's have a look at how we could read the content of Excel cells into those new records basically what we're going to do is loop over this list and add a new film for each row and that means incorporating the cell contents into our SQL statements we're going to be careful about any text which contains single quotes because as you've just seen we're using single quotes as a text delimiters in our statements so things like Marvel's The Avengers and Europe's most bonded here we need to consider how we handle these little single quotes to make sure that the statements will still work but the first job let's do this in an Access database first what we need to do is modify our function so that it will accept some values because we want to be able to call this function for each new row of our spreadsheet to insert a new film so let's start by adding some parameters to our function definition we need to have three parameters for our function to work so we need to pass in a film name a release date and runtime minutes so let's just define these quite simpler that's called them film name as a string followed by a comma then we'll have film dates as dates to be honest if some really matter which data type you use in the long run because of the film date is eventually going to be converted into a string anyway but using the dates data type make sure that you get at least a small amount of validation if we try to pass in something which isn't a date into the function then will at least generate runtime errors data type mismatch errors so let's use the data type for film Nate and likewise for film time we will use the integer data type so that will allow us to hold a whole number and again we'll get validation errors if we try to pass in something which isn't a number so those are the three parameters for the function defined the next thing we need to do is start incorporating them into our sequel string now at a basic level this simply involves concatenating the value of each parameter into the existing string so replacing the word gravity by concatenating a reference to the film name parameter but it's not quite so simple remember we have to take into account things like single quotes in our film name string and we want to make sure we'll be formats our date in the correct way as well so what we're going to do I'm going to tidy things up a little bit I'm going to try to build each piece of information on a separate line so what I'm going to do first of all is after the word gravity and a comma I'm going to type in a double quote and then an ampersand a space underscore and take the date down to the next line open a double quote at the start of that line and then again after the comma closed in double quotes space ampersand space underscore and then I can fill in the film runtime of the film time on the last line so that make sure I can separate out each individual bit of information let's deal with a film named max M so I want to concatenate a single quote character to the start of let's just go with word gravity for the moment and then after the word gravity I want to concatenate a close single quote and a comma before I then bring in the release date so instead of putting in the word gravity here of course we're going to reference our film name parameter so let's replace the word gravity with a reference to film name now as long as the film name doesn't contain any illegal characters that will work absolutely perfectly however we've got to make sure we take into account those single quote characters now in a similar way to handling double quotes in a VBA string you can double up the double quotes in sequel you can double up the single quotes to make sure that you can catenate a single double quote into the phrase and we can use a V be a function called a replace to find any instances of a single quote in our film name string and replace that with a double single quote so let's have a look at the replace function also replace open brackets the first argument of this is the the expression of the string I'm trying to look for a value in then after the film name we can tell it to search for a single quote character and then we can say that if it finds a single quote we can replace those with two double quotes I started two single quotes and then close around brackets and that will handle the film name happily now we need to do something similar to make sure that the dates goes in in the correct format so just as we did on the previous line let's close a set of double quotes to make sure we get the opening hash mark character then we have a space in an ampersand join on the actual date in a moment let's just leave the existing date in there for the time being and then type in another space after the date another ampersand and open a set of double quotes to concatenate the closed hash mark and then the comma to move on to the next value now again we're going to replace first of all just the existing date with a reference to our film date parameter so it is and we also want to make sure that this goes in in the correct format as well so going to use the format function to do this so if I say formats open brackets you can see that it takes in expressions that can be almost any value then the second parameter is what formats you want to place it in so after film date I'm going to type in a comma then open some double quotes and the date format that we want if you want to follow this ISO 8601 formats we're going to go for white why why why SAS that puts the Year in in a four-digit year then a dash and then two M's for the month and another dash and two M's sorry two DS for the day then then close the double quotes close round brackets and that will put the date in the correct format as well we've finally got to handle the film time as we've called it in the in the parameter list so this is this is not a straightforward one a lot all I need to do is take away the actual number that I pass in there and making sure that I need to have a closed round black it is a very last character so just before that I'm going to say film time ampersand and then close round bracket now vbi uses a feature called implicit data type conversion so even though we're passing in the film time as a number it will automatically be coerced into a string because we're concatenated it with other strings if it made you feel better you could use the C straw function cser to make sure that the the film time gets explicitly converted into a string that's just for the sake of completeness if you like you do not need to do this in VBA and data type conversions work in political and you can always convert something into a string that's always going to work so it's intellicus whether you put the C string function in there or not I'm going to leave it in there as I've taken the effort to type it in so that should now generate a valid sequel string based on the values passed into the function let's see we can use it to loop over our list of films adding a new film for each record that we encounter so that means going back to our main subroutine and we'll have to have a couple of extra variables here as well so let's declare a new variable or at least one varial dim R as range and once we've created our new connection objects and open that up and created our new command object and set up its connection property what we want to do here is generate a new command text and execute that command each time we encounter a new film so these are the two lines that essentially are going to loop over our list of films so that's in demos and then just above those lines what we're going to do is say for each are in and the range of cells that we want to loop over is on sheet 1 from cell a3 down to a 15 but I want to make sure this works regardless of how long the list is so what we're going to do is we're going to say back in the VB editor for each are in range a3 try them again a 3 comma range a 3 dot end Excel down that's just a quick convenient way to get the entire list it assumes that there are no gaps in the list at all so we'll stop it there any gaps in the list but that's more than enough for our basic example we need to make sure that we do move on to a next next row the next cell in that um in that loop so let's say next are at the end and then all we've got to do is modify the way that we call this function because we've now got to pass in a number of arguments to each of the film I'll get in set text access function parameters so let's see how we can do that what I need to do I think I'll break this down onto multiple different lines just to make sure I can see the whole thing on one single screen with going to say movies command command text equals then a space underscore and I'm going to get the get insert text back this on a new line and then I'm going to have a space in the school after the open brackets and the film name is going to be provided by our dot offset 0 comma 1 dot value so that's the value of the cell one column to the right of column a so what's happen in common then another space underscore I know that in the film date must be offset two columns to the right and therefore the film runs home must be offset three columns to the right nice and straightforward nice and consistent oops three not twenty three dot value then close around brackets and that should successfully generate the string for our insert statement right let's see if this will work I'm just going to quickly show you the Access database table so let's go back to the film table and access and you can see that gravity was the last film that was added there so we know I'm already out of those films let's close that table down back to the VB editor and we're connecting to the Access database that's good and we're calling the access getting set text function so that's just in fact let's step through the first few lines just make sure it's going to go the first couple of rows so this will generate the command text now works happily that's not the one we're really worried about we're really worried about it does this line work so I've hit f8 to run that it seems to have happened and it's going to do this for each row now in that sheet so let's hit the Run button the play button to just run all the way through to the end it seems that everything's worked let's head back into access quickly open up the film table and there are all of our new films fantastic so have we successfully handled the being the the single quote so we've got Marvel's The Avengers with a single quote there and we should also have Europe's most ones are there with a single quote superb so that's one way to make this this system work looping over the collection of cells generating a valid sequel statement for each row and then executing that one by one now that we've got things working in access let's have a look at what we need to change to make it work for sequel server as well so let's start by modifying the connector string we're going to go for this sequel connection string and we'll also call the sequel version of our function as well now there are several changes we can have to make to the function the main thing involves actually changing it to take into account the fact that we don't get an automatically generated ID number so we're gonna have to calculate that now as I said before this is this is not a feature of a sequel server this is purely because this particular database has not been designed to have also number fields nevertheless we're gonna have to deal with that in this particular function so let's start by copying the the parameters from our access version of the function we'll need to pass all of those in when we call on the functions remise I'll copy those and then we could also let's see we would also copy in fact most of this that generates the sequel string let's copy the entire expression which generates the sequel string and replace the existing one in the sequel server functions let's paste that in now of course access uses hash marks to delimit dates sequel server uses single quotes so let's replace the hash marks here to enclose a date in single quotes then the next bit is working out how we handle the film ID so let's incorporate the film ID field name in the list of fields and then let's give ourselves an extra blank line in between the value so the first value we working out to calculate now is the value of the film ID field so let's put in a pair of double quotes and then an ampersand and a space underscore now essentially what we want to do is find out what the current highest value in the film ID field is and then simply add 1 to that number to create the next ID number in the sequence it's a bit of a low-tech solution but it will work now if I wanted to do that in sequel server what I could do is if I just quickly show you in a new query window I could write a statement which says select max film ID from TBL film and if I execute that query that will show you the current highest number is number 999 that's what we had before the film gravity earlier on so I just quickly show you that that's the current highest value of the ID number okay so if I said simply plus 1/2 that number so if I wrap that query open to a set of round brackets and say plus 1 that will essentially generates that result plus 1 so that's what I can actually incorporate into my select statement sorry into my insert statement in my VBA code sequel supports the nesting of statements so I collect essentially what was a sub query in the insert into statement so inside that set of double quotes there I'm going to open and close a set of round brackets and then inside there I'm going to say select max open and close brackets inside those brackets I'm going to take film ID and then from TBL film and then after that expression I'm going to say simply plus 1 don't forget that I need a common then to separate the value of that field from the value of the next one which will be the film name and then the date and then the film time and that's essentially all we need to do to make this work in sequel server so let's head back up to the main subroutine I'm just going to quickly go back to sequel server and I'm going to delete the the film that we added in earlier the gravity film so I'm going to make sure that we've got the the last film that was added is the same as the one that was in the Access database number 265 won't already so I'm going to delete that choose yes to do it and then if we head back to the VBA and run the sublet in we ought to find is that everything appears to work correctly baptists equal server open up the table again and if I scroll all the way to the bottom there is our new list of records starting from number 266 so there we go to successfully written functions that allow us to insert data into two different types of databases now what we've done so far in this example is essentially generate single SQL statements or commands and executed them one by one but when we're connected to a sequel server database we can actually build a whole sequence of commands into one single command text property then execute them all at the same time so to demonstrate how that works what I'm going to do is I'm going to move the execute method of the command to after the loop so what that means is I'm going to build up the entire set of commands or statements into a single command text property and that means I'm building this row by row I'll need to concatenate the new statement to the end of the existing command text so I'm going to copy that movies command or command text and make that equal to itself and the result of the function that we generated earlier on there's one more additional thing I'm going to do I don't if you remember earlier on I mentioned that there was an optional statement terminator in SQL which is the semicolon it's optional in both sequel server and in Microsoft Access what I'm going to do is I'm going to add that semicolon character to the result of my function so it means that once I built one single statement one single insert into statement it has a terminator for that statement right at the end okay so what I can do now in fact rather than trying to execute this what I'm going to do first is I'm just going to debug print the result of this full set of statements I'm going to say debug print movies command dot command text once I've done that let me just review the immediate window and if I run the entire sibling scene from start to finish so we're not actually going to execute the command at this point which is going to print out the final command text if I actually the whole thing here's what we get so you can see I've got the start of my first insert into statement and then we've got the list of values for that which is Marvel's The Avengers and so on and so on then when that one's finished there's the semicolon statement terminator and we go into the next insert into statement and that just carries on for every insert into statement we generate we have a semicolon separating that one from the next and then final semicolon right at the very very end okay so having seen that we've generated a lot of valid string let me uncomment now line remove my debug print statement and before I read let's have a look at the M the movies table so if I look at the film table in the movies database and just to show you that we've only got what was the last number the last number those added number 278 so if I close that down head back to the VB editor and run the whole thing in one go back to SQL open at that table again and what we should see this time is even more records so every time we run this we execute all those insert insert statements as a single batch which is offers a couple of simple advantages to to accessing individual statements one by one there's an interesting thing to see that you can do with with sequel server now although we can do this quite happily for sequel server we can't do the same thing for microsoft access using the same technique if I just incorporate a semicolon statement terminator at the end of my access command text so it is if I attempted to them modify this so I'm looking at the access connection string I'm calling on the access functions so movies command command text equals text SQL text access is what I want to say here so that calls the access version of the function if I attempt to run this one just quickly show you in them in the table that we've got 279 is the last record if I attempt to run this one what's going to happen is I'm going to get a runtime error this is actually generated by Microsoft Access when you run when you're doing this it's as if it finds characters after the end of a first sequel segment so with the semicolon statement terminator access automatically checks if there's anything after that semicolon and it prevents you from executing more than one statement at the same time I'm going to click the debug button here and what I want to be able to do is just make sure that I close down my movies connection so I'm going to draw like my yellow arrow so it does actually close down the movies connection so it'll run all the way through to the end and I'll see that if I go back to access that I haven't added any new records at all so why not this access do this too is why did not allow us to write multiple segments while sequel server does now this is probably easiest to demonstrate if I switch back to using sequel server just for this sections I'm going to go back to the sequel server connection string and I'm going to go back to getting my results my query string from the sequel version of the function essentially what we're doing here each of these functions is generating a form of something called dynamic SQL an ortho Namek SQL is is a string of text that's concatenated out of various different component parts now whenever you're generating dynamic SQL any sort of unscrupulous person shall I call them who knew a little bit about how SQL works has the opportunity to insert things into that sequel string that can cause things to go horribly horribly wrong what I'm going to do to demonstrate this is back in sequel server I've actually created a quick table called TBL test there's no data in it but what I'm going to do is I'm going to insert something into my Excel spreadsheet they'll cause that table to be deleted so back into the Excel spreadsheet what I'm going to do is at the end of in fact ago do this at the end of any single one of these film over time in minutes I'm going to pick the first one just for the sake of convenience I'm going to edit the text in there what I'm going to do is after the number 143 I'm going to close some round brackets and then type in a semicolon and we've already seen that the semicolon is a statement simulator in SQL what I'm then going to do is write out a new statement called drop table TBL test and we haven't seen the drop State remember drop table is another sequel statement you can pass into an an ad ODB command object and once I've done that action I'm going to type in another semicolon which will end and we'll terminate that statement and then I'm going to type in two dashes the dashes are examples of the comment character in SQL so you know how in VBA we can enter a comment by typing in a single quote in SQL the - - symbol is used to indicate comments so what this means is in the full sequel string that I generate after the first number for the first film I'm entering I'll get a set that statement will be terminated and then another statement gets inserted a drop table and then that one is terminated and then everything after that is going to be considered to be a comment so the insert statements relating to the next list of films is going to be commented out now then there is one other small tweak I'm gonna have to make to make the system work I just go back to the VB editor we do have one small safety net in the way our function works because the film time parameter is expecting an integer if it isn't an integer then it's going to fail with a type mismatch error so just to show you that this will work if this was being passed in as a string let me just quickly do that so having done all of that and just reassuring you that at the moment the test table is still there if I were to go back to the VB editor and then run this entire subroutine so I'm not even going to bother testing I just gonna run the whole thing from start to finish another thing appears to have worked if I switch back to sequel however although at the moment I can still see my test table when i refresh this folder can you see the test tables disappeared don't under so that's the other big problem with generating dynamic SQL now of course as you've seen it depends on which type of database you're connecting to sequel is a bit more flexible it allows users to pass in multiple statements in one single command but because access is a slightly more I suppose basic database application and because of the the possibility of dynamic SQL doing horrible things of the database they have built in some sort of validation to make sure you can't pass in multiple statements this is to me definitely definitely have to be aware of if you're going to be attempting this sort of thing in VBA be very very careful of dynamic SQL and unscrupulous people passing in strings that try to break your database there's even a famous cartoon I don't if you guys have seen the xkcd website but they serve one of their most famous cartoons is this one exploit someone get a quick quick google it's quite amusing if you know anything about SQL anyway having done all that let's see what we could possibly do to prevent this from happening in our own database I guess obviously one thing is that we could make sure that if we're passing in the film time then that should always be passed in isn't it so that would have solved that problem in the first place if I just kept that as an integer I wouldn't have been able to do that because of the type mismatch error but it might well have been the case that the final parameter was passing in a string so how could we test if the return value of our of our string did not contain the drop keyword so we can add a little bit of validation to our insert text SQL function to make sure that this will work now one way to test if a string of text exists inside another in VBA is to use the instruction which is offer in string so just before we get our function to return a value we're going to say if instra open brackets and then there are several parameters we're going to fill in the first one is the start position for your search so it's the character index of the character you want to start searching from it's up in the number one we start searching from the start of the string of text make sense the second parameter string one it could have been better named but this basically wants to know which string of text you are looking in so we're looking in our sequel string variable the third parameter is which string of text you're looking for so we're going to say here is looking for the word drop now by default based on default settings in in VBA in string works on a case sensitive basis if I search for the word drop in lower case it's not going to find it if it if I have the word drop typed in in uppercase so there are a couple of different ways we could handle that we could use the L case function to convert our sequel string into all lowercase text so to do that we'd say L case open brackets equal string closed brackets so that converts a sequel string into all lowercase text alternatively you could use the fourth parameter of the instruction so you could say if it's having a comma after the the drop parameter we could tell the the entering function to compare based on text so that makes this completely case insensitive so that's the method I'm going to use I'm going to use a vb text compare option there so closer round brackets now if the in string function does not return a match it doesn't find the word we're looking for then it returns zero so I'm going to check if it does not return 0 so the result of this function is not 0 then and we want to do something now just before you go any further there's a couple of other considerations to make here if I had a film name which contain the word drop then our is statement will pick up on that film even though we're not trying to do something nor see to the database it will still pick up the film as though it was trying to drop a table so there are several of the ways you might want to be able to test for this you might want to consider just the word drop by itself so you could type in a space after the word drop to ensure it finds finds for instance as we saw there drop table so there's a space after the word drop in the way I've written it out here there's a space before the word drop as well and then a semicolon before that this would have still worked by the way there was no space after the semicolon so it could be semicolon drop or semicolon space drop so as you can see from this there are lots of different ways that a user could try to make something bad happen to your database so what you'll find happen in the real world is you'll probably need to test multiple different instances of this so what we could do is for instance in this example could type in a semi colon and a space drop space another test would be drop space by itself and this test might be semicolon drop with no space and so on and so on and so on for this example we're just going to test for the word drop and so be it if there were only films with the word drop in their titles this would still throw an error so we'll ask the if theme in itself let's write the end if statement and then we can just say what we want to do if we encounter the drop keyword in our sequel string what we'll do for our example is get the function to raise an error to the calling subroutine if it encountered the drop keyword so to do that we can say Oh dot raise now we've covered error handling in several previous videos in fact there's a single dedicated video with this all about error handling I'm not sure you ever mentioned about raising custom errors before now so if you raise a custom error there are several things you should provide first of all you must provide an error number now the numbers for the errors is quite a long large range passing zero to sixty five and a half thousand numbers but there's a certain range that's reserved by the system so what you can do to find out what numbers are are acceptable is click on the word raise and then press the f1 key on your keyboard and that will take you to the help page which in this version of Office is actually a web browser system so you can see the Rays and method of the object and we're looking at the number programmer to here so this is a number between zero to sixty five and a half thousand or so but there's between zero and five hundred and twelve is reserved for system errors so we've got to use a number this above five hundred thirty five hundred twelve basically it gives you a little hint about how to generate the the correct error number by using a simple expression so we're going to add a number to the constant VB object error so I'm just going to copy that code out and I'm going to go back to my VB editor and after the raise method for the first number parameter I'm gonna say ctrl V to paste it in I'm not gonna you to the number 513 that's fairly mean this isn't it I guess the number I'm gonna use 600 is fairly meaningless as well but it's just a bit better than 513 so if I type in a comma the next parameter then is the source of the error so usually this is the name of this function or the subroutine which is raising the error in the first place you don't have to provide that a quick description again says the source of the error there so we're not going to bother with the sauce pronouncing that's not going to be useful piece of information to us what we're going to do instead is move straight on to the description pedometer which can be anything you like basically so it just something that describes why this error has happened in the first place so I'm going to call create my description I'm going to call it something like oh I know naughty words used there we go that's quite um quite obvious in it what's going on so there's the way that we raised the error what we need to do now is incorporate that in some kind of error handling system in the main subroutine so just before we try to call this function on the line just above this one we're going to write an on error statement we're going to say on error and go-to I'm just going to generate a basic error handler so I'm going to say error handler for the name of the label I'm going to jump to I'm going to copy that keyword head down towards the bottom of the server de team and paste our word in type in a colon after it which converts it into a label or a bookmark so if it ever happens after this point it will jump straight to this label and run any code beneath it now there's a couple of other things we should do as well we only want to test for this specific error in this line of code so I'm going to say on error goes to zero immediately afterwards and that will reset the customer around left back to normal error handling status again that's talked about in much more detail in the video on our handling we also want to make sure that we can't reach the error handler unless we get there by generating a runtime error so we must right exit sub immediately before the error handler otherwise the code will just continue and run straight through it anyway so the exit sub means we can only get to their handling section if we we generate a runtime error in this line okay so that's the the error handling sort of system created we've just got to work out what we want to do when the error actually occurs I guess we should show a message to the user to tell them what error has occurred so let's use a message box to do that and the first part of the message the prompt is going to be ever number equals and then I'm going to calculate what the error number is now I can do that by saying a number so that will tell me the number that's been generated from our raised error method earlier on but I also have to make sure that I subtract from that the vb object error value otherwise I'll just get some completely random number all together so make sure that I get the number 600 okay so our close the parentheses there I'd also like to have a new line characters svb new line and then after that I'm going to show the description of the arrow as well so description I guess we could also display different symbols on the message like a critical symbol and so on using the other parameters of the message box but I'm just going to stick with what I've got for the time being just nice simple message on the next line the other thing that we also want to do is make sure that we close down the movies connection if something goes wrong here then of course we jump over that line and we don't we will after the connection left open we don't want that to be the case so I'm going to repeat the line this has movies con dot close I suppose we could also say set movies con equals nothing but that will happen automatically anyway as I've said before so with that done there's just one more change that we need to make as well to make this system work before we test it it remember that I switched back to setting the film time parameter to being an integer which is actually one much simpler way to validate this whole system if I try to pass in something which is not an integer into that and parameter then the whole system fails anyway but let's just switch it back to a string for now and give it one more quick test so I'm going to set a breakpoint on my on arrow statement line that means I can learn the subletting up to that point and then use the f8 key to tab through so when I use f8 here it's going to call my subletting or function torie and there we go we can work on a pass in all these various values including a film time value which includes the drop table keywords so I use the f8 key again to continue stepping through then I can build a sequel string as usual and now my Kista min checks what the result of the trick I'm looking for the word drop in my single string is so returns the number 166 which means that the word drop begins at the 160 sixth character in that entire sequel string which clearly is not the same as 0 so we're going to raise an error which will take a straight back to the error handler in our calling subroutine so as soon as you do this we're going straight to the error handling section show this masterbox error number number 600 naughty was used so I click OK then we can close a connection and then the subroutine so there's a way to handle dynamic SQL naughtiness I suppose if people try to insert horrible keywords or horrible statements into your sequel statements you can prevent them from happening using error handlers similar to this one now this kind of system works reasonably well when we're building up a sequence of sequel commands or sequel statements into a single command text property then executing everything all at the same time but if you remember we couldn't do that with Microsoft Access and it might be the case that we decided to execute individual instructions on our sequel server database as well so in fact we're just going to switch my code bats using that basic earlier version so I'm going to remove my breakpoint and then remove the line which concatenate the command text to itself and then I'm going to move the execute statement back into the for each loop I'm going to do that just after the error handling statement as well okay so there we go now what's gonna happen now of course is that I'm going to build up a sequel string and then immediately execute that sequel string to insert a record then do it again and insert another record and so on and so on and so on now imagine we were doing that but it wasn't the first film that gave us a problem let me move this value so I'm going to copy that I'm going to place it into a different film's running time instead hits Skyfall that's got the same running time as the Avengers that's convenient so I'm going to paste that in there then I'm going to change this cell back to just being 143 okay so when we run this now the way our system works we're going to successfully insert this record and then this one and then this one and then we'll get to the point where Skyfall will cause an error so at that point we will have failed to insert a record and none of the other records will be inserted so let's imagine then that we solved the problem we found out what the issue was and we change that back to a standard number and then we tried to run this every scene again of course at that point we'd already have the first three films in the database so we'll be adding those ones again as well as all the extra ones so we want to avoid that from happening and one way to do that is to incorporate the use of transactions into our error handlers so going back to the VB editor and we're going to have a quick look at how you can incorporate transactions into this code now I've made a complete video on how transactions work that's part of the sequel server programming series I'm not going to encourage you to go away and watch that one right now just to give you a quick summary basically when you begin a transaction it tells your database to remember any changes that you make 20 data until you decide to either commit those changes or roll them back so the idea behind a transaction is we begin it before we attempt to make any changes to our data at all so what I'm going to do is just before we start our loop I'm going to say movies con dots bacon trans so transactions associated with a connection object not by the command object what that does then is every time we make a change of the data then sequels of remembers what change that was made so it is essentially like a setting a safe point I suppose in your in your database what we could then do is if we find that we raise an error by trying to insert something with the drop table statement in it as our function now works what we could do is in part of our error handling code just before we close down the connection we could say movies con dot rollback trans so that would essentially undo any changes to data have been made up to the point or price of the point of the we we M began this transaction we'd also have to make sure that everything works properly then before we close down the connection and everything it worked successfully we'd need to make sure that the transaction was committed so to do that we'd say to before we close the connection normally we'd say movies con not commit trans so you must always do one of two things when you begin a transaction most are always either commit it or roll it back you can't leave your transaction hanging your DBAs will get very upset with you if you leave your transactions open so with all that code written let's give this one a quick test I'm going to just save this subroutine at this point and we're going to step through the subroutine to see what happens before we do that let's just have a quick reminder of what's in the sequel server table at this point so if I go back to sequel server management studio and open up the movie table and scroll down to the end we should take the last record is in the Mattoon to the 78 Lincoln so what we should see at the end of this said what is in the first time we're in it is no extra records after that point so it's close down that table and then let's go back to the VB Elza and I'm going to set a break point again on the on arrow statement so I can run that up to that point and then I can pause and use the FAC step through so FH step through this will generate hopefully a valid sequel string the first time because the number 143 is in the film's run time this time so the in string function finds that looking forward drop but it turns the number 0 which means that the if statement isn't triggered we don't release the error we get the insert text and then we execute that statement and that film is now been added to the database it hasn't been saved yet when you begin a transaction until you commit it the data has not yet been saved but it is actually inserted into the table successfully so we'll carry on going through and the next film will work as well because it has got a sensible running time so this one will work let's just quickly go through that then one more time this one will work because it's a number 142 and that's inserted as well this time however this is the film whose running time is not valid this is whether word drop appears in the running time so this is going to raise our error and at this point it's going to trigger our error handler show us the message what he was used we should probably tell these are there any existing records will then be removed or any any records added since since we started will be removed will roll back the transaction and then close the connection and then the subletting so if all that's worked as intended what we should find is if I go back to sequel server and open at the film table edit all rows we should find that the last record again is still in the maternes md 8 Lincoln so let's just quickly check this to make sure it does work if we enter completely valid data back to excel verse I'm going to remove this and silly statement and replace it with a number 143 so if I go back to the VBA tour again I'm not going to bother stepping through the so pretty in this I'm I'm going to remove my breakpoint engine running the whole thing through them start to finish look back to sequel server open up the table one more time and have a look at the end we should see numbers more than 200 at this time so there we go we got a completely new set of all those records because we committed the data to the database so that's the beauty of using transactions they're absolutely fantastic when you're writing error handling code in VBA to talk to databases if you're modifying data at all and anything goes wrong as long as you chose to begin a transaction before you modified something you can always revert back to the previous status you can roll back then when you're happy that everything's worked you can commit it you can commit I realize that we spent a long time talking about the insert statement in this video but it's important to realize that exactly the same techniques that we've used here will work for any of the other sequel statements as well so if we're talking about simply modifying data the update statement and the delete statement but also more complex things like create and drop as we've seen the drop table statement clearly works when you pass it into a command object so I just wanted to do a couple of quick extra examples here with the updates and the dil Dietz method a suspect is fairly unlikely you'll be creating and deleting tables from VBA code but I think certainly updating and deleting records might be something you'll be interested in doing so just a couple of techniques for using updates first and then and then delete to finish off the video so back to the vbn exam what I'd like to do for this example is assume that maybe the lengths of our films change on a regular basis and I'd like to be able to loop over my collection of films in the spreadsheet and update the database to have the modified version of the film length so I want to do this in Microsoft Access we didn't put a lot in sequel server today in this video so let's revert to access just put a bit of variety my head back to the VB editor one of the nice things I can do here is just pretty much copy the definition of my insert text function for access so I'm going to copy that whole thing it's worth bearing in mind that if you're working the sequel server this will still work of course so if you're using sequel server please feel free to follow this through using sequel rather than access I'm going to modify the name of the function of course you get update text access and then I don't need to include the film date parameter in the definition my function so I'm going to remove that I've made sure that I've gone back to an integer data type for the film time as well and then essentially what I've got to do is just modify the the sequel string that's returned to make sure that it's an update statement instead so let's remove everything apart from a veritable votes and then work out how to write a sensible update statement now there's essentially three main parts to an update statement and the first part is really simple it's just the name of the table that you're trying to update so I'm gonna say updates TBL film then for the next part I'm going to put that on the next line so I'm gonna using them ampersand space underscore let's start the next line I'm going to start another set of double quotes a space I want a space in between the name of the film table and the start of the next part of my statement I could have put that in there actually but I've decided to include it the start of the next line so the next statement so the next part of the statement says what field you're trying to set so we're going to set film run time minutes equal two and we're going to set that to be equal to whatever value gets passed in by the film time parameter so here we can concur we can concatenate in the film time parameter and then another ampersand space underscore to go to the next line start the next line we include a where clause to make sure we only change this field for a specific record or records if we miss out the where clause then just all the film long time minutes values changes in the entire table so that's at a where clause where film name equals then I need to open up a set of single quotes close double quotes ampersand film name ampersand open double quotes close single quotes and I'm also going to incorporate this statements emulator semicolon as well so the the single quote here and here will enclose the film name in the final statement and that is essentially the end result of our sequel string we've got to modify the name of the function of course so we're getting update text access rather than getting insert text access as it gets update text access that's the name of the function and I suppose we should probably think about incorporating the error handling code as well it's just as possible for somebody to try to delete a record using the drop statements in our function and as it is when they were trying to insert records we're working with an Access database we can only pass in a single statement at a time anyway so the same interpolate any characters at all appear after that statement terminator then access will prevent us from continuing in the first place so we can actually get away without adding the error happening go to the access function now before we head up and incorporate this function into our main subroutine there's one more thing we've got to do I almost forgot but of course our film names can contain single quote characters within them so as we handled it last time we used the replace function I'm going to cheat here and copy and paste that into this function as well it's going to copy the replace function and put it in place of the film name so that make sure that any single quotes get replaced with a sequence of double quotes which means that everything will then work happily ok so having done all of that we can now go back up and incorporate this into our main subroutines let's scroll all the way back up to the top I think should make things a little bit more clear in this example I'm going to get rid of all the error handling code we won't trigger an error handler anyway because of the function that we're going to call so let's remove that section of code altogether makes it a little bit easier to read and also the error handling same as the on arrow statements let's get rid of those two so I'll get rid of those we need to make sure that we're connecting to the access database not the sequel server one so let's change the connection string that we use to the access connection string and of course the main thing is we've got to change which function we're calling we're going to call the get update text access function this function of course only accepts two parameters so at the moment were still trying to pass in three which includes the film's date but we don't need to do that anymore we can get rid of the line which passes in the date of each film because all we need to do to make this function work is passing the name and the runtime now what we haven't done yet is actually changed anything on time so many of our films so let's go away and make some change to the running times before we try to run this subroutine so let's head back into Excel and let's change the running times in fact let's change them all let's make them really obviously different numbers let's go for a thousand for the Avengers and a thousand and one for the next film that I can just quickly cheat and copy and paste the sequence down so these will clearly stand out when we and this ability now before we run the subroutine let's just quickly check what valleys we currently have in the film table so if I open that up there's our list of films in the Access database and the running times are their original values at this point so what we can do now close that table down head back to the VB editor and that should give the entire subletting a quick run-through so apparently something's happened let's go back to access and open up the film table have a look at the film than time in minutes and there are all the changes so if I should mention that there was more than one film with the same name this would have updated every single film with that name so if I had multiple Lincoln's and multiple skyfall's etc they would all have their values changed to the same number that's one thing to be slightly careful of you might want to make your test a little bit more specific your where clauses but the cool thing is you can update a ton of data in a single database using VBA code and reading values of cells from Excel okay so one more example just to finish off with this video we haven't looked at the delete statement yet so the final example is going to loop over the collection of films one more time and it's going to check if the film genre is awful there's only one example of that type of film in our database I shan't speak its name if that film is that particular genre awful then a film with that name will be deleted from the underlying database so the genre text actor didn't exist in the endline database at all so now we're going to be modifying data in a database access or sequel server based on information that's only contained in a spreadsheet it's quite powerful stuff if you think about it so what we'll need to do first is create a new function that creates the delete statement text so let's go back to the VB editor and scroll down to the bottom of the module and let's cheat again let's create a new copy of this access function copy and paste and then we can make some modifications to this to make it create the delete method or delete statement let's start by changing the name of the function to get delete text access and that means we'll also have to modify how we return a value to the function as well so that should be delete text access or get delete text access as well we can then modify the parameters because we only need to pass in the fill name this I mean only to pass in the film time we're only interested in deleting a film with a specific name so we can get rid of their film time parameter altogether and then we simply need to modify the sequel string that's generated now the delete statement is probably the easiest of all the statements we've covered in this video so rather than update we will say delete from TBL film we don't need to set any field to be a particular value we don't even need to say which fields we want to delete either the delete statement deletes entire records so we can remove that line altogether and then the worst Clause of the statement is exactly the same as last time it's whatever film then we decide to pass in that's what will be deleted okay so that's the name of the function or that set the function itself created now we need to incorporate it into our main subroutine so let's head back up there now and we'll start to replace the parts of this ability and then we need to modify so first of all let's get rid of the part which has gets updated text accessing will change that to get delete text access of course we only need to pass in the film name this time so we can get rid of the parameter or the argument which passes in the film the running time so let's get rid of that and we can probably give that now squeeze the whole thing onto one single line of this much neater we also need to include an if statement in this stability to make sure that we only generate the command text and execute the statement if the film is an awful film so let's add an if they mentioned above this line we'll say if our dots offset 0 comma 4 dot value equals awful then then we need to actually no we don't need to but I'm going to anyway I'm going to indent these lines I need to otherwise I won't sleep tonight and we need to add an end if we do need to add an end if to make them maybe have some incomplete now it's worthwhile mentioning that the the if they one is case-sensitive so if I'm comparing this piece of text into this piece of text if it's not spelt with a capital A then this will work so it's probably worthwhile quickly saying L case I'll offset 0 comma border value and testing that all against lowercase text as well and then this is all we need to do in order to start deleting records from a database so just a quick check back in the access table see that that film is still there the one whose name is not me spoken there it is and if we close that table down and go back to the VB editor and run the subroutine something's happened at least back to access open up the table again and suddenly that film no longer exists that's much better excellent now it's worth mentioning one factor that could affect your ability to delete records and that's all to do with relationships in your database if I quickly switch to the database tools tab and use to view the relationships you can see that there is a one to many relationship between the film table and the cast table in this database what that means is the field here cast film ID can only contain a value if that value already exists in the film ID field of the film table so what that means is if I delete a film from the film table and its ID is removed then any record in the cast have always used that film ID can no longer display that value now by default the way access handles are is it prevents you from deleting the film from the film table if that film has already been used in the cast table now of course the records we've just added in this video have not then been assigned to cast records in the cast table so we can successfully delete any of those but other records so for example if I open up the film table and you just think I think you scroll to the very bottom where I know there are some films with records if I expand that you can see that there are some records in the corresponding cast table there for two villains Rasik Park so I could not delete Jurassic Park by default now there are a couple of ways and rounded back in the relationships window I can modify the relationship to either delete it altogether and that would allow me to delete records or if I edit the relationship in a bit more detail I can tell it what to do if I delete records from the film table if I check this box cascade delete related records what that means is if I delete a film that had records in the casts table now I'm in the middle of using things at the moment I can't actually do that I can't make changes but if I enforced that delete related records fact feature that would mean that if I deleted a film here and it had records corresponding to that film in the cast table all the records in the cast table would also be deleted for that film so taking that a step further imagine we deleted a director with cascade deletes who directed deleted say Steven Spielberg that deletes all of Steven Spielberg's films and then all any old roles played by any actor for Steven Spielberg's films as well so you can potentially get rid of hundreds of Records but just by placing a single one one table so it's something to be careful of when you're deleting records but then you should only be deleting record from a database if you absolute the intensities in the best ways anyway so apart from that it's a pretty powerful feature the idea that you can write code in your VBA projects that can modify all this data in a database in sort of that in the sequel server or Microsoft Access in factually the database type doesn't matter so I think that's probably enough talking about modifying data for one one video hope you found this one useful 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: 67,861

Rating: 4.9383035 out of 5

Keywords: ActiveX Data Objects, Microsoft Excel (Software), Visual Basic For Applications, SQL (Programming Language), Microsoft Access (Software), insert, update, delete, wise owl

Id: -c2QvyPpkAM

Length: 83min 12sec (4992 seconds)

Published: Wed Apr 02 2014