Home

Tutorial 9

1. Create an acton query to create a table
2. Create action queries to append, delete, and update data
3. Define many-to-many and one-to-one relationship between table
4. View and create indexes for tables
5. Learn about joining tables
6.  Join a table using a self-join
 

Important Points.
1. An acton query is a query that adds, changes, or deletes multiple table records at one time.
2. A make-table query creates a new table from one or more existing tables.
3. An append query adds records from an existing table or query to the end of another table.
4. A history table contains data that is no longer needed for current processing but that you might need to reference in the future.
5. A delete query deletes a group of records from one or more tables.
6. An update query changes selected fields and records in one or more tables.
7. Advanced Filter/Sort lets you specify multiple selection criteria and specify a sort order for the selected records in the filter window.
8. A one to many relationship exists between two tables when each record in the primary table matches zero, one, or many records in the related table, and when each record in the related table matches exactly one record in the primary table.
9. A many to many relationship exists between two tables when each record in the first table matches many records in the second table, and each record in the second table matches many records in the first table.
10. A one to one relationship exists between two tables when each record in the first tables matches at most one record in the second table, and each record in the second table matches at most one record in the first table.
10a. One to one, one to many and many to many are global joins that are active when the database is loaded.
11. An entity subtype is a table whose primary key is a foreign key to a second table and whose fields are additional fields from the second table.
18. An index is a list that relates field values to the records that contain those field values.
19. An inner join is a join in which Access selects records from two tables only when the records have the same value in the common field that links the tables.
20. A left outer join is a join in which access selects all records from the first, or left, table and only those records from the second, or right table that have matching common field values.
21. A right outer join is a join in which Access selects all records from the second, or right, table and only those records from the first, or left, table that have matching common field values.
22. A self-join is join a table with itself
22a. inner join, left outer, right outer and self join are local joins and are only loaded when the query is run.
23. An index is a list that relates field values to the records that contain those field values.
24. Indexs can be viewed in the table design view.

 

Tutorial 10

1. Design a switchboard and dialog box for a graphical user interface
2. Run and add actions to macros
3. Single-step a macro
4. Create a macro
5. Add a macro to a macro group
6. Add a command button to a form
7. Attach a macro to a command button
8. Create a dialog box form
9. Add a list box to a form
10. Use an SQL statement to fill a list box with object names
11. Create a macro group
12. Use the Switchboard Manager to create a switchboard

Important Points.
1. A user interface is what you see and use when you communicate with a computer program.
2. A graphical user interface (GUI) displays windows, menu bars, pull-down menus, dialog boxes, and graphic pictures (icons) which you use to communicate with the program.
3. A switchboard is a form that appears when you open a database.
4. Command buttons provide access to all the options available to the user.
5. A restricted menu bar contain only those menu options that a user needs for manipulating data.
6. A hidden toolbar prevents users from accessing commands that they could use to potentially alter the database design.
7. Shortcut Keys ( hot keys) are the underlined letters in the words next to each command button option, allow users to select an option by pressing and holding dow the Alt key while typing the underlined letter.
8. Text boxes and pictures can be used to provide identification and visual appeal.
9. A custom dialog box is a form that resembles a dialog box, both in appearance and function.
10. A command button is a control on a form that starts an action when you click it.
11. A macro is a command or a series of commands that you want access to perform automatically for you.|
|11a. Visual Basic for Applications (VBA) also lets you automate task.
12. An action/event is an instruction to Access to perform an operation, such as opening a form and is a state, condition or occurrence detectable by access.
13. Access lets you automate most tasks using either macros or Visual Basic for Applications.
14. Macro window is where you create and modify macros.
15. Action column is were you enter the actions you want access to perform.
16. comment column you enter optional comments to document the specific actions.
17. Arguments are additional facts access needs to execute an action.
18. Single stepping executes a macro one acton at a time.
19. Actions can be added by dragging and dropping.
20. Event property specifies how an object responds when an event occurs.
21. Event procedure occurs when the event executes code.
22. A macro group is a macro that contains other macros.
23. Macro name column lets you distinguish individual maros in a macro group.
24. View argument specifies the window view in which to open the object.
25. Filter name argument specifies the name of a query or filer that will be used.
26. Where condition argument specifies an expression or SQL statement that will ub used to select records.
27. Data Mode argument specifies the forms data-entry options.
28. Windows mode argument specifies the forms window characteristics.
27. A list box is a control that displays a list of values.
28. Structured Query Language (SQL) is a standard language used in querying, updating, and managing relational databases.
29. Select statement is used to define what data it retrieves from a database.
30. Select-from-where-orderby is the basic SQL code.
31. After Select list the fields you want to display.
32. After From list the table used
33. After Where list the selection criteria
34. After Order by list the sort field, DESC indicates descending order.
35. semi colon (;) is the ending character of a SQL program.
36. System tables are special tables maintained by Access that store information about the characteristics of the database.
37. MSysObjects table keeps track of the name, types, and other characteristics of every object in a database.
38. Left function provides the first character(s) in a text string.
39. The switchboard manage is a tool that helps you create and customize a switchboard(menu).
40. Switchboard items is a table that contains records describing the command buttons on the switchboard.
41. Default page or Main page is the switchboard page that will appear when you open the switchboard.
 

