Access 101 Using Microsoft Access to create database

Access 101 Using Microsoft Access to create database tables and relationships

A quick look at an Access Database • In the last lecture, we looked at a talking spreadsheet called “Guate Tours. xlsm”. • Here is the student’s Access database from which I made the spreadsheet. • Guate. Tours. accdb is available in today’s lecture materials.

Pay no attention! • When I open the file, notice the security warning below the ribbon. • You can ignore these for now, but when it the time comes to run action queries (insert, update, delete), you will need to enable this content.

The Database • When you open the database, you should see this:

Tables • As I said in the previous lecture, a relational database is a collection of tables and the relationships between them. • Here is the collection of tables. • Double-click where it says “Tours : Table” • The Tours table appears, looking not too different from what you’ve seen in Excel. • This is called the “Datasheet” view of the table.

Datasheet View of Tours Table • Note the category names across the top: Tour. ID, Tour. Name, Origin, etc. • These are the fields (aka attributes, aka columns). • Each horizontal row below the field names is a record (aka tuple, aka row).

Datasheet View • In this datasheet view, you can make changes to the data—inserts, updates, and deletes. • To insert a row, start typing data in the blank row at the bottom—the one with the asterisk (*). • To delete a row, select it by clicking in the lightblue column at the left of the row and then hit the delete key. • You can update (change particular values in the data) just as you do in Excel; click in the cell and start typing/deleting/editing.

Table Views • In the Home tab of the ribbon, the left-most button says “View. ” • The down arrow will show your choices: Datasheet View, Pivot Table View, Pivot Chart View, and Design View. • For this class, we will only use Datasheet View and Design View. • When in Datasheet View, the button defaults to Design View, and vice versa. This means that to switch from one to the other, you only need to click on the View button (ignoring the down arrow).

Design View • So—switch the Tours table to Design View. • It should now look something like this:

Design View Features • Note that each field has a name and a data type. • Note that clicking on a field’s selector (blue square on the left) will change the info displayed below. • Note that clicking one of the data types will cause a combo box to appear, offering several data type choices. • Data types can be further refined on the general tab below.

Primary Key • Note the little key icon next to Tour. ID • This indicates that Tour. ID is the primary key for this table.

Rules for Table and Field Names • No Spaces! Access will allow you to name a table “My Company’s Employees”, but this causes many problems down the road, especially when interfacing with VB. • “Employees” would be a much better name • The whole database probably relates to “My Company”, so there’s no need to include that in the table name. Even if there were—NO SPACES!!!!!

Rules for Table and Field Names • No punctuation: Most DBMS’s (except for Access) won’t allow any punctuation in the name of a field or table except the underscore (_). • I’m not a fan of underscores, either—they tend to be obscured by hyperlinks and such. • For this class, if your table or field needs a multi-word name, use Interior. Capitals.

Access Keywords • Access has many keywords with special meaning that should not be used as field or table names. • Highlighted words are the ones most likely to cause trouble when trying to name tables and fields in Access: · · · · · · · · · · ADD ALL Alphanumeric ALTER AND ANY Application AS ASC Assistant AUTOINCREMENT Avg BETWEEN BINARY BIT BOOLEAN BY BYTE CHAR, CHARACTER COLUMN Compact. Database CONSTRAINT Container Count COUNTER CREATE Create. Database Create. Field Create. Group Create. Index Create. Object Create. Property Create. Relation Create. Table. Def Create. User Create. Workspace CURRENCY Current. User · · · · · · · · · · DATABASE DATETIME DELETE DESC Description DISALLOW DISTINCTROW Document DOUBLE DROP Echo Else End Eqv Error EXISTS Exit FALSE Field, Fields Fill. Cache FLOAT, FLOAT 4, FLOAT 8 FOREIGN Form, Forms FROM Full FUNCTION GENERAL Get. Object Get. Option Goto. Page GROUP BY GUID HAVING Idle · · · · · · · · · · IEEEDOUBLE, IEEESINGLE If IGNORE Imp IN INDEX Index, Indexes INNER INSERT Insert. Text INT, INTEGER 1, INTEGER 2, INTEGER 4 INTO IS JOIN KEY Last. Modified LEFT Level Like LOGICAL, LOGICAL 1 LONG, LONGBINARY, LONGTEXT Macro Match Max, Min, Mod MEMO Module MONEY Move NAME New. Password NO Note NULL NUMBER, NUMERIC Object OLEOBJECT · · · · · · · · · · OFF ON Open. Recordset OPTION OR ORDER Orientation Outer OWNERACCESS Parameter PARAMETERS Partial PERCENT PIVOT PRIMARY PROCEDURE Property Queries Query Quit REAL Recalc Recordset REFERENCES Refresh. Link Register. Database Relation Repaint Repair. Database Reports Requery RIGHT SCREEN SECTION SELECT · · · · · · · · · SET Set. Focus Set. Option SHORT SINGLE SMALLINT SOME SQL St. Dev, St. Dev. P STRING Sum TABLE Table. Def, Table. Defs Table. ID TEXT TIME, TIMESTAMP TOP TRANSFORM TRUE Type UNION UNIQUE UPDATE USER VALUES Var, Var. P VARBINARY, VARCHAR WHERE WITH Workspace Xor Year YESNO