Setup Access to Link to/Import from Excel Spreadsheets

hey everybody thanks for joining us in this video we're going to discuss linking and importing Excel spreadsheets to access databases so the situation we're describing is you have an Excel spreadsheet that you want to utilize as a data source and you want to be able to use that in your Access database when you create when you link a spreadsheet to an Access database to access it looks like it has linked to an external table a table in another database somewhere alright and you create an active link or an active connection if you double clean the database in the spreadsheet when you open that linked table in your database it pulls for you a copy of what's in that spreadsheet at that moment in time it does not have a create such an active link that if you update the spreadsheet while the table is open you'll see those changes were a fighter you'll have to close a table and reopen it to see the changes reflected now contrast that with importing a spreadsheet into a database when you import a spreadsheet into a database what you make is a copy of that data you make a local copy of it in your database at that point going for there's no further connection between the Access database and the spreadsheet so if somebody modifies a row in the spreadsheet or adds a few Pro to the spreadsheet those changes will not be reflected in your local copy in the database your reopening closing and reopening that's that table that link that that imported table will not change that it will still reflect what you originally imported so if you're going to import spreadsheets into databases and you're expecting the change you're gonna come I need to come up with a strategy to to update them every so often so let's go over to the spreadsheet we're gonna use as a data source I've made a very small table here in a spreadsheet and actually I want to apologize I inadvertently made a a United States centric spreadsheet here this is a I'm calling it vendors we have a vendor ID in a name and some address fields and I've got cities and states over here apologize to those who are not the United States for that I am going to close a spreadsheet for the moment and open our database and the first thing I want to discuss is linking linking to the spreadsheet so to do that we will go to the external data tab and we want to link to an Excel spreadsheet so we'll click the Excel button we want to choose a radio button for link to data source by creating a link to table and then we're going to press the Browse button to go find our database and I'm calling it called it vendors so all we do is click the Open button here ok so we've got our file name there we're saying link and we're click OK now it's gonna give us some choices our spreadsheet has three sheets in it only the first sheet is what we're interested in that's one one has data in it so we select our first sheet click the next button it's asking us if the first row of data is actually column headings now when I created a spreadsheet I was careful to not put spaces in the column headings the reason I did that is on the access side if you're gonna work with this table and have these as column names if you have spaces embedded in them you have to put brackets around them and if you write careers on there what not and this makes it easier not to have spaces in there in my opinion we're gonna click yes the first row contains column headings you can see now it turned that first row into column headings for us I click Next it's guys because what named we want to appear in our table list in Access I'm going to say vendors - linked because we're also gonna produce we're also going to create an imported version in a minute I want to build competitive so vendors link to my press finish and it tells us they've finished like a double click on our table and we can see what was in our spreadsheet all right now I'm going to while this table is open in access I want to double click on my spreadsheet and try to open it and we get a message here that spreadsheet is locked for editing by another user so you're gonna have some contention issues when you link to a spreadsheet as you can see I'm gonna put click cancel I don't want to bother opening it now close I'll link a copy of the table so now let it open up our spreadsheet slide it over here so we could see both the table and speak to you at the same time what I want to do is I want to add a new rule over here I'm not gonna be very creative because I'm doing this when you add a new row I mean we're gonna save it over here in the spreadsheet come back over here to our database and go to the home plus press refresh it's not pulling in the new data we need to close and reopen this linked table in order to get this refreshed copy of data all right I'm going to close this table I'm gonna delete that row from the spreadsheet save it and close it and see that fifth row is gone again and now I want to contrast this to importing that same spreadsheet into another table so again on the external data tab will click Excel I'm gonna browse to the same folder same spreadsheet this time I'm going to say import the data source now when we do this it's gonna actually need to create a table for us because we don't have a table in here for it to this day to end right now we've the same choices we use sheet 1 go to next again the first row contains column headings go to next now since it has to build a table for us it has my few more questions to ask us it's asking us about the values in this vendor ID row so we're gonna leave leave the field name the way it is it's suggesting a double data type now I don't need this to be a double data type and my example I'm intending these to be whole numbers so instead I'm gonna choose long integer not knowing how big they might get and it's asking us about indexes our duplicates okay or our duplicates not okay or is it not indexed at all so here's we have to decide what you want your primary key to be in your imported table do you trust whoever is building this spreadsheet to have always have unique values in this vendor ID yeah and if you do you can say yes it's indexed and no don't allow duplicates and you can make the vendor ID be your primary key and if I were building this table from scratch and we're gonna only reside in my database that's exactly what I would do but not trusting whoever's gonna just take this table for us in a spreadsheet for us rather I'm gonna choose yes it's indexed but duplicates are okay and watch what happened and we'll see what happens on the next screen let's click through the rest of these the rest of these we're gonna leave us text text text text and that's fine and that none of those need to be indexed the next next screen here and now it's asking us about the primary key had we chosen on the previous screen that the vendor ID was gonna be unique we could say twos my primary key and make a vendor ID our primary key I decided decided to allow to allow duplicates in that column so I'm gonna say that access create a primary key for me and that's what it's doing over here in this left side an ID column over here I'm gonna click next and it would get to name it and again this is the names gonna show up over here in our navigation bar I'm gonna call it vendors important and click finish now this it's asking you asking us do we want to say the steps we just created and I'm gonna say definitely yes and the reason why I want to say that is if we think that that spreadsheets gonna be updated in the future we might need to wipe out our copy of their our local copy and re-import that that spreadsheet to get the most updated data and if that's the case if we have these import steps saved it's extremely easy to rerun that import I'm gonna rename this though import vendors and I'm gonna call it reload okay using this step here's in a situation where we would be replacing the entire table and I click save import all right let's take a look at that table now this is a local copy of the spreadsheet it's it it at this point is not related to the spreadsheet anymore except that that's where the data came from we can do whatever we want to to that spreadsheet add rows change data they will not be reflected in here unless we completely reload this table or append to it I want to discuss appending it I'm gonna close this copy at the table I say let's pull any more data from that spreadsheet let's pretend that that spreadsheet that we've been working with has additional new rows we want to add into this table let's browse to it very quickly so what I'm saying is the owner of that spreadsheet has has created a new spreadsheet and that person is saying I've got four more rows 4 new rows then you need to add to your table you could give it a delta spreadsheet like that I'll pin those rows to your table so on our input import choice here we'll choose a pinned and when I say a pinned in two vendors imported that's how important click okay I could go through the same setup that we went through before okay now it knows about the first row B and column headings click next import the tables table vendors imported I don't care for to analyze my table after importing ok finish again s asked us to save the import steps I'm gonna say yes if you want to be able to rerun your your append easily and I would say yes do that and I'm gonna change the name to improv endures append now take a look at what happened I told it to append the contents of that spreadsheet so it's added these four rows to the NMS spreadsheet this wouldn't have worked had I set up vendor ID as a primary key it actually would have protected us from bringing in these duplicate values if I have set finger ideas as a as a primary key that does not allow duplicates so that would be the disadvantage of doing what I did making the choice I made here of a lot of having access create a a different primary key for me now let's talk about updates to existing rows say your spreadsheet owner has a cited that that act that address has been wrong for a long time it's not 29 watch waste 293 watching way alright and they they change this existing row in the spreadsheet now you'll prep your linked version and it pulls in the current data because it's got an active link it can it goes looks at that spreadsheet before it displays this data and it's got the new didn't the new add address but your imported version it still has the old address cuz this is a local copy that has no relationship to that spreadsheet so if we want to pick up that new address in our local copy of the spreadsheet our imported table we need to rerun that complete import the way we get to those is our external data tab and the saved imports button so this is the import we built for reloading or pulling in the entire spreadsheet into the table again so we select it click run it's warning us that when it runs us is going to overwrite all the data that's in that table right click yes and let it run open our table you can see that we're back to the four original rows they're in this spreadsheet right but it's pulled in the new is pulled in the new address the 293 address which is good we could just as easily close this and rerun our import renders append if we do that it's gonna add those four same rows a second time it's just not what we really want but we'll do it just to show it right now we've got four additional rows the rows repeating so hope that I've adequately explained the difference between linking and importing spreadsheets into Access databases hope you got something out of this and we will see you next time Thanks

Channel: Access Jitsu

Views: 17,021

Rating: 4.7586207 out of 5

Keywords: Microsoft Excel (Software), Microsoft Access (Software), Microsoft Office, Link spreadsheet to Access, Link Excel spreadsheet to Access, Import spreadsheet into Access, Tutorial, Software development, Set up a spreadsheet like a linked Access table

Id: QFl0qiCd8rk

Length: 13min 29sec (809 seconds)

Published: Sun Aug 30 2015