Tutorial 11

1. Learn about function procedures, Sub procedures, and modules
2. Review and modify an existing Sub procedure in an event procedure
3. Create function procedures in a standard module
4. Create event procedures
5. Compile and test function procedures, Sub procedures, and event procedures
6. Hide text and change display colors
7. Create event procedures for a dialog box.

Important Points.
1. Visual Basic for Applications is a programming language that only compiles within the application. A separate compiled executable file is not created outside of the application (access).
2. Coding is the process of writing VBA instructions.
3. VBA instructions are called statements that respond to events that occur with the objects in a database.
4. VBA is called both an event-driven language and an object-oriented language.
5. An event property specifies how an object responds when an even occurs.
6. An event procedure occurs when access executes (calls) a group of statement when an event occurs.
7. Function procedure, or function, performs operations, returns a value, can accept input values, and can be used in expressions.
8. Sub procedure, or subroutine,performs operations and can accept input values, but does not return a value and cannot be used in expressions.
9. You store a group of related procedures together in a module.
10. Each module starts with a Declarations section, which contains statements that apply to all procedures in the module.
11. A standard module is a database object that is stored in memory with other database objects when you open the database.
12. A procedure that more than one object can use is called a public procedure.
13. A class module is usually associated with a particular form or report.
14. The event procedures in a class module are local procedures, or private procedures, which meant that only the form or report for which the class module was crated can use the event procedure.
15. Focus refers to the record and control that is currently active and awaiting user action.
16. The Current event occurs when a form opens and when the focus moves to another record, the VBA procedure is associated as an event procedure with the form's OnCurrent Property.
17. The Visual Basic Editor is the program you use to create and modify VBA code.
18. The Visual Basic Window is the program window that opens when you use VBA.
19. The Code window is the window in which you create, modify and display VBA code.
20. All event procedures are Sub procedures.
21. Each sub procedure begins with a Sub statement and ends with an End Sub statement.
22. The sub statement includes the scope of the procedure, the name of the procedure, and an opening and closing parenthesis.
23. The Option Compare statement designates the technique Access uses to compare and sort text data.
24. A control structure is a set of VBA statements that work together as a unit.
24a. Case control structure is a conditional control structure which evaluates an expression.
25. An assignment statement assigns the value of an expression to a field or property.
26. A field's ForeColor property determines the field's foreground, or font color.
26a. The RGB (RED,GREEN,BLUE) function returns an RGB color value.
Hyper Links: For 3 number color codes:
http://www.lynda.com/hexv.html or
http://www.lynda.com/hexh.html

27. The Visible property determines when Access displays a control.
28. The AfterUpdate event simply accepts the new or changed entry into a field.
29. Each function begins with a function statement and ends with an End Functions statement.
30. A placeholder is called a parameter.
31. The value passed to the function and used in place of the parameter when the functions is executed is called an argument.
32. All VBA function names, Sub procedure names, argument names, and other name you create must conform to the following rules:

a. Must begin with a letter
b. Cannot exceed 255 characters
c. Can include letters, numbers, and the underscore.
d. Cannot contain keywords that VBA uses as part of its language.
e. Must be unique.

33. The UCase function accepts a single string argument as input, converts the value of the argument to uppercase letters, and then returns the converted value.
34. A logic error occurs when the procedure produces incorrect results.
35. Use the Immediate window to test VBA procedures without changing any data in the database.
36. An If statement executes one of two groups of statements based on a condition being true or false.
36a. True-statement group is the code that is executed when the condition is true.
36b. false-statement group is the code that is executed when the condition is false.
37. The IsNull function returns True when a filed value is null and false when it is not null.
38. The process of translating modules from VBA to machine language is call compilation. The module is compiled.
39. VBA Data types

