Creating Access Forms To Work with Excel Data
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
I'll get my bias out of the way right off. I think Excel is the greatest application ever. If this were a perfect world, I'd do everything in Excel: budget, write, balance my checkbook, answer my email. Unfortunately, as nearly perfect as Excel is, it isn't the best tool for every task.
Basic Excel Databases
Excel does a fine job of maintaining simple databases. It's fast and easy to learn. As long as your data fits this description, it's officially a "database:"
a single list
headings for each column in the first row
each row a unique value
no blank rows
This kind of worksheet table is called a flat-file database. If your needs are that simple, there's no reason to change. But at some point you'll probably bump up against the limits of Excel as a database manager and wish for the sort of flexibility that Microsoft Access provides.
Let's use an equipment inventory as an example (see Figure 1). This database started out as a simple list in Excel, but now we might want to add more complex information, such as who uses the equipment or its maintenance history. If one piece of equipment is assigned to different people over time or has multiple service incidents, it would be awkward (to put it mildly) to try to maintain that kind of detail in a single list. Better to create multiple tables, linked by unique elements such as an asset tag number.
)
Figure
1Before getting too carried away with planning your new Access database, you need to decide what to do with your existing data in Excel. Is it possible to move completely to Access, or do you still need to maintain the database in Excel for backward compatibility? (For example, maybe the person who maintains the data doesn't have Access or doesn't know how to use it.)
Excel data can be imported into a new or existing Access table, or it can be linked into a table to make the data editable by either application. This article covers importing and linking, how to create a basic data-entry form to view the data one record at a time (rather than in table format), and using filtering and sorting.