Of how to you know I join these two tables and bring in attributes related to this client we can try to find out top 20 top 10 clients top 20 clients and then we can bring in the information or we can do like like a summary of this huge data set that we have around seventy seven thirty thousand records so if you have to even just open this file it's gonna take a lot of time if you do to a normal method like open the file.
Activate the sheet copy the data and so on okay in this way we are not gonna open the excel file itself we're just gonna connect to the excel file using the connection a do connection and we're gonna you know like pull the information write the data to the table or update the records okay now just before we begin if you go to vbe to that YouTube.
Or YouTube channel and the search for it's got a video section and search for a do active data objects you'll see a lot of examples that I've already done for SharePoint Server access database and all this how to update how to add new data how to look through the records so many examples ok so here once you know how to work with any of this database it's the same all across the.
Way you'll be working with ok and I've just done a very high-level here so there are so much more you can explore it's so so powerful ok now if you go to my blog and search here for a do there are many sample codes that I use for all these videos have been uploaded here so you can directly copy and directly start using it for example here this must be updating the salary filled with 50,000 as a demonstration there are some sickles I'll also give you a bit of.
Go in there and if you scroll down here so here it has string for XLS eggs for trading data as tags XLS B XLS some and all okay so for now since or our file that we are trying to use its XLS eggs I'm gonna copy the XLS extreme again and we'll start off with a new file let me just save this file is I'm gonna save this file here as a do.
Demo so I have removed all my customization I'm doing some lesson for absolute beginners you must abstain if anyone is looking for word to learn new VBA they can come to the community tab here and use this link to sign up and I'll be sharing the lesson as soon as possible so if you have anyone who is interested please share the link with them that will be helpful so this is our.
Connection string we need and looks like ok put it here so we have the connection string first step for us here will be to add a reference to a deal so Microsoft active data objects whatever is the highest you can check those ok again you might not use this often but this is gonna be very handy if you do not have much option with you only have XLS your.
Option then it's gonna be very useful ok so this connection string we need this this is where our file part goes so let me get this and update it here okay so that's our file pod headers years this is I'm just gonna leave all this as default so first thing we'll need is two because we'll we are going to reuse the connection and again and again instead of setting up a connection for every process I'm gonna write like a create a.
Public connection and then all these processes that will be creating can refer to this connection so first let's set up the connection so let's call this connection X as adodb dot connection and then we'll write our first function will be to open the connection or create a connection open connection something like this and here here we can set up this connection.
Posts Related:
- Magazine Mockup Pack Download In PSD Files Sheri Sk Magazine Mockup PSD
- 100 Professional Identity Card Templates Download In EPS Files English Illustrator Tutorial Office
- How To Design Stories Using HTML, CSS & JavaScript - Part 1 BlogSpot
Set this connection connection and then - ID Card Make
With this connection we can set up with dot connection string is equal to this and then here we can say open okay and we can say spool in if in case it errors and then we can.Okay and we need to close function function to close as well let's call this close connection maybe connection Excel will call this connection Excel and here we can check if the status is currently open and then we can close the connection after executing the process again so we'll need to see if this third state and then dot gonna be add state of.
Them okay so we'll see if simple basically validate if these conditions are true if that's true then we can close the connection and I think we'll just return show give it errors we're not expecting it to ever but anyways let's keep it consistent.
true and false okay so now our connection is ready yes okay so if this is true clothes else don't do anything okay now let's give it a shot so you don't need to have this all open well first try maybe writing it to this this table so the sheets which you adhere will be considered as a table and.
The first one will be the header I'll show you like ways to refer to this okay now let's first write a code and then we'll close this and we'll try to see if it updates let's first write the update method let's call this update table let's make this not update let's first try the insert we'll add a new data here so let's try the sequel afterwards let's.
Say insert 0 equal to a string sorry this is what happens when you call in multiple places so our insert sequel will be the syntax is very simple so insert in the table and then the fill names where you want to insert and then you specify the values what do you want to insert in those fields ok so insert into and then we want to insert into this transactions table so I'll copy this so one thing to note here.
Is when you're working in shape when you have to specify these brackets square brackets and then when you're working with Excel you have to specify the tables with a dollar sign like this okay in the end and then here we specify the field names so we want to insert into these fields so let me just bring it here so this field as well let's put them in this format so these are the fields.
Where we want to insert the data to okay and then now we can specify let's close this bracket values and then this is where we specify so in the same order that you specified here you can specify the values as well so for date client ID and the amount okay so let's say that we want to insert for client seven so values first will be to date.
Okay so let's specify the date date we'll have to go in a proper date format so you can specify maybe today's date which is 12 July 2020 and the third one is going to be the amount okay so it can be any random amount all the amount will go without the already now this so let's say we want to like this is a number that you want to write okay without any double quotes okay so this.
Is our C code basically now going quickly going through it again insert into this table okay insert into these
Fields and in this order okay this feels and then these are the values for this field so in a date will write this value incline we're gonna write this and then amount we're gonna write this okay now there are ways to like directly export or basically select everything from one table and then you can directly write it to that table as well they are examples like this as well.In the channel so just check that out now what we need to do is we need to use this connection so first of all I have to open the connection so we'll this a connection open and the connection close okay so this will take care of opening the connection and closing the connection now with this connection so if this is set up correctly alright then we will have this connection active and.
Then using that connection we can execute this sequel okay so this is a sequel and then you can also see the number of Records it affected so let's try this this is okay I'm not going to use anything else for now let me close this so now here we have 1230 client is 500 I'm gonna save this this file is quite heavy if you look here this is 79 be at the moment.
Yeah I'm gonna close this and let's see if it works first I'm gonna just press f8 and go through it let me quickly check one thing so this has to end with this okay semicolon and 10 so let's see if it opens the connection the file part is fine this okay okay the the connection open so the property is now set to true it's gonna execute this particular string SQL and.
Then once it's done it will go into this line just give it a moment now we can close the connection okay so that was good I just need a exit function here all right so now let's try one more for the same date let's try for Klein eight and let's see the amount is this no I'm gonna straight forward run it and then.
We'll open the file and verify it okay so it's done let's open up this file okay so you can see here we have added two records for this date for client seven and eight and this is the amount okay so that's a very simple way to add using SQL come on now for all this sequel I find let's say left join.
Something like this you'll lend to W trees cool calm not all of them will be relevant for this particular Axl but there are very good examples that you can use I find it very easy to follow their tutorials so I kind of recommend them this side for example insert insert into sequel okay here insert into this table these are the columns and then if you have more columns you could specify more columns.
Values and then this and then the semicolon okay so this is how you can do it and there are examples you're gonna actually practice it here yourself if you want to gain some expertise around this okay but it's very simple sigil is very simple so it's just give it a try and then it should not be a problem now let's try one more method of adding the data maybe this method I'll just copy this we're not going to reduce all this but so that we don't have to rewrite and.
I'll quickly talk you through what is it so here we already have we don't need a set of connection so here is what we need to use this connection to open our database I'm gonna remove all the loops instead of this if you okay let me just first set it up so open the connection close the connection here.
The reason for me showing this is so that you can directly go and copy this and reuse most of them we're not gonna add any handler so here we are this is we are declaring RS basically this is the table stands for a corset and then here we are setting up what is our record set and then we are opening this record set now the table we want to open is transactions and not done so here you.
Can specify like this as well select all star represents all from this table okay you can also specify like this or you can specify a circle here and then pass the circle here so open this table and then using this statement and then using this connection and this is gonna be the cursor type and a long time okay so here you can see cursor time this is the log type so if you want to edit then you.
Have to open up in this argument so yeah so in this part is saying in this table add new using add new method and then for these fields we are assigning these values so if you if we try in this similar fashion so for the date I don't remember trying this out in Excel using this method but let's give it a shot if it works good that.
So first is date let's say again I will put it for the same date for a different client maybe this time line and a mark will disturb it let's say the man is like this now you can leave this open as well there should not be a problem okay gonna.
Give it a shot syntax error in from close just give me one second this method seems to be causing a bit of problem with the sequel let's try like it is in the zone here we'll just specify the table like this source and the connection and the cursor.
Time they this has to be the client ID the field name this can be return written also as in this format dot field and here you can specify the file name for example client ID you can also specify in this fashion okay but if the file name is straightforward like this without any gap then you can use this format so let's say we want to try for.
10 always longer as compared to the subsequent ones let's see if it rot any value here okay so that seems to be the value we try to write for 58 point end and then the date okay so this method also works but I don't know why this part is not able to make it work but in.
Order database and you know servers it should work okay so this is two ways to update in case you have to look through and update many records based on the table then you use the update batch method okay instead of this and you don't have to update every time after you write all the values you can update a batch as so on here okay so if you look here it's looping true two to the last row and then adding the records for example so you use this method if any.
One of you fine see this error there is you know a way to handle this as well so what sort for that now let's try update method and then we'll go to how to import all the data let's call this will just replicate this but instead of insert we'll use update update records so here the updates you call if we just want to not just give you a demo update.
Sequel so update table name the column the value okay and then call them to really if you want to update more and then we're condition okay so I'm just gonna keep it here X is equal to this and then put it like this for now and we want to update this table and let's say that we want to update the amount field and we'll update the value.
Something like this oh this remote this for now and then it's no space now let's say we want to update this value so our condition let's say that because there are many klein klein 1 repeating here let's say we want to update this particular report so our condition will be wear date is equal to this and then the client is equal to this ok so let's try this.
They do circle to this again this date has to go in this format and like we do in any other VA we'll just put in the condition I'm sorry this has to be like this and you called it this okay so update this table set the value this to amount where the condition did is this and the client ID is this let's.
Give it a try now instead of instead of update we could just call it s Q alone that's fine now we don't make this update we don't need their insert so open the connection using the connection execute and then let's say we want to know how many records are impacted we can declare every variable as long and here we can.
Specify so here is the command and records affected okay we can call this request experient oops sorry this is called a dis and maybe we can just give out dot print records impact account let's say just leave this open for now it's the same though so I sprinted here.
One record is impacted and if we go here this has been updated here okay so that is working you can follow this similar method as well to update for the sake of time I'm not gonna go through all of them but here for example instead of adding new you can just remote this and you can loop true and then you can update records okay so there are two you know multiple ways to update as well so you can use this Eco command or you can use by going through the records by the.
Way you can open specific view by passing your filters like this and then you can update those records as well there are I can please refer to the blog there are many examples how to look through all the records and all this okay now I'll demonstrate a few examples how to import data first let's replicate yeah I think this process let's call.
This import data let's say that we want to import all the data from this clients table first give it simple so we'll call this team SQL as string to call is equal to so like again this star is basically all select a strip from this table and this.
Instead of the fixed name we'll specify this we need to close the table so opening the table with the source connection again here we are opening the connection and then we're setting up the record record set which is basically a table and we're opening that instead of opening you know like the entire table you can also specify to open a specific filter data right so that it's faster in.
That way and then these are the log type and the culture type I'm just gonna leave that as it is this part's close the table clears the memory and close the connection you know now I think we'll be good idea as well for us to clear so that it does not create a memories yeah um okay and here so basically we are opening this entire client table and then from here we can use a metrical.
Copy from record set and first we need to specify where we want to import the data to so we want to import it to sheet 1 so and maybe for now sorry we'll import it to 1 ok copy from and then RS so this is what we are copying to this particular cell ok whatever is the result of this Tikal ok so let's try to run this.
