This something I see from time to time and I thought I’d give some pointers.
Although Microsoft describes them as two totally different tools, moving data between this is a lot more straightforward than you might think.
Why?
Well it is to do with how both of them are organised. Access is a database with tables and organised records. Excel in its own way is also a database in that we can organise the information on a sheet.
Let’s look at a quick example.
In Excel you have a spreadsheet of customer details. The best way to organise this so you can sort it or search for specific details is to put headers at the top of each column.
So we use Name, Address,Telephone Numbers as headers
Access is a database and is organised in tables. Each table has records and record have fields. In order to be able to do anything useful with the database we need to know what each part of the record or field is called.
So our fields might be called Name, Address,Telephone Numbers
At this stage you can probably see the similarities. Even though both are different programs the organisation of the information inside them is exactly the same.
How?
From Access to Excel
This is where the technology comes in. When you save an Access database, it is saved it as an .mdb file type. So the file name might be mydb.mdb.
Excel can open the .mdb files that Access is saved in. When Access saves the file it also adds the field names to the file. When you open the database mydb.mdb in Excel these field names now become column headers. Now you can save the database as an Excel spreadsheet.
That’s how you do it. Save the database to a location you want like C:\My Documents. You will probably need to use the Save As function in Access.
Open File -> Save As
Then open the file in Excel. The .mdb option is under Access databases in “Files of Type” when you click on Open in Excel.
From Excel to Access
As I said earlier using headers is exactly the same as having fields in a database table. In the same way that Access will give you column headers in Excel adding an Excel spreadsheet to Access will create those fields and add the information to them.
This is extremely useful if you have a very large spreadsheet as you can automatically transfer all of it to the database. Access will also produce a report on any records or details that are not added to the database table.
The term for what we are doing is Data Import and this is how we do it. Import is under the File Menu
File->Import
Once the Import has finished Access will create another table with details of any records that have failed.
Of course not everyone has a single sheet. However when you import a file with multiple sheets, Access will ask you which you want to use.
Multiple Worksheets
For each sheet Access gives you the option of creating a new table. If the multiple sheets contain the same headers then you can import each of the sheets into the same table. Unfortunately the simplest way to do it is manually and as you will see Access will only import one sheet at a time before closing the Import Wizard. After each import you need to reopen the file and import the next sheet. To import a number of sheets simultaneously requires some programming and is beyond the scope of this article.
And that’s it. The spreadsheet with 3000 names has now become a database table.
