Often times, it is far easier to input raw data into a spreadsheet. Because of the table like format, copying and pasting can be done quickly and efficiently. Add to that the ability to copy formulas down thousands of rows with a quick double-click (if you know where), and you can create the data much faster than you might be able to in Access.
However, for tracking that data, analysing it, and reporting it, Microsoft Access is the much better tool. Beyond that, Access allows for relationships between data, which comes in very handy for analyzing when specific products were added to specific orders for a specific customer. Tracking that in Excel is possible, but would be a nightmare to maintain.
So, what do you do with that data that is so easily entered into Excel when you need the analyzing power of Access? Really, you have two options here. One option is to link to the Excel spreadsheet. The other is to import the Excel spreadsheet.
Linking to an Excel spreadsheet from within Access is a viable option, but is not one I recommend on a normal basis. The problem for me is that you are splitting up the data into multiple sources. If that excel spreadsheet were to get moved or deleted, the data would be unusable and anything in the Access database that is depending on it would stop working correctly. Plus, Excel doesn’t have all the bells and whistles of an Access table, such as preventing duplicate data from being entered into a field that is being used as an index. In Access, you can, and normally would, define that Product 1 is named “Sample.” In this case, you do not want another product to have a number 1 and perhaps you do not want any other product to be named “Sample.” You can do this easily from within Access, but not in a linked Excel spreadsheet.
The better option in most cases is to import the spreadsheet and store the data within an Access table. You can then set that table to have the indexes in place to make sure duplicates are not imported into the table. When an import attempts to do so, Access will throw off a warning and the data will not be imported.
Importing data from Excel into Access is a fairly simple process, if you only want to do it one time. At the top of the screen, using 2007+, in the External Data Ribbon, in the Import & Link section, you will see the button for Excel. If you read my post on splitting a database, this should be familiar to you, except in that case you were linking from Access. The Excel button will run you through a wizard, where you will find the correct Excel spreadsheet and be able to make sure that the fields being imported are of the correct data type (i.e. dates are not being classified as text and so on).
A word of caution, before you begin the import process. Blank cells do not mean empty cells. The import wizard will import any fields that it believes are populated. If you ever entered information into cell H2 in the Excel spreadsheet, even if you erased it later, Access will import the entire H column as a field. In order to avoid this, before you run the import, select the entire column by left clicking the column letter at the top, right click the column letter, and select “Delete.” This will delete the entire column, but the columns will rename themselves, so in the sample above, H would be deleted, I would become H, J would become I and so forth. This works for multiple columns, just select the first column, hold the shift key, and select the last column. It also works for rows.
Importing an Excel spreadsheet can also be done programmatically through VBA code, but I will be posting that next week, so stay tuned!