Transferring the Excel Spreadsheet to an Access Database

Many of the world's biggest databases began as spreadsheets. When the time comes to build a database application from your spreadsheet, you can resort to a tedious manual method to transfer the data, or you can use your new VBA programming skills to automatically turn your spreadsheets into database tables. Once in a database format, your Excel data can be used in advanced company-wide reports or as a stand-alone application (needless to say, the latter requires that you possess database application design skills). The remaining sections of this chapter demonstrate how to link and import Excel spreadsheets to an Access database. Prior to moving your Excel data to Access, you should clean up the data as much as possible so the transfer operation goes smoothly. Keep in mind that each spreadsheet row you'll be transferring will become a record in a table, and each column will function as a table field. For this reason, the first row of the spreadsheet range that you are planning to transfer to Access should contain field names. There should be no gaps between the columns of data that you want to transfer. In other words, your data should be contiguous. If the data you want to transfer represents a large number of columns, you should print your data first and examine it so that there are no surprises later. If the first column of your data contains the field names, it is recommended that you use the built-in Transpose feature to reposition your data so that the data goes down rather than from left to right. The key to a smooth data import is to make your spreadsheet look as much like a database table as possible.

0 0

Post a comment