How to Create an SQL Table with Microsoft Access

The difference is that the RAD tool helps you by providing a visual interface — in the form of a table-creation dialog box (or some similar data-entry skeleton) — and by preventing you from entering invalid field names, types, or sizes.

SQL doesn’t give you as much help. You must know what you’re doing at the onset; figuring things out along the way can lead to less-than-desirable database results. You must enter the entire CREATE TABLE statement before SQL even looks at it, let alone gives you any indication of whether you made errors in the statement.

CREATE TABLE POWERSQL (
 ProposalNumber  INTEGER  PRIMARY KEY,
 FirstName   CHAR (15),
 LastName   CHAR (20),
 Address    CHAR (30),
 City    CHAR (25),
 StateProvince  CHAR (2),
 PostalCode   CHAR (10),
 Country    CHAR (30),
 Phone    CHAR (14),
 HowKnown   CHAR (30),
Proposal   CHAR (50),
BusinessOrCharity CHAR (1) );

In Access 2016, creating database objects such as tables is a little more complicated. You can’t just type a CREATE statement into the SQL View Object tab. That’s because the SQL View Object tab is available only as a query tool; you have to take a few extra actions to inform Access that you’re about to enter a data-definition query rather than a query that requests information from the database.

A further complication: Because table creation is an action that could possibly compromise database security, it’s disallowed by default. You must tell Access that this is a trusted database before it will accept a data-definition query.

1. Click the Create tab on the Ribbon to display the icons for creation functionality.

2. Click Query Design in the Queries section.

   This displays the Show Table dialog box, which at this point contains several system tables along with POWER.

3. Select POWER and click the Add button.

   As you’ve seen in the previous example, a picture of the POWER table and its attributes appears in the upper half of the work area.

4. Click the Close button on the Show Table dialog box.

5. Click the Home tab and then pull down the View menu at the left end of the Ribbon and then choose SQL View from the menu.

   As in the previous example, Access has “helped” you by putting SELECT FROM POWER in the SQL editor. This time you don’t want the help.

6. Delete SELECT FROM POWER and (in its place) enter the data-definition query given earlier, as follows:

   CREATE TABLE POWERSQL (
    ProposalNumber  INTEGER  PRIMARY KEY,
    FirstName   CHAR (15),
    LastName   CHAR (20),
    Address    CHAR (30),
    City    CHAR (25),
    StateProvince  CHAR (2),
    PostalCode   CHAR (10),
    Country    CHAR (30),
    Phone    CHAR (14),
    HowKnown   CHAR (30),
    Proposal   CHAR (50),
    BusinOrCharity  CHAR (1) );

   

7. After clicking the Design tab of the Ribbon, click the red exclamation point Run icon.

   Doing so runs the query, which creates the POWERSQL table.

   

   You should see POWERSQL listed under All Access Objects in the column at the left edge of the window. In which case, you’re golden. Or you may not see the table in the All Access Objects list. In that case, read (and slog) on.

   Access 2016 goes to great lengths to protect you from malicious hackers and from your own inadvertent mistakes. Because running a data-definition query is potentially dangerous to the database, Access has a default that prevents the query from running. If this has happened to you, POWERSQL won’t appear in the column at the left of the window, because the query won’t have been executed.

   Instead, the Message Bar may appear below the Ribbon, with this terse message:

   Security Warning: Certain content in the database has been disabled.

   If you see this message, move on to the next steps.

8. Click the File tab and, from the menu of the left edge, choose Options.

   The Access Options dialog box appears.

9. Select Trust Center from the Access Options dialog box.

10. Click the Trust Center Settings button when it appears.

11. Select Message Bar from the menu on the left and then specify Show the Message Bar by clicking its option button if it isn’t already selected.

12. Click your way back to the place where you can execute the data-definition query that creates the POWERSQL table.

13. Execute the query.

Becoming proficient in SQL has long-term payoffs because it will be around for a long time. The effort you put into becoming an expert in a particular development tool is likely to yield a lower return on investment. No matter how wonderful the latest RAD tool may be, it will be superseded by newer technology within three to five years.