Microsoft Access Database Management (Access IDE)


This page discusses how to manage the Access databases and tables.
  1. Check the Microsoft Access Database Help Pages.

  2. Download and Install Access at Microsoft Imagine.
  3. MS Imagine supplies Microsoft software for education. Mine is Access 2013, but I did not download it myself. I encountered a problem when I tried to use it. The problem was solved after I downloaded and installed Microsoft Access Database Engine 2010 Redistributable.

  4. Start the Access.
  5. Select the following Windows options:
       Start ⇒ All Programs
             ⇒ Microsoft Office 2013
             ⇒ Access 2013
  1. Start a New Database and New Tables.
  2. Take the following steps:
    1. Pick the “Blank desktop database” template.
    2. Enter the database name such as C:\Access-workspace\CSci260.accdb for my case:




    3. The database to be created includes the following features:
      • The data of an author includes
        • a unique ID (assigned by the system),
        • a name, and
        • an email address.

      • The data of a book includes
        • a unique ISBN (13 digits),
        • a title, and
        • a number of authors.

      • Database design of the many-to-many relationship is described as follows. The following three tables show sample values of a relational bookstore database, of which ISBN, authorID, and (ISBN, authorID) are the primary keys of the tables books, authors, and written_by, respectively. ISBN and authorID are foreign keys of written_by.

        bookstore

        authors
        authorID authorName email
        1 Poke Mon pokemon@cs.und.edu
        2 Digi Mon digimon@aero.und.edu
        3 Spider Man spiderman@gmail.com
        4 Iron Man ironman@hotmail.com
        5 Super Man superman@gamil.com
        6 Super Mario supermario@cs.und.edu
        7 Iron Man iman@my.und.edu
        ... ... ...

        books
        ISBN title
        9780596005436 Web Database Applications and ASP.NET
        9780596009762 MySQL Database Cookbook
        9780672329166 PHP and MySQL Web Development
        9781118490358 PHP and Access Database
        9781423902010 PHP and Web Database Systems
        9781423903246 ASP.NET Web Programming
        9781491918661 Learning PHP, MySQL, and JavaScript
        9781849690683 ASP.NET and Access Database
        ... ...

        written_by
        ISBN authorID
        9780596005436 3
        9780596009762 1
        9780672329166 2
        9780672329166 3
        9780672329166 5
        9781118490358 1
        9781118490358 2
        9781423902010 6
        9781423903246 2
        9781491918661 2
        9781491918661 6
        9781849690683 1
        ... ...



      • Under the database, create tables, such as “books,” “authors,” and “written_by,” by selecting the following options:
        • Create a table: CREATE ⇒ Table.
        • Save a table and assign a name: FILE ⇒ Save.
        • Create a foreign key: Using a dropdown list beside the column name.

        The authors table is designed by using the Design View at the right bottom as follows:


        Data is entered to the authors table by using the Datasheet View at the right bottom as follows:


        The following figures show how to design the written_by table, where the ISBN is a foreign key from the books table. Using the item “Lookup Wizard” from a dropdown list beside the column type:


        Pick the books table:


        Select the ISBN field:


        Enable the function “Cascade Delete,” so when a book in the books table is deleted, the corresponding records in the written_by table will be deleted too:


        Check the E-R diagram (or relationships) by selecting the following options: Home ⇒ Database Tools ⇒ Relationships .

  1. Start Managing the Database and Tables.
  2. Other than managing databases and tables via the Access IDE (Integrated Development Environment), you can manage them via the VB programs, ASP.NET IDE, or ASP.NET VB programs.