1Tutorial 1Creating a Database2Introduction to Database Concepts Data is a valuable asset Need to store, maintain, retrieve, present vital business data Entity (nib)23Organizing Data Example: issue25.accdb reference sheet Data hierarchy Byte Field Record Table Database4Databases and Relationships How do we connect related data that has been stored in different tables? Primary key Foreign key35Issue25 Database We’ll use periodically throughout the semester Available in CIS117 Data Files\Issue25\Issue25.accdb Overview its tables Writer Article Type6Opening a Database Access closes any previously opened database can have only one database open in the Access User Interface can open multiple databases simultaneously via macro or VBA  Access creates an .LACCDB file with the same name in the same folder as the ACCDB file Access is a multiuser database when multiple users use the same db, conflicts can occur as users try to use/modify/delete the same object the LACCDB file keeps track of the users connected to the db, which objects they’re using, and locking information Practice Time open issue25.accdb observe the issue25.laccdb file47Relational Database Management System (RDBMS) Software used to store, maintain, retrieve and present data in structured tables We will use Access to: create new tables define relationships between tables add and delete records change field values so they’re up-to-date retrieve data to answer questions create customized forms create customized reports utilize a switchboard use database startup options86 Types of Access Objects Table (Tutorials 2, 5, 9) an object that stores data in rows (records) and columns (fields) Query (Tutorials 3, 5, 9) an object that selects requested fields and records from a table can pull data from multiple tables if they have a common field Form (Tutorials 4, 6) an object used to enter, change & view data records on-screen in custom layout Report (Tutorials 4, 7) an object you can use to print records in a custom layout596 Types of Access Objects Macro (Tutorial 10) an object containing a list of actions you want Accessto carry out Module (Tutorial 11) an object that stores Visual Basicprogramming instructions written to automate, customize, and enhance a database all these objects are stored in a single .ACCDB file10Creating a Database Created Chatham.accdb from Blank Database template Access Window Navigation Pane Shutter Bar Table Datasheet View Table Design View Create a Table in Datasheet View Easy & crude Access guesses the data type and format based on values you enter Can change using Data type & Formatting options Serious database developers prefer to work in Design View instead611Two Views of a Table Datasheet View see the table’s data as a grid rows = records columns = fields can view, insert, update, delete table records Design View (Tutorial 2) see/change the table’s structure12Entering Records Can add records using a Form or a Table’s Datasheet forms are preferred… they’re flexible and powerful New Record button [Tab] to move between fields Pencil symbol Record Navigation buttons Current Record Box Practice Time add yourself as a new writer713Saving Save Button saves changes to an object’s layout or structure also saves changes to the record’s data (starting in Access 2007) Pencil symbol indicates a dirty record… one whose data has changed Access automatically saves changes to the data in the current record when you: move to another record close the table/form use Home | Records | Save Record press [Shift]+[Enter] Practice Time add a friend as another new writer14Copying Records from Another Access Database Source table’s structure must match destination table’s Copied 76 records from Appointment table in Cindi.accdb, pasted into Chatham.accdb Visit table Clipboard Copy Paste Steps to Import1. open the source table2. select the records to be copied3. click Copy4. open the destination table5. click New Record’s record selector6. click Paste815Creating a Simple Query Query an object that selects specified fields & records from one or more tables Create tab | Query Wizard Simple Query Wizard guides you: 1. select source of data (table or query) 2. select fields to display 3. select Detail or Summary 4. specify query object’s name Practice Time Create a query named qryBusinessArticles that shows the title, type, issue, length, author and phone, but only for business articles16Creating a Simple Form Form a customized on-screen view of table data, commonly displaying one record at a time the most powerful and flexible way to present data to users table datasheets are plain and limited AutoForm places all fields from the table on a new form which shows one record at a time steps:1. Select source of data (table or query)2. Create Tab | Form Practice Time Create a form frmArticle that displays fields from Article table917Creating a Simple Report Report an object you use to print records in a custom layout AutoReport places all fields on a new report and calculates total for Number and Currency fields, page numbering steps:1. Select source of data (table or query)2. Create Tab | Report18Viewing Objects in the Navigation Pane Navigation Pane displays objects in the current accdb file several display options available1019Compacting a Database When you delete an object, it is not actually removed from the database Compacting creates a new .accdb and copies the non-deleted objects into the new file Requires free space on disk File | Compact and Repair Microsoft highly encourages regular compaction to promote database health Compacting also physically rearranges table records in primary key sequence (nib) rebuilds indexes (nib) recompiles each query’s execution plan (nib)20Documenting an Access Object (lib, Tut 6) Shows the structure and properties for the selected object(s) Database Tools | Database Documenter Practice Time use the documenter to show the details for the Writer table1121Backing Up and Restoring a Database What could go wrong with a file? Extremely important to have