40. The string type is the same as the access text field.
41. The Bollean type is equivalent of the yes/no field type in access.
42. To declare a variable the DIM variable AS type statement is used.
42a. A variable is a location in computer memory that can contain a value that may or may not change during program execution.
43. The CStr function is used to convert a value to a string.
44. A group of statements executed repeatedly is called a loop.
45. Do While, Do Until, and For, Next are looping structures.
46. The Len function returns the number of characters in a string.
46a. Left function returns a string containing a specified number of characters from the left side of a specified string.
46b. The val function returns the numbers contain in a specified string as a numeric value.
46c. DoCmd statement executes an action in a procedure.
46d. CancelEvent acton cancels the event that that caused the procedure or marco contain the action to execute.
46e. Me keyword refers to the current object
47. The Load event occurs when Access opens a form.
47a. BeforeUpdate event occurs before changed data in a control or recorded is updated.
48. SetFocus is a method that moves the focus to the specified object or control.
49. A method is an action that operates on specific objects or controls.
50. StrConv function converts the letters in a string to all uppercase or to lowercase, or fist letter of every word to uppercase and other to lower case.
51. VBA constant is a predefined memory location that is initialized to a value that does not change during program execution.
52. vbuppercase constant specifies the conversion of the string to all uppercase letters.
53. Vblowercase constant specifies the conversion the the string to all lower case.
54. Load event occurs when access opens a form.
 

Tutorial 12

1. Analyze a database's performance
2. View and print database documentation
3. Link a database to a table in another database
4. Use the Linked Table Manager
5. Use replication to create a Design Master and replica of a database
6. Synchronize the Design Master and replica databases
7. Split a database
8. Set database startup options
9. Encrypt and decrypt a database
10. Set and unset a database password
11. Use the User-Level Security Wizard to assign users, groups, and permissions to a database
12. Create an MDE file

Important Point:
1. The Performance Analyzer is an Access tool that you can use to optimize the performance of an Access database.
2. The Documenter is used to print out the characteristics of the database.
3. A linked table is a table that is stored in a file outside the open database from which Access can update and retrieve records in the open database.
4. The database can be linked to data stored in Excel, HTML documents, text files, other Access databases, and databases created by other DBMS.
5. The Linked Table Manager is an Access tool you use to change the filename or disk location for linked tables in an Access database.
6. If you change a linked files location you must refresh the link.
7. When you create a replica of a database, the original copy of the database becomes the Design Master.
8. The design Master and all of its replicas are called the replica set.
9. The process of updating the Design Master and the replicas is called synchronization.
10. Replica priority refers to when multiple replicas update the same record, only the change for the replica with the highest priority is made.
11. Replica visibility choices are global, local, and anonymous.
12. The replica record deletion when checked prevents a user from deleting a record in the replica.
13. The Database Splitter is an Access tool that splits an Access database into two files: one file contain the tables, and the other contains the queries, forms, and other database objects.
14. The database that contain the tables is called the back-end database.
15. The database that contains the other objects if called the front-end database.
16. Startup options take place when a database opens.
17. Security refers to the protection of a database against unauthorized access, either intentional or accidental.
18. Encryption converts the data in a database to a format that's indecipherable to a word processor or other program and stores it in a encrypted format.
19. Decryption a database reverse the encryption.
20. A password is a string of characters assigned to a database that users must enter before they can open the database.
21. When you open a database with exclusive access, you prevent other users from opening and using the database at the same time. When you set a password open the database up with exclusive access.
22. User-level security lets you grant users or groups of users specific permissions to tables, queries, forms, reports, and macros.
23. A user's permissions specify what kind of access the user has to objects in a database.
24. User permissions are Modify Design, Read Data, and Delete Data.
25. The database administrator controls the permissions assigned to database users.
25a. User-lavel security wizard is used to create the workgroup information file and establish groups, users, and their permissions.
26. A group is a collection of users identified by a group name and ID.
27. Group name and IDs, user names and passwords, and group membership information are stored in an Access Workgroup information file that is read at startup.
28. The users group is one of two groups created by default when you create a new workgroup information file.
29. Users assigned to the Admin group have full permissions and are the only users that can create users and groups.
30. A MDE file complies all VBA modules, removes all editable VBA source code, and compacts the resulting database.
31. You can't view, modify, or create forms, reports, or modules in Design view, and you can"t import or export forms, reports, or modules in a MDE file.