How to Create a SQL Database Table in Access 2013

When you fire up your Access 2013 development environment in SQL, you’re greeted by the welcome screen. From there, you can build a database table in several different ways. You might want to start with Datasheet view because that approach shows you how to create a database from the ground up.

image0.jpg

How to build a database table in Datasheet view

By default, Access 2013 opens in Datasheet view. To build an Access database in Datasheet view, double-click the Blank Desktop Database template.

Your Access datasheet stands ready for you to start entering data into Table 1, the first table in your database. You can change the table name to something more meaningful later. Access gives your new database the default name Database1. It’s better to give the database a meaningful name at the outset just to avoid confusion.

image1.jpg

That’s the start-from-scratch method, but you have several different ways to create an Access database table. This next one uses Design view.

How to build a database table in Design view

In Datasheet view, building a database table is pretty easy: You just start entering data. That approach, however, is prone to errors, because details are easy to overlook. A better way to create a table is in Design view by following these steps:

  1. With Access open in Datasheet view (the default), click the Hometab on the Ribbon and then click View below the icon in the upper-left corner of the window. Choose Design View from the drop-down menu.

    When you choose Design View, a dialog box pops up and asks you to enter a table name.

  2. Enter POWER (for your mythical Powerball winnings) and click OK.

    The Design view appears.

    image2.jpg

    Notice that the window is divided into functional areas. Two of them are especially useful in building database tables:

    • Design view options: A menu across the top of the window offers Home, Create, External Data, Database Tools, and Design options. When the Ribbon is displayed, the tools available in Design view are represented by the icons just below the menu.

    • Field Properties pane: In this area for defining database fields, the cursor is blinking in the Field Name column of the first row. Access is suggesting that you specify a primary key here, name it ID, and give it the AutoNumber data type.

      AutoNumber, an Access data type, isn’t a standard SQL type; it increments an integer in the field by one automatically every time you add a new record to a table. This data type guarantees that the field you use as a primary key won’t be duplicated and will thus stay unique.

  3. In the Field Properties area, change the primary key’s Field Name from ID to ProposalNumber.

    The suggested Field Name for the primary key, ID, just isn’t very informative. If you get into the habit of changing it to something more meaningful, it’s easier to keep track of what the fields in your database are for. Here the field name is sufficiently descriptive.

    image3.jpg
  4. In the Field Properties pane, check the assumptions that Access has made automatically about the ProposalNumber field.

    As is often the case, the assumptions Access makes are fine for what you want to do. If any of the assumptions are incorrect, you can override them by entering new values.

  5. Specify the rest of the fields you want this table to have.

    image4.jpg

    The data type for FirstName is Short Text, rather than AutoNumber, so the field properties that apply to it are different. Here Access has given FirstName the default Field Size for short text data, which is 255 characters. However, other development environments might not have this capability.

    Here the default Access assumption is that FirstName is not a required field. You could enter a record in the POWER table and leave the FirstName field blank, which takes into account folks who go by only one name, such as Cher or Bono.

  6. Change the Field Size for FirstName to 15.

    For a rundown on why this is a good idea, see the accompanying sidebar, “Thinking ahead as you design your table.”

  7. To ensure that you can retrieve a record quickly from the POWER table by LastName (which is likely), change the Indexed property for LastName to Yes (Duplicates OK).

    image5.jpg
  8. Enter the rest of the fields, changing the default Field Size to something appropriate in all cases.

    image6.jpg

    There’s no point in indexing a field that has only two possible entries; indexing doesn’t narrow down the selection enough to be worth it.

    Access uses the term field rather than attribute or column. The program’s original file-processing systems weren’t relational and used the file, field, and record terminology that are common for flat-file systems.

  9. Save your table by clicking on the diskette icon in the upper left corner of the window.

    Keeping one eye on the future is wise as you develop your database. It’s a good idea (for example) to save frequently as you develop; just click that diskette icon now and then. Doing so could save you a lot of tedious rework in the event of a power outage or other untoward event.

    Also, though it won’t destroy the planet if you give the same name to a database and to one of the tables that the database contains, it might be mildly confusing for later administrators and users. As a rule, it’s handier (and kinder) to just come up with two different names.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com