What is the difference between table design view and table Datasheet view in Access?

Design View can make some things easier to do than Datasheet View when creating a table.

Here's how to create a table in Design View.

1. 

   Click the Table Design Button

   Click Table Design on the Ribbon (from the Create tab).

2. 

   The Blank Table

   A blank table will appear in Design View.

   You can see that field names are to be entered on top of each other down the left column, and their corresponding data types will be listed in the next column.

   One of the big differences between Design View and Datasheet View is how they display the fields. Design View displays them as rows (down the left), whereas, Datasheet View displays them as columns (along the top).

   Regardless, this doesn't change the design of the table. It's still the same table whether it was designed in Datasheet View or Design View.

3. 

   Enter the Fields

   Enter a name for each field down the left column.

   Select each field's data type from the next column.

   You can also create a lookup table if required, by selecting Lookup Wizard... from the data type list.

4. 

   Enter the Field Properties

   You can also change each field's properties in the bottom pane.

   To do this, you need to click on the field first. Clicking a field updates the bottom pane with the properties for that field.

   The properties displayed in the bottom pane are different, depending on the data type of the selected field.

   Field properties can be used to do things like, specify a default value for the field, specify whether it's a required field, create an input mask, and more.

5. 

   Set a Primary Key

   To set a field as a primary key, right click on the field and select Primary Key.

Once the table has been configured, be sure to save it. In any case, you will be prompted to save it when you try to switch to Datasheet View or close the table.

You can switch between Design View and Datasheet View at any time by using the little Datasheet View/Design View icons at the botton right of the screen, or the View icon in the left part of the Ribbon (from the Design tab).

Design View vs Datasheet View

You can create a table in either Datasheet View or Design View. Generally, you can do most things in both views. However, there are some things you can do in Design View that you can't in Datasheet View.

But there are some things that are just easier to do in Design View. For example, you can see all fields' data types in one screen, without having to click on each field.

The Design View interface can be easier to adjust field properties too.

Design View can make it easier to set up a table. It provides a different view of the table to Datasheet View, and is typically used to configure the table.

You can use Design View to create a tables, set up data types for each field, specify default values, specify how data should be entered and displayed, and more.

Generally speaking, most tasks can be achieved using either Design View or Datasheet View, however there are some advanced settings that can only be changed in Design View. Also, you might find that you prefer to do certain tasks in Design View and other tasks in Datasheet View.

Datasheet View vs Design View

Here's a quick comparison of Datasheet View and Design View:

• 

  Datasheet View

  Datasheet View is what we used when we created our table previously.

  Datasheet View displays the table as a grid. The fields are displayed as columns, and the records are displayed as rows. The field names are listed as the column headers.

  Datasheet View displays the data. If this table had data, it would be displayed in the cells.

  Each row represents a record. If the table had say, five records, there would be five rows of data.

  In Datasheet View, if you want to find out a field's data type, you need to select that field, then select the Fields tab on the Ribbon. The field's data type and other properties will be listed on the right side of the Ribbon.

• 

  Design View

  Design View doesn't display any data. Therefore, there's more space available to display other settings.

  In Design View, the fields are listed vertically. They are listed above and below each other as opposed to side-by-side.

  In Design View, you can see the data type listed next to each field.

  The way Design View works is, when you click on a field (in the top frame), the bottom frame displays the properties for that field. You can then change these properties as required.

We will now use Design View to do two things:

1. Create a new table
2. Set field properties for our existing table

Create a new Table in Design View

So now that we've established that Design View and Datasheet View are just two ways of looking at the same table, let's go ahead and create a table in Design View.

1. 

   Create the Table

   Click Table Design from the Ribbon (ensuring that the Ribbon is on the Create tab).

   

   A blank table called Table1 will be displayed in Design View.

2. 

   Add Fields to the Table

   Now we will enter each field and select their data type.

   In the first cell under Field Name add a field called GenreId. Select Autonumber as its data type.

   Under that field, create a new one called Genre and select Short Text for its data type.

3. 

   Change the Genre Field Properties

   Ensuring the Genre field is selected (by clicking on it), look at the bottom Field Properties frame.

   Double-click somewhere in the Required row, so that the value now reads Yes.

   Now, double-click in the Allow Zero Length field so that it changes to No.

   We have just made the Genre field a required field — all records must have a value in this field, and it can't just contain a blank string.

   You can also click in the row, then use the small button with the down-arrow to change the value.

   Or, you can simply highlight the current value (by clicking on it) and type Yes or No over the top of it.

4. 

   Set a Primary Key

   Right-click on the GenreId field and select Primary Key from the drop down list.

   This makes the field a primary key field. Once you've done this, you'll see a little key icon to the left of GenreId.

   A primary key is the unique identifier for the table. Each value in this field will be unique — no two records in the table can have the same value in the primary key field.

   This is why we set it to Autonumber. Access will automatically generate a number each time a new record is created.

   Note that a table can only have one primary key.

5. 

   Save the Table

   Right-click on the Table1 tab and select Save from the drop down list.

   

   Call the table Genres and click OK.

Set the Field Properties for an Existing Table

You can also use Design View to edit any existing table. Whether the table was created in Design View or Datasheet View doesn't matter. Any table can be edited in Design View.

Now we will change some field properties to the table we created previously.

1. 

   Open the Albums table in Design View

   Right-click the Albums table in the left menu and select Design View from the contextual menu.

   This will open the table in Design View.

   You can also open Design View by:

   • Right-clicking on a table's tab (if the table's open)
   • Clicking the View button at the left of the Ribbon
   • Clicking the Design View icon in the bottom right corner of the window (the Datasheet View icon is on its left)

   You can switch back and forth between views at any time by using this method.

2. 

   Modify the AlbumName Field

   Set the AlbumName field to be a required field.

   Also set Allow Zero Length to No.

3. 

   Modify the ArtistId Field

   Set the ArtistId field to be a required field.