Locked lesson.

Upgrade

About this lesson

Explore the options for designing in the datasheet view.

Quick reference

Working in Datasheet View

Explore the commands on the contextual ribbons when working in datasheet view. 

When to use

It's important to understand the commands available on the contextual ribbons and the limitations of using Datasheet view so we can make an informed decision regarding which view to use for certain tasks. 

Instructions

To switch to Datasheet View

  • From the Home tab, click Views and select Datasheet View. 
  • OR, from the Table Tools tab, click Views and select Datasheet View. 
  • OR, click the Datasheet View button in the Status Bar. 

Using Datasheet View to Design

Datasheet view is used predominately to add records to the table. However, it can be used to control some design elements also. It's not as flexible as Design view and the changes we can make are minimal. Let's explore the options we do have if working in Datasheet view is our preference. 

The Table Fields Contextual Ribbon

Notice in Datasheet view, we have two contextual ribbons: Table Fields and Table. 

  • Click on the Table Fields tab. 
Switching Views
  • Click View to toggle between Datasheet View and Design View. 
Adding and Deleting Columns

We've already seen how to add new columns to the table by clicking the Click to Add column at the end. This will always add a new column to the end of the table.

If we want to add a new column in the middle of the table, we can do this by selecting a data type from the Add & Delete group. 

  • Click on the column to the left of where you want the new column to be added. 
  • Select a data type from the Add & Delete group to add a new column. 
  • To delete a column, select the column and click Delete. 

Modifying Field Properties

We can modify the field properties in the Properties group. The properties we can change here are minimal compared to Design view. 

  • Select the CustomerName column. 

Notice the active buttons. We could change the Field Size, set a Default Value, or add a name and caption. The other buttons in this group are greyed out/not available. 

  • Select the Priority column. 

This column contains the drop-down list that we created using the Lookup Wizard. Notice that an additional button, Modify Lookups is now available. We can use this to make changes to the value list. 

  • Select the Description column. 

Notice the Memo Settings button is now available.

We have two options: Append Only or Rich Text.

Append Only means that users will only be able to add new information to the description and not edit existing information. Rich Text means that users can change the existing description and use formatting such as bold and italics. 

  • Select the Charge column. 

We can make changes to this field using the commands in the Formatting group. We can change the currency, change the format and increase and decrease the decimal places. We can also see at a glance the validation options set for this field. This field is required and indexed. 

  • Click Validation and select Field Validation Rule. 

This will show the current validation rule set for this field. 

  • Click Validation and select Field Validation Message. 

This will show the current validation message set for this field. 

Using the Expression Builder

It might be that you need validation to consider more than one field at a time. For example, in the Charge field, the current validation rule states that the minimum charge has to be greater than 50. However, what if we also want to set a rule that says the charge cannot be greater than 2000?

We can use the Expression Builder to set this up. 

  • Click Validation and select Validation Rule. 

The Expression Categories list shows the fields in the current table. We can use these fields to build an expression and create a rule. 

  • Double-click on the Charge field. 
  • Type '<=2000' (less than or equal to).

The expression should look like this:

[Charge]<=2000

  • Click OK. 

Now, let's add a validation message. 

  • Click Validation and select Validation Message. 
  • Type 'Charge must not exceed $2000'. 
  • Click OK. 

Test the validation is working. 

  • Change the Charge for the first record to '3000' and click elsewhere in the table. 

If the validation is working, we should see the validation message. 

The Table Contextual Ribbon

The Table ribbon mainly contains commands that are outside the scope of this course such as creating macros and adding events. However, we can also access table properties from here and see any relationships we've created between tables.

We are going to look at both of these in more detail later on in the course.

Hints & tips

  • In the Add & Delete group, click More Fields to see the full list of data types. 
