Author: Tomas Rutkauskas 

Answer:

How to Create a Table in Access Using SQL

Even if you don't have the ability to run Access on your PC, you can still create 
tables in an Access database using ASP and SQL. 

Here is a general look at the "Create Table" command: 

CREATE TABLE tablename (
id Counter Primary Key, 
fieldname_1 type NOT NULL, 
fieldname_2 type NOT NULL
);  

Notes: 

Be sure to NOT modify the section "id Counter Primary Key" Every table you create 
should have an auto-incrementing primary key field. Always naming this field "id" 
is a good practice as well. 
Be sure to replace "tablename" with the actual name you want to call your new 
table. 
Be sure to replace "fieldname_1" and "fieldname_2" with the actual field names you 
want in your new table. You can have as many fields as you need, not just two! 
Be sure to replace "type" with the actual type of data you want your field to hold.
Some valid options for "type" include: 
Counter - An auto-incrementing number. 
Currency - Used for holding financial numbers. 
Datetime - Used to hold formal date and time information. However, it is easier to 
make date fields using "Text(50)" instead of actual "datetime" fields. Every 
database uses a different deafult format for dates, and it is difficult to keep 
track. So the easiest method is to hold dates in a text field and translate that to 
an actual date in your programming language of choice rather than having the 
database keep track of an actual formatted date. 
Long - A number that can include decimal places. 
LongText - A text field that can hold billions of characters. 
Text(n) - where n is a number between 1-255, this is the maximum number of 
characters that can be held in this field. 

How to Alter a Table in Access Using SQL

Even if you don't have the ability to run Access on your PC, you can still alter 
tables in an Access database using ASP and SQL. The examples below use the 
"Birthdays" table we created in the Create Table tutorial. There are three ways to 
alter a table in any database: 1) add a column, 2) modify a column, 3) delete a 
column. 

Here is a general look at the "Alter Table" command: 

ALTER TABLE tablename ADD/ALTER/DROP COLUMN fieldname type NOT NULL;  

Notes: 

Be sure to replace "tablename" with the actual name of the table you want to 
modify. 
Be sure to select only one action from "ADD/ALTER/DROP" depending on how you want 
to modify your table. 
Be sure to replace "fieldname" with the actual field name you want to modify in 
your table. 
Be sure to replace "type" with the actual type of data you want your field to hold.
Some valid options for "type" include: 
Counter - An auto-incrementing number. 
Currency - Used for holding financial numbers. 
Datetime - Used to hold formal date and time information. However, it is easier to 
make date fields using "Text(50)" instead of actual "datetime" fields. Every 
database uses a different deafult format for dates, and it is difficult to keep 
track. So the easiest method is to hold dates in a text field and translate that to 
an actual date in your programming language of choice rather than having the 
database keep track of an actual formatted date. 
Long - A number that can include decimal places. 
LongText - A text field that can hold billions of characters. 
Text(n) - where n is a number between 1-255, this is the maximum number of 
characters that can be held in this field. 

Part 1 - Adding a column to a table

The following SQL statement will add a column called "zodiac_sign" to our table 
(zodiac_sign will be a text column with a maximum length of 50 characters): 

ALTER TABLE Birthdays ADD COLUMN zodiac_sign Text(50) NOT NULL;  

Part 2 - Modifying a column in a table

The following SQL statement will modify the field called "dob" in our table by 
changing it from a text field to a datetime field. (zodiac_sign will be a text 
column with a maximum length of 50 characters): 

ALTER TABLE Birthdays ALTER COLUMN dob datetime NOT NULL;  

Part 3 - Deleting a column from a table

The following SQL statement will delete the field called "zodiac_sign" from our 
table: 

ALTER TABLE Birthdays DROP COLUMN zodiac_sign;  

How to Delete a Table in Access Using SQL

Here is a general look at the "Drop Table" command: 

DROP TABLE tablename;  

Notes: 

Be sure to replace "tablename" with the actual name of the table you want to 
delete.