Entering records directly into Datasheet View is probably the most intuitive way to enter data into a database. In Access, this is common because many Access databases are considered single-user databases. With multi-users databases, data integrity policies would discourage most users from having editing rights directly to the tables. Therefore, in these databases, the more common method for entering and editing records in a database would be via a form. Nonetheless, entering records directly into Datasheet View has some advantages, including:
- Users can look at their data in columns and rows in a datasheet without using any special formatting.
- When a user opens a table in Datasheet View it resembles an Microsoft Excel worksheet, and the user can paste or add data in one or multiple fields. Data can be copy and pasted from a Word table or Excel worksheet into Access.
- Printing a table’s datasheet in Portrait or Landscape orientation. Use Print Preview to change the orientation and margins before printing. Output can also be sent to a PDF file.
- Users don’t need to explicitly save their data. Access commits user’s changes in the table when they move their cursor in the same row towards a new field, or if they move their cursor towards another row.
- Sorting and filtering data is very similar to Microsoft Excel.
Access allows users to sort data in Datasheet View one iteration at a time in either Ascending or Descending order. Ascending order is also referred to as alphabetical order when the sort is based on a text field, chronological order when the field is a date/time data type or numerical order when the data type of the field is Number. Sorting a datasheet does not affect the order that the records are displayed in a form.
Displaying records based on specified criteria is called filtering. When a filter is applied, records that do not meet the specified criteria are hidden from view until the filter is removed. To filter records based on the data in a field, click in the field to base the filter on and then click on the Filter button on the ribbon or click the drop-down triangle to display a menu similar to the graphic below.
To remove a filter, clear the check boxes, and click OK or click the Filter button again or click the Advanced button on the ribbon and choose Clear All Filters.
Most databases contains hundreds, if not thousands, of records. Therefore, when a table becomes so large that the records and data do not fit in the datasheet window without scrolling, the Find feature becomes very practical.
In Datasheet view, use the Find command, from the Home tab, to quickly locate specific records using all or part of a field value entered in the Find What: field. If in the States table above, if the user entered the value North in the Find What field, Access would find two records for North Carolina and North Dakota, but only if the Match: value was changed from Whole Field to Any Part of Field or Start of Field. Using wildcard characters can be extremely helpful if the user does not know the exact spelling of the value they are looking for.
A wildcard character can replace a single character or multiple characters, which can be both text and numbers. Clicking the Replace tab will allow the user to replace the data in the Find What: field with the data entered in the Replace With: field. This could be very useful when updating name changes, such as when Kentucky Fried Chicken changed their name to KFC in 1991.