Login to download
  • 00:04 In this section of the course,
  • 00:05 we're going to explore Datasheet View in a bit more detail.
  • 00:09 And we're going to start out by talking about designing in Datasheet View.
  • 00:13 Now currently,
  • 00:14 I am in Datasheet View I can see that it's selected when I right click.
  • 00:18 Also remember that you can switch your views from the Home tab
  • 00:22 in the Views group down here, so I have a choice of datasheet view or
  • 00:26 design view for this particular table.
  • 00:29 Now when it comes to designing your table, I will say that design view is
  • 00:34 much better you have more options than you do in Datasheet View.
  • 00:38 But that's not to say that you can't make minor changes to the design or
  • 00:43 the way that your table looks in Datasheet View.
  • 00:46 So let's explore some of those options.
  • 00:48 So once you're in Datasheet View,
  • 00:50 one thing you'll notice is that you get a couple of what we call contextual ribbons.
  • 00:55 And these are ribbons, that only appear when they're needed.
  • 00:59 So we have table fields and table at the top here which
  • 01:03 are exclusive to when you're working in Datasheet View.
  • 01:08 Now on this table fields ribbon, if we start at the beginning we have yet
  • 01:12 another way to switch between our different views.
  • 01:15 And then we have an add and delete group.
  • 01:19 And this allows us to very quickly add or delete columns from our table.
  • 01:24 So, for example, if I wanted to add another column to this table I could
  • 01:29 come down to my table and right at the end here we have Click to Add.
  • 01:33 And we did actually use this in one of the previous lessons, you can simply click and
  • 01:37 select the data type and then type in your data.
  • 01:40 But the disadvantage with this method is that it's always going to add a new
  • 01:44 column to the end of your table.
  • 01:47 If you want to add a new column somewhere in the middle,
  • 01:50 this method isn't going to work.
  • 01:52 So that is where we would use this add and delete group.
  • 01:56 For example, if I select the Technician column,
  • 02:00 if I want to add a column to the right of this column,
  • 02:03 I just need to select it and then choose what data type this column is to be.
  • 02:09 So maybe I want a short text column.
  • 02:12 If I click that, it's going to add that new column to the right.
  • 02:15 And all I need to do then is add in a field name.
  • 02:19 I'm just going to put test in there for the time being.
  • 02:21 So really nice and straightforward.
  • 02:24 Similarly, if I want to delete a column, I simply select it,
  • 02:28 have a big hold at delete button.
  • 02:30 If I click on this,
  • 02:31 it's going to ask me if I permanently want to delete this column, which I do.
  • 02:35 And there we go, it's gone.
  • 02:37 Now moving across to the properties column,
  • 02:39 you'll notice that most of these options are grayed out.
  • 02:42 So it means that they're not available for the field that I'm currently clicked in.
  • 02:46 So I'm clicked in row 1, record 1 of this table.
  • 02:50 And the only option that's available to me is Name and Caption.
  • 02:54 So I could come in here, I could change the name of the field, the caption for
  • 02:59 the field and also the description.
  • 03:01 Now if I was to click, let's click on the priority column for
  • 03:05 example, notice that I get a lot more accessible buttons.
  • 03:10 So now I can change the name and the caption,
  • 03:13 I can also set a default value and I can also set that field size property.
  • 03:19 So instead of having to go into Design View in order to change that field
  • 03:23 property, I can do it when I'm working in Datasheet View as well.
  • 03:28 And you can see that currently this field size is set to 40.
  • 03:31 Another thing you'll notice is if I click on the Customer Name field,
  • 03:36 notice that the Modify Lookups button is grayed out.
  • 03:39 And that's because this field doesn't contain a lookup, a drop down list.
  • 03:44 But if I click on Priority, that button is now active and it means that I can modify
  • 03:49 or add more options to what I'm seeing in the drop down.
  • 03:52 So this is another property that we can modify from Datasheet View.
  • 03:56 Now what about if I click on something like Description?
  • 03:59 That's going to ensure that the memo settings button becomes active.
  • 04:04 And in here we have two options, and these really relate to editing the description.
  • 04:09 It might be that you want to set this field to append only.
  • 04:13 And what that means is that anybody who wants to make changes to
  • 04:18 the description can only add new sentences, new words onto the end,
  • 04:22 they can't change the existing text.
  • 04:25 So that's quite good if you need to keep a log of everything that's happened with
  • 04:29 a ticket.
  • 04:30 Or alternatively I could change this to rich text which means that people can
  • 04:35 utilize formatting in the description such as bold or italic.
  • 04:39 Now the next group we have is the Formatting group,
  • 04:42 this is going to show me the data type of that column.
  • 04:44 And if I click on something like Charge you can see here the data type is
  • 04:49 currency, the format is currency, I can change it from here to something else.
  • 04:53 And I can also define how I want that currency to look.
  • 04:57 If I want to decrease the decimal places, I can also do that from here as well.
  • 05:02 And then the final group on the end is the Validation group.
  • 05:07 Now, if I click on something like Technician, notice that required and
  • 05:12 indexed are both ticked because those are the field properties that we set for
  • 05:17 this field.
  • 05:18 Now the charge field this includes validation, and what I'm also noticing
  • 05:22 when I click on this charge field is that required and indexed are not ticked.
  • 05:27 So maybe when I was setting up this charge,
  • 05:30 I forgot to apply those field properties.
  • 05:32 Because the charge is required and it does also need to be indexed.
  • 05:37 So I can very quickly set those from there.
  • 05:40 Now, because this field contains validation, I can click the dropdown and
  • 05:45 I can take a look at the validation rule that I have set up.
  • 05:49 And when I click on this it goes into what we call the Expression Builder, and
  • 05:53 we are going to look at this a little bit more later on in the course.
  • 05:56 And I can see my expression just there, greater than or equal to 50.
  • 06:02 If I want to view what validation message I have set up, I can select it,
  • 06:07 I can review and I can change it if I need to.
  • 06:10 Now what about if I want to set up another validation on this field?
  • 06:14 Maybe I want to also say that the charge can't be greater than $2,000,
  • 06:20 maybe we have a cap.
  • 06:21 So I can go into Validation and set up another validation rule.
  • 06:26 So for this, if I take a look at my expression categories I can see all of my
  • 06:31 fields in here.
  • 06:32 So I'm going to say Charge double click less than or
  • 06:37 equal to 2000, and click on OK.
  • 06:41 I'm also going to add a new validation message,
  • 06:47 charge must not exceed $2,000, and click on OK.
  • 06:54 So now that we've done that, let's test our new validation rule.
  • 06:58 I'm going to change this first charge to let's say $3,000,
  • 07:03 and when I click somewhere else I get a little pop up charge must not exceed 2000.
  • 07:10 So I can see that that is working.
  • 07:14 Now aside from this Table Fields ribbon, we also have a Table ribbon.
  • 07:19 Now, most of the things on the Table ribbon are outside the scope of this
  • 07:23 course.
  • 07:24 But one thing that isn't is this Relationships button just here.
  • 07:28 We're going to be talking a lot about relationships once we've created a few
  • 07:32 more tables.
  • 07:33 So just bear that in mind when we get to that section.

Lesson notes are only available for subscribers.

Gift this course