Microsoft Office Access 2007 Expert
MAcc
icrosoft®
ess
2007
Quick Ref
Using th
erence Tutorial
e Trust Center
VBA – Using the If-Then-Else Statement
The If-Then-Else statement is a staple of nearly every programming language and is used
Trusted
Specify a trusted publisher (developer) in extensively. You have made literally millions of these types of decisions in your lifetime no
Publishers
this pane. Any content from these
doubt! It determines if a particular statement evaluates to TRUE. If so, it will execute one
publishers will be assumed as safe.
block of code. Otherwise (the statement being false) some other block of code will execute.
Trusted
Any folder paths listed here are
If it is raining outside, you will take an umbrella with you. If it is not raining, you won’t take
Locations
considered safe by Access. Use the Add
an umbrella. Translated into VBA code structure:
new location button to specify a folder or
network folder to trust.
If (it is raining) Then
I will take an umbrella
Add-Ins
The Add-Ins tab lets you specify add-in
security. Some settings provide safer
Else
usage, but may limit the functionality.
I will not take an umbrella
Macro Settings Macros, like add-ins, can be
As you will learn in the last section of this manual, SQL queries can be nested; that is you
automatically safe or unsafe based on
can have a query inside another query. The same is true of If-Then-Else statements:
these security settings.
If (it is raining) Then
Message Bar Show or hide the message bar which
displays information regarding blocked
I will take an umbrella
content if you open a database file.
Else IF(it is sunny)
I will take a hat
Privacy Options These options allow you to specify how
Else
Microsoft Office will access the Internet
I will not take a hat
for additional resources.
I will not take an umbrella
Other Macro Tasks
This statement covers all bases. If it is raining, you will take an umbrella, end of story. All
other code will be ignored. However if it is not raining, but sunny, you will take a hat (and
thusly no umbrella). If it is not raining but also not sunny, then you will not need a hat or
Embedding a Embedding a macro means a macro is
umbrella.
Macro
directly assigned to a form, report, or
control. They cannot be directly access
through the Navigation Pane.
Assign to
You can assign a macro to run when a
Keystroke
key combination is pressed. The macro
must be named as AutoKeys.
Assign to
Certain database objects like forms and
Event
reports have special events, such as the
On No Data event. If no data is found, a
macro assigned to this event can run.
Assign to
A macro can be set to run when a control
Control
is interacted with such as a mouse over,
double-click, or keystroke when a control
is in focus.
COM Add-In Security
SQL Subquery Types
If your situation requires a specialized piece of code to
A subquery is defined as a complete SELECT query inside another SELECT query. The
perform an action, Microsoft Access 2007 requires the
inner query will retrieve results that the outer (or main) query will use as arguments.
following criteria to be true:
Scalar
A scalar subquery returns a single value.
SELECT [Branch ID] FROM Branch
The add-in has been digitally signed by the
WHERE Address = ‘123 Anystreet’
developer, showing that the original content has
AND City = ‘New York’;
not been modified
will return a single branch number.
The digital signature is continuous and valid and
have not been altered since the signature was
Row
A row subquery can return values from the same row.
considered valid
SELECT [Branch ID], Phone, [BranchManager]
The digital signature is current (not past its expiry
FROM Branch WHERE Address = ‘123 Anystreet’
date)
AND City = ‘New York’;
The digital signature was issued by a reputable
Table
A table subquery can return multiple values from one or more rows:
commercial certificate authority (CA)
SELECT [Branch ID], Phone, [Branch Manager]
The developer is a trusted publisher and any of
FROM Branch
their content is trusted by your computer
WHERE City = ‘New York’;
Texas State University- San Marcos
IT Assistance Center- 512.245.itac (4822)
Before Digitally Signing a File…
Private SharePoint Space Categories
Trust in Each
Packaging a database and sending it to another user
To access your personal space, click the My Site link after logging into the
Other
is a contract of trust between you and the recipient. A
SharePoint server. The default Private space will contain the following
correct digital signature ensures that the data
sections:
contained inside is safe and has not been tampered
My Calendar
Your space can act like a simple scheduler & mail server
with.
for you if you already have access to some sort of web
mail client such as http://mail.mycompany.com.
Older Access
Though Access 2007 supports older file formats, the
Versions
Package and Sign command only applies to Access
News for You
A link to the main news page of the SharePoint site.
2007 file types. Access 2007 can use the sign and
Notifications can be posted and viewed here by all
distribute function on older Access file types, see the
members of your organization.
help file for more information.
My Links
The Links section is similar to the Favorites section of
One at a Time
Only a single database file can be packed at once.
Summary
Internet Explorer. Add workspace URLs or the URLs of
any websites you visit frequently.
Everything is
All objects in the database file are digitally signed.
Protected
Access 2007 also compresses the database file for
Links for You
Items shown here are links that members of your
faster downloading.
organization can send to you.
SharePoint
If you are using Windows SharePoint Server 3.0 or
My Alerts
SharePoint services feature an internal message and
Functionality
higher, you can extract databases from the SharePoint
Summary
alert system that will notify you of any errors or changes
Site instead of having to download them first.
to documents you specify.
SharePoint Requirements
Categories of SharePoint Lists
In order to log into and use a SharePoint site, you will need a few things:
New lists are divided into seven different categories:
Document
Create a Document Library for any file type, or a Form
Internet
Windows SharePoint services are built largely on
Libraries
Library which can organize and use XML-based forms.
Explorer v6 and Microsoft technology. Therefore, in order to properly
Internet
use the SharePoint features you must use Internet
Picture
Create a Picture Library to store any pictures/charts/
Connection
Explorer as your Internet browser. A high-speed
Libraries
photos.
Internet connection is strongly recommended.
Lists
Like the table template in Access 2007, create a list of
Username/
A user name and password will be provided to you
Announcements, Contacts, Events, Tasks, or Issues.
Password
from your system administrator. Keep your password
Custom Lists
Create a completely customizable list, or import a
in a safe place.
spreadsheet made in Microsoft Excel.
System
Your computer must have Windows 2000/XP/Vista
Discussion
Create a newsgroup-style discussion board to discuss
Requirements installed, and at least 512 megabytes of memory (1
Boards
topics.
gigabyte for Vista). The recommended amount of hard
drive space is dependant on how much data you intent
Surveys
Create a quick survey to receive input from others.
to transfer to and from the SharePoint site, but modern
computers should have no space issues.
Web Pages
Create a basic page, a web start page to display
different web parts, or a workspace.
Structured Query Language (SQL) : Basic Queries
Select All Data
SELECT * FROM [table name];
Select Specific Data
SELECT Employees.Name FROM Employees WHERE Employees.[Employee ID] = 15;
Select Conditional data
SELECT Employees.Name FROM Employees WHERE Employees.Salary >= 30000;
Order By
SELECT Employees.[Employee ID], Employees.Name, Employees.Salary FROM Employees ORDER BY
ASC(ending)/DESC(ending) Employees.Salary, Employee.Name ASC;
SELECT Employees.[Employee ID], Employees.Name, Employees.Salary FROM Employees ORDER BY
Employees.Salary DESC;
SELECT
The SELECT keyword is used to retrieve information from one or more tables.
SELECT Employee_ID, Employee_Name FROM Employees WHERE Salary <30000;
UPDATE
The UPDATE keyword is used to update data in a table.
UPDATE Employees SET Salary = Salary * 1.05 WHERE Position = ‘Manager’;
COM Add-In Types
Other Database Tasks
Active Application
These are installed and are currently running.
Splitting a
Access 2007 can split a database into two halves: one
Database
containing tables, another containing queries and forms.
Inactive Application
These are installed but are not currently running.
Database
Database replication is not a backup. You create one or
Document Related
These are template add-ins currently being used.
Replication
more identical copies of the database what can each be
Disabled Application
These are disabled because they caused
called upon to help with querying. If the master should
problems in the past.
fail, one slave database can revive the entire system.
Texas State University- San Marcos
IT Assistance Center- 512.245.itac (4822)
