Creating a Table

In the SQL, to create a table, you can type the expression CREATE TABLE followed by the name of the table. The syntax starts with:

CREATE TABLE Name;

The CREATE and TABLE keywords must be used to let the SQL interpreter know that you want to create a table. The Name factor specifies the name of the new table. The Name can use the rules and suggestions we have been applying to the variables of the database objects. As mentioned already, a table needs at least one column. The formula to create a column is:

CREATE TABLE Employees(ColumnName DataType)

A column is specified with a name and a data type. The name can follow the rules and suggestions we reviewed for the tables.

After formulating the SQL statement, you can pass it to the RunSQL() method of the DoCmd object. Here is an example that creates a table named Employees:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees(EmployeeName Text);"
End Sub

As mentioned already, to work in SQL, you can use the DoCmd class. To get a DoCmd object, you can access it as a property of the Application that is readily available whenever you start Microsoft Access.

To create a column in the SQL, specify its name, followed by its data type, and some possible options. In the parentheses of the CREATE TABLE TableName() expression, the formula of creating a column is:

ColumnName DataType Options

Notice that there is only space that separates the sections of the formula. This formula is for creating one column. If you want the table to have more than one column, follow this formula as many times as possible but separate them with commas.

Here is an example that creates a simple table with one column:

CREATE TABLE Customers(FullName Text);

Here is an example that creates a simple table with two columns:

CREATE TABLE Customers(FirstName Text, LastName Char);

Here is an example that creates a table with many columns:

CREATE Table Employees(FirstName Text,
                       LastName Text,
                       EmailAddress Varchar,
                       HomePhone Char);

Here is an example that creates natural number fields:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE LibraryMembers(MemberName Text, " & _
                                             "MembershipStatus Byte, " & _
                                             "MembershipType Integer1);"
End Sub

Here is another example that creates natural number fields:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE ClassAttendance(Course Text, " & _
                                             "Registered Short, " & _
                                             "RoomCapacity Integer1);"
End Sub

Here is another example that creates natural number fields:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE StateCensus(State Text, " & _
                                             "Men int, " & _
                                             "Women long, " & _
                                             "Children integer4);"
End Sub

Here is an example that creates a few Boolean fields:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "FullName TEXT, " & _
                 "AvailableOnWeekend BIT, " & _
                 "OwnsACar LOGICAL, " & _
                 "CanShareOwnCar YESNO);"
End Sub

These are equivalent to Microsoft Access' Yes/No and to Microsoft Visual Basic's Boolean data type.

Here is an example that creates a NULL and a NOT NULL fields:

CREATE TABLE Contractors(FirstName TEXT NULL,
                         LastName VARCHAR NOT NULL);

Here is an example of a table with two fields with one field created as a primary key:

CREATE TABLE Genders(GenderID COUNTER(1,1) PRIMARY KEY NOT NULL,
                     Gender varchar(20));

Here is another example of creating a primary key, this time, using the CONSTRAINT keyword:

CREATE TABLE Persons(PersonID COUNTER(1,1) NOT NULL,
                     FirstName varchar(20),
                     LastName varchar(20) NOT NULL,
                     CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonID));

Here is an example of a table that will have a primary key named ReceiptNumber:

CREATE TABLE RentalOrders(ReceiptNumber COUNTER(1001, 1) NOT NULL PRIMARY KEY,
                          OrderPreparedBy TEXT(10), " & _
                 	  OrderFinalizedBy TEXT(10));

Here is an example with various columns that use different data types:

CREATE TABLE Transactions(TransactionID COUNTER(1001, 1) NOT NULL PRIMARY KEY,
                 	  TransactionDate DATE,
                 	  EmployeeID LONG,
                 	  CustomerID LONG,
                 	  TransactionTypeID LONG,
                 	  DepositAmount DOUBLE,
                 	  DepositTypeID LONG,
                 	  WithdrawalAmount DOUBLE,
                 	  WithdrawalTypeID LONG,
                 	  ServiceCharge DOUBLE,
                 	  ChargeReasonID LONG,
                 	  Notes MEMO);

Here is an example that creates a foreign key:

CREATE TABLE Persons
(
    PersonID AUTOINCREMENT(1,1) NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    GenderID Integer REFERENCES Genders(GenderID),
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);