Microsoft Access Advanced-H

Course Outline

Implementing Advanced Form Design

You have completed Part 1 and 2 of Access training,and you have built a sound foundation and you feel ready to take on more advanced features and capabilities that Access has to offer. One of them is creating an advanced form design which will be discussed in this lesson.
You will find out how to make an easy to-use database application by applying controls.Command buttons will add extra functionality,when graphs, tab order, and subforms will make you feel like using a piece of commercial software. You will, also, organize forms with tabbed pages and navigation forms, and learn how conditional formatting can make your data stand out.

  • Add Controls to Forms
  • In this topic you will become familiar with different types of form controls. You will learn how to arrange them on a form and change the tab in order to perform actions, to display a subset of data, or to label it.
    By learning this skill you will organize information which will add efficiency and a professional touch to your forms.

  • Create Subforms
  • Subforms are separate forms that can be linked or added to your existing forms.They are important as they hold new or an additional information that supports your main form.You will work with subforms and see how they can provide visual and practical functionality.

  • Organize Information with Tab Pages
  • Tab pages could be very useful for collecting similar records on separate forms,allowing you to switch easily among open forms via tabs.
    Imagine that you are asked to create a medical history form;in the first tab you could enter Patient Information and contact details such as name, address, and phone number. Following tabs could be Allergies, Family History, and Personal History. If the information is organised like this,it is much easier to enter or to find the required information.
    By learning how to use tab pages,you will be able to flip back and forward and switch easily among open forms via tabs while entering the data.

  • Enhance Navigation of Forms
  • When you need to access related forms,you can either access it from a single location, or to open multiple forms which could lead to an extra effort and wasted time. Good news is that Access gives you the ability to have a single form that links to multiple forms.Its function is similar to the tab control,but has a hierarchy of options where you can pick a main category, and then select sub-categories.Navigation Control and Navigation Forms can help you to organize forms and reports quickly and efficently.

  • Apply Conditional Formatting
  • If you want to change the font color to red when an item’s inventory falls below a certain number,you will need to create a condition and apply special formatting to data when a particular condition is met.In this topic you will master conditional formatting in order to make some information to stand out in forms and reports.

Using Data Validation

As a database designer,you have to be capable of creating a database that any user can open,enter and query data without any futher understanding of what is going on behind the scene or how the database is designed.
By now,you might have come accross some database where information were incorrectly entered into fields and that led into inconsistency which then led to wasted time and money as the data entries need to be corrected on a regular basis. In this lesson, you will learn various data validation techniques to minimize mistakes in data entry process.

  • Field Validation
  • There are several methods that can work for you in order to constrain the information entered into the database tables. Each method uses a different approach to constrain data in its own way and you have to decide which method is the most appropriate for your field data. This topic will help you understand which methods to employ to enforce field validation.

  • Form Validation
  • If you want to have more control over entered data,you will need start using form validation. This application is very similar to field validation covered in the previous topic, but it allows you to restrict more information in order to decrease the amount of incorrect data. By learning what roles control wizards, combo boxes, list boxes, and option groups play to validate form, you will add more integrity to your data.

Using Macros to Improve User Interface Design

You know very well that data entry can be a long and repetitive process. When faced with repetitive tasks that are difficult for users to perform,you will need to look for help that you can get from advanced Access applications in order to make their life easier.
In this lesson, you will get familiar with macros.They can be very useful in data entry and with their help data can be entered more easily and with fewer mistakes.

  • Create a Macro
  • Either you want macros to trigger events,automate data entry or actions,you will need to have some knowledge how to create them. In this topic, you will be introduced to Macros and will find out what tasks different types of macros can perform. They are often seen as a simple alternative to learning a complex programming language which then perform various actions based on a set of instructions designed by you.

  • Restrict Records Using a Condition
  • Lets’ say you have a customer with an outstanding invoice and you want that information to be displayed when you enter customer name without having to search through records. This is just one example of how a macro can work based on conditions.Put simply,a condition enables a macro to execute a task if particlar set of circumstances is met. The macro might look for True/False conditions to take an action. If a condition is True, then the macro takes an action. If False, then macro takes a different action.

  • Validate Data Using a Macro
  • Macro is a versatile and powerful tool that can assist you with almost anything you need to keep your data accurate. If your data becomes corrupt,you will need to clean it,and you loose time and money. This is why this topic is of great importance to you – by creating a macro that encourages users to enter correct information will automatically cut down the risk of data becoming corrupt in the first place.

  • Automate Data Entry Using a Macro
  • So far,you have seen various jobs Macros can carry out. Automating Data Entry Using a Macro is one of their roles which will make data entry easy for users through automation.It will also increase the accuracy and the speed during the data entry process.For example,when users enter part numbers,the price can be then automatically added based on the part number. This is exactly what you need – easy,simple and efficient.

  • Convert a Macro to VBA
  • As you learn to use macros in Access, you might want to know how to convert a macro to VBA format. Visual Basic for Applications is used by Microsoft Office applications for scripting inside those applications and is the preferred procedural language for all of the Microsoft Office products.

Using Advanced Database Management

