How To Link An Excel Spreadsheet to A Microsoft Access Database
Microsoft Office - Making Connections between Excel and Access
Have you ever been asked to create a database at work and then been told all of the data is already held on an an Excel spreadsheet?
Or even 20 Excel spreadsheets?
Excel is usually the first place administrators will go to if they need to enter 'lists'. The lists are usually made up of things like names, addresses and zip codes. Maybe they are lists of clients. If you work in a school, maybe they are lists of parental information for each child in the school.
Excel does this kind of thing very well. it is a neat and tidy, column and row formatted spreadsheet but there its uses end as a piece of software for 'storing' data.
But Excel spreadsheets can be linked to other software packages in the Microsoft Office suite.
For example, you can copy and paste a graph from an Excel spreadsheet into a Microsoft Word document.
And you can also link an Excel spreadsheet to a Microsoft Access database - and that is when your static list on an Excel spreadsheet becomes something much more - it becomes part of a database.
And when it becomes part of a database, it is no longer static. It becomes an active table of data which you can manipulate at will.
Here is how to link an Excel spreadsheet to a Microsoft Access database.
Open Your Excel Spreadsheet - Tidy Up Before You Begin
Browse to your Excel spreadsheet and open it up (Figure 1)
Take a quick look at it and ensure any untidy elements are fixed.
Maybe some of the data is half-completed or untidy. Sort that out first.
Next, save it in the same place as you intend to save your Microsoft Access database.
This will make hunting down both files much easier.
Once you are happy that the data on the Excel spreadsheet looks ok, close it.
In this example, I have chosen a fairly straightforward Excel spreadsheet with just a few headings.
It is always worth ensuring that you have TITLES in your column headings so you know what is in each column, e.g. Surname, Forename etc.
Linking Your Excel Spreadsheet
Open Microsoft Access.
Select Blank Database from the top of the screen and then give it a meaningful name, e.g. 'Client Info.accdb'.
The next screen will be blank except for the first line of a potential table. Usually, you might be creating a database and then inserting the data into a table yourself.
In this instance, we are inserting the data from an Excel spreadsheet because it already exists. This is going to save us a huge amount of time in creating the database.
In the left hand pane, right click on Table1:Table
Select Import and then choose Excel from the drop down list. (Figure 2).
Linking the Excel Data
The 'Get External Data' screen will be shown which offers you three choices:-
- Import the source data into a new table in the current database
- Append a copy of the records to the table........
- Link to the data source by creating a linked table
Let's look at these choices more closely.
- Choice 1 offers you the opportunity to import and that will bring the data into Access where you will then append any additional client names into the database, actually rendering the spreadsheet obsolete.
- Choice 2 offers you the opportunity to append a currently existent Microsoft Access table.
- Choice 3 offers you the opportunity to link to an existent Excel spreadsheet and maintain that link so that any data updated in the spreadsheet automatically copies over into the database.
For this article, we are choosing Choice 3 - linking the Excel spreadsheet to the Microsoft Access database. (Figure 3)
Click on choice 3 and then click OK
Formatting the Excel Spreadsheet for Microsoft Access
When you see the next screen for Microsoft Access, it will ask you whether the first row of your spreadsheet contains the headings. Mine does so I click in the box to acknowledge it (Figure 4)
Click NEXT and then give the 'Linked Table Name' a meaningful name as requested.
Microsoft Access will then advise you that it is finished linking to the Excel spreadsheet, click OK
You will now see your Table containing your Excel data on the left hand pane of your computer screen.
And that is how you link an already existent Excel spreadsheet to a Microsoft Access database.
Linking Excel Spreadsheet to Microsoft Access Database
Linking from Excel to Microsoft Access Turning Static Data into Dynamic Data
You have basically re-used data which already existed in a different application and been able to use it to manipulate the data in a proper database.
The data was static but is now dynamic.
If you needed to, you could now run queries on the data.
- e.g. Get Microsoft Access to do a query to show you all of the clients who did not give their address and chose email instead.
- Get Microsoft Access to do a query to show you all of the clients called Smith.
You can also run reports in Microsoft Access based on the client list. Maybe you want to send out mailshots?
- You can ask Microsoft Access to create a mailing list report to use in Microsoft Word.
You have just made your data more useful by linking from Excel to Access.
I hope you found this useful. Many thanks for reading.
More by this Author
How to Add a Check Box in a Microsoft Word Document. A useful guide to how to add a check box to a number of Word documents, including lists, questionnaires and surveys.
How to make an e-card on Microsoft Powerpoint. Create a great Christmas e-card to email to friends, do it all on Microsoft's presentation package, Powerpoint.
Thomas More's Utopia was humanism in renaissance literature. How much was More influenced by the Renaissance? Like other humanists in the Renaissance, he looked to the future influenced by the past.