-
The new table named "Table1" is displayed in the so-called "Datasheet view" as soon as the new database is created.
-
Since I am planning to manipulate everything by SQL, I am closing "Table1" by
right-clicking on the tip of its tab and then choosing "Close." (Please note that right-clicking
on the tab may offer some really useful choices in many Microsoft products.)
-
Now I have a blank screen with an empty list of "All Access Objects" pane on the right side of the screen.
-
I am clicking "File" tab and then "Save Database As." (How you get to this option may differ
slightly depending which version of MS Access you are using, but it is very consistent
with other Microsoft Office products, such as Word or Excel.)
-
The Save As dialog box opens. Please do not just accept the default location
and file name! This is your homework in progress, and you should have a particular
Windows folder already created for CIT-122 class work. I am saving my file
in "My Documents\CIT122" folder, and changing the file name to "sql_101.accdb". You
shoud always be aware of the exact location of your files!
-
In Access 2003 and earlier the file name will be "sql_101.mdb". That's okay - it's an older format of the database which is still usable in later versions of Microsoft Access.
-
For a completely unknown reason Access displays a Security Warning that
says "some active content has been disabled..." Apparently, Microsoft is afraid of
their own products, but pardon my ignorance, I am not reading any of this foolishness
and just clicking "Enable Content." By the way, this Security Warning is going to show
up every time I re-open Access or open a new database, so I will "Enable Content" every
time this happens -- otherwise certain SQL commands may be disabled by Access!
-
Click Create tab on top of the ribbon.
-
Click Query Design icon.
-
A tab named "Query1" opens. On top of it, a "Show Table" dialog is displayed.
This is an annoyance, because we have no tables and we don't want to use any helpers in
specifying what to do.
-
Dismiss the "Show Table" dialog box simply by closing it. You will do this every time you need to create a new query.
-
By the way, the whole notion of a "query" is misleading at this point, because "query" means
"an inquiry", but we know our database is empty at this point, so it's kind of meaningless.
However, this is not Access's fault; in SQL, all commands are called "queries," this is just a
technological term.
-
An empty "Query1" tab opens in the so called "Design View". We need to switch to the "SQL View." To do this, locate the "View" in the "Results" group of the "Design" tab.
Here is how it looks like on my screen:
After switching to SQL View, the "Query1" changes to a simple text entry window with a SELECT keyword
and a semicolon ";" like this:
A semicolon can be entered at the end of any MS Access SQL statement. In many cases it
is optional, so when you enter your own SQL commands you don't have to type the semicolon. Some textbooks use the semicolons, but
I will avoid adding them at the end of my SQL examples.
-
With "Query1" SQL View open, type in the following command (you can copy and paste it from this handout, but I would also recommend keeping your own text file with queries that you use in this course.)
CREATE TABLE Customer
(
CustomerID INTEGER,
LastName TEXT
)
It looks like this on my screen:
-
You can execute this query simply by clicking "Run!" in Access 2010:
However, in Access 2007 this will give you a strange error message about non-existing database object.
To stay consistent and trouble-free across multiple versions of MS Access, I want you to save
your query by right-clicking on the "Query1" tab and selecting "Save."
-
Instead of just saving what you have so far, the database asks you to
specify the Query Name:
I am saving my query with the descriptive name CREATE_TABLE_Customer.
This should always be the case when you work with Access. Never save your database objects
as "Query1" or "Table1" - this is a very poor choice. You could also notice that instead of
spaces between words I am using the underscores. In some databases, spaces are not allowed
anywhere in the names of tables or queries. So my habit is to use the underscore characters
instead. Although I am not encouraging it, it's probably okay for you to use spaces if you prefer.
(More on this later.)
-
Once the query is saved, you can execute it like this:
The result of CREATE TABLE query is a new, empty database table named "Customer". The table has two columns:
the "CustomerID", which is an INTEGER, that is, a numerical field, and "LastName", which has a TEXT type - accepting strings of characters.
(More on data types later.)
-
If you double-click the "Customer" on the list of tables, it will open in a separate tab in "Datasheet View". There you can actually input some data into the table.
-
Close the "Customer" table tab. (In fact, once I am done using a database object, such as a
query or a table, I have a habit of closing it for two reasons: to reduce clutter, and often
to avoid warnings that Access needs to close it before running a particular command. As you
try different things, you will see this kind of message quite often in Access.)
-
Click Create tab on top of the ribbon.
-
Click Query Design icon.
-
Again, a tab named "Query1" opens. On top of it, a "Show Table" dialog is displayed, which you need to close.
-
Switch to "SQL View" and enter the following SQL statement into the query text window:
DROP TABLE Customer
-
Save the query as DROP_TABLE_Customer
-
Click the "Run!" icon to execute the DROP_TABLE_Customer query. In Access 2007 you get a warning that you are about to delete a database object. In Access 2010, the table is silently deleted.
-
Why would you want to delete a table? Perhaps you created it by mistake, or you misspelled the table name and
need to correct this mistake. Sometimes tables are used as temporary tables. In other cases,
you may wish to restructure an existing database and delete a table that you know is no longer used.
-
Note that in Micrsoft Access, the table is deleted even if it contains the actual data rows. Other SQL platforms, such as SQL server or Oracle, may refuse to delete a non-empty table. In that case you have to delete
the data from the table first:
DELETE FROM Customer
The "DELETE FROM Customer" statement removes all data rows from the table, so the table becomes empty.
-
Once the table is deleted by "DROP TABLE Customer", you can re-create it by opening the CREATE_TABLE_Customer and then again choosing "Run!"
-
Our next step is to populate some data in the "Customer" table. This can be done by opening
the table and entering the data directly. However, our goal is to keep using the SQL for every
manipulation of the content in our database. So instead of using the "Datasheet View", we will create a new query.
-
Do all the steps to create the new query as outlined above. Switch to SQL view and paste in the following command:
INSERT INTO Customer ( CustomerID, LastName )
VALUES ( 1001, 'JONES' )
The INSERT statement inserts a single row into the database table. This is very tedious and may seem much less
convenient rather than entering the data directly (like you do in Excel.) However, the INSERT statement
is exactly the way the data is manipulated when you go to an ATM machine and
withdraw $20.00 dollars from your bank account, or when you register your Facebook account,
or in general, when you create new record in any software that runs on top of some relational database.
-
Save the query as INSERT_INTO_Customer, and "Run!" Typically, Access gives you a warning telling
that you are about to insert a row into a table. Why this is happening is beyond my ability to
know. You will never see this warning if you wrote a program in a different programming language
and invoked your SQL query from that program. I prefer when programs are less verbose
and generally keep their message boxes away from what I am trying to do. 'Nough said.
-
You can open the "Customer" table and see the results. There should be one row of data present there with "1001, JONES" entered there.
-
Next, change your SQL command to
INSERT INTO Customer ( CustomerID, LastName )
VALUES ( 8027, 'SMITH' )
then save it, and run it again. This will insert another row into the table -- you get the idea.
-
Modify your INSERT query to insert a couple more rows with different data.