Your ability to manage a healthy database with clean data, through great design, will definitely increase after this lesson which will guide you through the processes of linking your databases to external data sources and manage those links, backing up your database as well as analyzing its performance and many more tasks. Not as exciting as your database designer job,but this will enable you to perform your database administrator job with great efficiency.

  • Link Tables to External Data Sources
  • This function is very important and very advanced. To gain insight into Access capability to connect with popular external sources like Excel spreadsheet, other Access databases, SharePoint lists, ODBC databases, HTML documents, Outlook folders and many others, is a big addition to your knowledge.But that is not all… it gets even better – when the external files are updated, Access automatically updates them without a third party involvment.You can easily see that your hard work is paying off.

  • Manage a Database
  • As you keep your house in order by regular maintenance or have your health checked, it is the same with your database. Like anything else, your database needs maintenance and housekeeping, and by understanding what you need to do to maintain it, you will also ensure data integrity and its overall health. Good investment of your time would be to focus now on the two most basic requirements; how to backup your database and how to compact and repair it.

  • Determine Object Dependency
  • This topic will explain to you objects dependency and what to do when an object depends on the object you are referring to, or when it depends upon other objects. Simply, all objects in Access have dependencies and this is because objects do rely on one another for data. In other words,tables depends on each other,forms can depend on queries and on tables and so on. There are different types of dependencies and they will be discussed in this topic.

  • Document a Database
  • You understand really well the database which you designed and maintained for some time. As it happens, you might move on and your database is given to someone else to look after it and maintain it. That person might not understand embedded macros and your validation rules.
    However,if you created a necessary and detailed documentation when working on your database,the person who inherited your masterpiece could be in much better position to succesfully take care of that database.
    It is critical to learn what tools to use so that all important details of changes are saved and up to date.

  • Analyze the Performance of a Database
  • In order to prevent your users from complaining about sluggish database, you always need to be few steps ahead of them, spotting and sortng out problems before they become real disaster. If you use performance analysis on a database, this can help you identify and fix potential problems before you get those calls about database slowness. The Performance Analyzer is an Access built-in tool,and is designed to help you do analyze performance. Make it your best friend and avoid problems in the long run!

Distributing and Securing a Database

This lesson will take you to the next level of Access knowledge, where you will make your database ready for multiuser access by dividing it into front-end and back-end components. This new skill will be enriched with various tools to implement security for your databases, including trusted locations, passwords, conversion to ACCDE format, and digital signatures.

  • Splitting a Database for Multiple User Access
  • You might ask why is necessary to split a database at all? What is the reason for it? One of the main reasons is performance. In the previous lesson you analyzed database performance and learned what important part this tool plays, but this new method of splitting database increases performance for multi-user access. It is straightforward and reduces network traffic and allows continuous front-end development without affecting data or interrupting users.

  • Implement Security
  • Even if you are not obsessed with security,you need to understand how to provide adequate security, so that your data is not hacked and compromised. You might be aware that there is no 100 % foolproof security system, but there are sufficient security measures in Access which you want to find about and will enable you to implement security, so that your data is not breached.

  • Set Passwords
  • Setting password on a database is the first step to keep your information secure. It is only one measure, among others,but the one that cannot be ommited. Password can prevent unauthorized access to your database by sending out the message that this database’s information is private.In this topic you will set a password on a database as a first line of defense.

  • Convert an Access Database to an ACCDE File
  • When your database is ready to be distributed to users outside of your design group,it would be wise to secure it from other users’s intentional or unintentional attempts to to change it. You can do so, by converting your database to an ACCDE database, which is a locked-down version of your database where nobody cannot make any design changes to forms, reports, macros, or VBA code. This is another way of keeping your data secure.

  • Package a Database with a Digital Signature
  • If you want to let the users, outside of your workgroup, know that they can trust your content and codes, and that they can use your database with confidence, you will want to package a database with a digital signature.This will inform them that the content is safe and signed by you. In this topic you will apply digital signature to your database.

Managing Switchboards

By now you have seen and worked with a number of methods to make your database more secure and easier to use.
This lesson focuses on use of switchboard and its management.
This is a new function taht will enable you to configure the startup options, so that when a user opens the database, they see the switchboard. From there,they will select the database components they need to work with.

  • Create a Database Switchboard
  • To explain switchboard in simple terms is to think about the Table of Contents in a book.It is very similar to it, but instead of page numbers, there will be links or buttons that perform some actions.
    Switchboard in Microsoft is a master form that open as you open database.It can have diferrent links to other forms, links to reports, and control buttons to close the database, print, or exit Access.</p

  • Modify a Database Switchboard
  • In the previous topic you have created a Database Switchboard and used Switchboard Manager. To make your switchboard more visually appealing, you can change colors, extend the form’s size, change fonts and much more. You can add, remove and edit items.If you want to enhance the look of your switchboard, you can add a background image as well.

  • Set Startup Options
  • Startup options is an additional modification that can help protect your database and make it easier for users to access forms, reports, and queries. In this topic, you will configure startup options for your database.

    You have successfully completed the Microsoft® Office Access® 2013: Part 3 course and created form controls, macros, subforms, and tab pages. You’ve learned how to manage and secure your database by using different methods to smooth the user experience of your database.
    Well done!