Create/Alter/Delete tables and fields in Access using SQL (Views: 702)
|
Problem/Question/Abstract: How to Create/Alter/Delete tables and fields in Access using SQL 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. |