MS Access: Security

Database-level password

• Set a database-level password on a database in Access 2003/XP/2000/97
• Remove a database-level password from a database in Access 2003/XP/2000/97

Workgroup Administrator

• Launch the Workgroup Administrator in Access 2003/XP | Access 2000/97

Miscellaneous

• Associate an mdw security file with a database in Access 2003/XP/2000/97
• Disable the Shift key when logging into a secure database in Access 2003/XP/2000/97
• Re-enable the Shift key when logging into a secure database in Access 2003/XP/2000/97

MS Access: Tables

Basics

• Create a table in Access 2007
• Open a table in Design view in Access 2007
• Open a table in Datasheet mode in Access 2007

Navigation Pane (Access 2010 and 2007)

• Display tables in the Navigation Pane in Access 2007
• Display all objects (tables, queries, forms, reports, modules) in the Navigation Pane in Access 2007
• Display tables and related objects in the Navigation Pane in Access 2007

Hide / Unhide tables

• Hide tables as hidden objects in Access 2007 | Access 2003/XP/2000/97
• Hide tables as system tables in Access 2007 | Access 2003/XP/2000/97
• View system tables in Access 2007 | Access 2003/XP/2000/97
• View hidden tables in Access 2007| Access 2003/XP/2000/97

Linked Tables

• Link to a table in another database in Access 2007 | Access 2003/XP/2000/97
• Refresh table links in Access 2007 | Access 2003/XP/2000/97
• Access does not recognize table changes to linked tables in Access 2003/XP/2000/97
• Linked table record shows as deleted (ie: #DELETED) in Access 2003/XP/2000/97
• Link to an Oracle table using VBA code in Access 2003/XP/2000/97

Imported Tables

• Import a table from another database in Access 2007 | Access 2003/XP/2000/97
• Import table record shows as deleted (ie: #DELETED) in Access 2003/XP/2000/97

Sequential numbers

• Create an auto-increment field in a table in Access 2007 | Access 2003/XP/2000/97
• Create a sequential number that you can control instead of using an AutoNumber field in Access 2003/XP/2000/97
• Create a sequential number that you can control using a format such as AGR-0001 in Access 2003/XP/2000/97
• Create a sequential number that you can control using a format such as A05-0001 in Access 2003/XP/2000/97
• Create a sequential number that you can control using a format such as OD00000001 and generate multiple records at once in Access 2003/XP/2000/97
• Create a sequential number you control that is assigned every time a form is opened in Access 2003/XP/2000/97

Miscellaneous

• Create a random number field in a table in Access 2007 | Access 2003/XP/2000/97
• When a record is deleted in a parent table, the child records are deleted also in Access 2003/XP/2000/97
• Reset the counter on a AutoNumber field in Access 2003/XP/2000/97
• Find next incremental value for an AutoNumber field in Access 2003/XP/2000/97
• Indexes on a table in Access 2007 | Access 2003/XP/2000/97
• Define a custom format for a Date/Time field in a table in Access 2007 | Access 2003/XP/2000/97

MS Access: Reports

Tutorials

• TUTORIAL: Report Basics (Understanding and creating reports) in Access 2007 | Access 2003/XP/2000/97
• TUTORIAL: VBA Basics (Understanding the VBA environment) in Access 2007 | Access 2003/XP/2000/97
• TUTORIAL: Debugging VBA Code (Understanding VBA's Debugging Environment) in Access 2007 | Access 2003/XP/2000/97

Navigation Pane (Access 2010 and 2007)

• Display reports in the Navigation Pane in Access 2007
• Display all objects (tables, queries, forms, reports, modules) in the Navigation Pane in Access 2007
• Display tables and related objects in the Navigation Pane in Access 2007

Formatting

• Border on a report page in Access 2003/XP/2000/97
• Dynamically size report object in Access 2003/XP/2000/97
• Hide report object if no value in Access 2003/XP/2000/97
• Suppress blank lines on a report in Access 2003/XP/2000/97
• Create a report title that repeats on every page in Access 2003/XP/2000/97
• Display a subtotal for each category within a report in Access 2003/XP/2000/97
• Left align a field in a report in Access 2003/XP/2000/97

Layout

• Create a 2 column report in Access 2003/XP/2000/97 | Access 2007
• Set up a report to print on legal size paper using VBA code in Access 2003/XP/2000/97

Conditional Formatting

• Change the formatting of a field in a report based on its value in Access 2003/XP/2000/97
• Change the formatting of a field in a report when the field contains a NULL value in Access 2003/XP/2000/97
• Conditionally format a field in Access 2003/XP/2000/97

Graphs

• Create a graph (ie: chart report) in Access 2003/XP/2000/97
• Fill in missing data on a graph in Access 2003/XP/2000/97

Checkbox

• Display a checkbox on a report in Access 2003/XP/2000/97

Page Breaks

• Force a page break based on the first letter of the LastName field in a report in Access 2003/XP/2000/97

Errors

• Trim function will not work in a report in Access 2003/XP/2000/97

Filter Results

• Filter report results using a date parameter in Access 2003/XP/2000/97
• Filter report results using the OpenReport VBA command in Access 2003/XP/2000/97
• Use the OpenReport VBA command to return only those records containing a keyword in Access 2003/XP/2000/97

Miscellaneous

• Determine if a report has no data in Access 2003/XP/2000/97
• Maximize report window but not affect other windows in Access 2003/XP/2000/97
• Email a report in Access 2003/XP/2000/97
• Report Example (Employee hours worked per day) in Access 2003/XP/2000/97
• Display parameter values in a report based on a parameter query in Access 2003/XP/2000/97
• Create lines at the end of a report so that handwritten entries can be added in Access 2003/XP/2000/97
• Using WeekdayName function in a report in Access 2003/XP/2000/97
• Counting the number of records with a blank field in Access 2003/XP/2000/97
• Set up a text box to display the difference between two dates in Access 2007 | Access 2003/XP/2000/97

MS Access: Queries

Tutorials

• TUTORIAL: Pass-through Queries in Access 2003/XP/2000/97

Basics

• Create a query in Access 2007 | Access 2003/XP/2000/97
• Open a query in Design view in Access 2007 | Access 2003/XP/2000/97
• View SQL for a query in Access 2007 | Access 2003/XP/2000/97
• Add tables to a query in Access 2007 | Access 2003/XP/2000/97
• Run a query in Access 2007

Navigation Pane (Access 2010 and 2007)

• Display queries in the Navigation Pane in Access 2007
• Display all objects (tables, queries, forms, reports, modules) in the Navigation Pane in Access 2007
• Display tables and related objects in the Navigation Pane in Access 2007

LIKE Condition

• LIKE Condition (using wildcards) in Access 2007 | Access 2003/XP/2000/97
• Create a query whose LIKE clause gets its condition from a text box in Access 2003/XP/2000/97
• Create a query that excludes records that contain a wildcard character in Access 2007 | Access 2003/XP/2000/97
• Query to retrieve records that contains keywords stored in a table in Access 2007 | 2003/XP/2000/97

Group By Features

• Avg Function
• Sum Function
• Min Function
• Max Function
• Count Function

Queries using Max function

• Query to retrieve max date in Access 2007 | Access 2003/XP/2000/97
• Query to retrieve the most recent record from a table in Access 2003/XP/2000/97
• Query to retrieve max value but display all columns from original table in Access 2007 | Access 2003/XP/2000/97

Queries using Count function

• Query to retrieve the number of customers with a certain shoe size in Access 2003/XP/2000/97

Creating queries

• Only show unique values in a query in Access 2007 | Access 2003/XP/2000/97
• Prevent a query from timing out in Access 2003 | Access 2000/97
• Concatenate two fields in a query in Access 2007 | Access 2003/XP/2000/97
• Concatenate first name and last name fields with a space in the middle in Access 2007 | Access 2003/XP/2000/97
• Joining 3 tables (using left joins) in Access 2003/XP/2000/97
• Create a query using a Left Join in Access 2003/XP/2000/97
• Create a query that returns a new table based on 3 nested subqueries in Access 2003/XP/2000/97
• Include NULL values in query results in Access 2003/XP/2000/97

Update queries

• Update Query that updates one field based on the value of another field in Access 2003/XP/2000/97
• Update Query that updates values in one table with values from another table in Access 2007 | Access 2003/XP/2000/97

Filtering query results

• Filter a query based on a text box value in Access 2007 | Access 2003/XP/2000/97
• Filter a query based on InputBox command in Access 2007 | Access 2003/XP/2000/97
• Filter a query based on data entered in a text box in Access 2007 | Access 2003/XP/2000/97

Errors

• How to fix a "Compile error.in query expression" Error in Access 2003/XP/2000/97

Pass-through queries

• Create a pass-through query to retrieve the nextval from an Oracle sequence in Access 2003/XP/2000/97
• Create a pass-through query to retrieve the nextval from an Oracle sequence with VBA code in Access 2003/XP/2000/97
• Call an Oracle stored procedure using VBA code in Access 2003/XP/2000/97

Parameter queries

• Create a query that accepts "Start" date and "End" date as parameters in Access 2003/XP/2000/97
• Create a query that accepts a "barcode" number as a parameter in Access 2003/XP/2000/97
• Create a parameter query that performs a wildcard search in Access 2003/XP/2000/97
• Remove parameters from a query in Access 2003/XP/2000/97

Stack rank

• Perform a stack rank in a query in Access 2003/XP/2000/97
• Perform a stack rank (values greater than 0) in a query in Access 2003/XP/2000/97

Miscellaneous

• Use a query to find duplicates in Access 2007 | Access 2003/XP/2000/97
• Use a query to find unmatched entries between two tables in Access 2003/XP/2000/97
• View only records with uppercase letters for a certain field in Access 2003/XP/2000/97
• Complex set of queries that involve calculating the pass rate for each subject in Access 2003/XP/2000/97
• Display parameter values in a report based on a parameter query in Access 2003/XP/2000/97
• Create a query to differentiate between upper and lower case in Access 2007 | Access 2003/XP/2000/97
• Use a public string as part of the criteria in a query in Access 2003/XP/2000/97
• Convert IF formula in Excel to IIF formula in a query in Access 2003/XP/2000/97
• Retrieve Top N records from a query in Access 2007 | Access 2003/XP/2000/97
• Determine winning bids in a query in Access 2003/XP/2000/97
• Suppress system messages (Query confirmations) in Access 2003/XP/2000/97
• Create a query that displays movies loaned out in Access 2003/XP/2000/97
• Handling "Divide by Zero" errors in queries in Access 2007 | Access 2003/XP/2000/97
• Calculate a patient's weight loss between the last 2 visits in Access 2003/XP/2000/97
• Extract Last Name from a Full Name value in a query in Access 2007 | Access 2003/XP/2000/97

MS Access: Modules

A module is a collection of user-defined functions, subroutines, and global variables written in VBA code. These objects can then be used/called from anywhere in your Access database.

Tutorials

• TUTORIAL: VBA Basics (Understanding the VBA environment) in Access 2007 | Access 2003/XP/2000/97
• TUTORIAL: Debugging VBA Code (Understanding VBA's Debugging Environment) in Access 2007 | Access 2003/XP/2000/97

Navigation Pane (Access 2010 and 2007)

• Display modules in the Navigation Pane in Access 2007
• Display all objects (tables, queries, forms, reports, modules) in the Navigation Pane in Access 2007
• Display tables and related objects in the Navigation Pane in Access 2007

Errors

• Database variable "not defined" error in Access 2003/XP/2000/97
• How to fix a "Compile error.in query expression" Error in Access 2003/XP/2000/97

Launch applications

• Launch Microsoft Word from Access 2003/XP/2000/97
• Open a Microsoft Word document from Access 2003/XP/2000/97
• Launch Microsoft Excel from Access 2003/XP/2000/97
• Launch Notepad from Access 2003/XP/2000/97
• Launch an application from Access 2003/XP/2000/97
• Open a PowerPoint file from a macro in Access 2003/XP/2000/97

Sequential numbers

• Create a sequential number that you can control instead of using an AutoNumber field in Access 2003/XP/2000/97
• Create a sequential number that you can control using a format such as AGR-0001 in Access 2003/XP/2000/97
• Create a sequential number that you can control using a format such as A05-0001 in Access 2003/XP/2000/97
• Create a sequential number that you can control using a format such as OD00000001 and generate multiple records at once in Access 2003/XP/2000/97
• Create a sequential number you control that is assigned every time a form is opened in Access 2003/XP/2000/97

Oracle

• Link to an Oracle table using VBA code in Access 2003/XP/2000/97
• Create a pass-through query to retrieve the nextval from an Oracle sequence with VBA code in Access 2003/XP/2000/97
• Call an Oracle stored procedure using VBA code in Access 2003/XP/2000/97

Updates

• Perform an update with VBA code in Access 2003/XP/2000/97
• Update a date field with VBA code in Access 2003/XP/2000/97
• Reset a text field to NULL with VBA code in Access 2003/XP/2000/97
• Renumber invoices with VBA code in Access 2003/XP/2000/97

Converting Macros to VBA

• Convert CancelEvent macro to VBA code in Access 2003/XP/2000/97
• Convert Close macro to VBA code in Access 2003/XP/2000/97
• Convert GoToControl macro to VBA code in Access 2003/XP/2000/97
• Convert GoToRecord macro to VBA code in Access 2003/XP/2000/97
• Convert OpenForm macro to VBA code in Access 2003/XP/2000/97
• Convert OutputTo macro to VBA code in Access 2003/XP/2000/97
• Convert Quit macro to VBA code in Access 2003/XP/2000/97
• Convert RunCommand macro to VBA code in Access 2003/XP/2000/97

Hide/Unhide Close button on Access program

• Hide Close button on Access program in Access 2003/XP/2000/97
• Unhide Close button on Access program in Access 2003/XP/2000/97

Tab Controls

• Establish tab order between objects in tab control pages in Access 2003/XP/2000/97
• Set focus on first text box on first tab page and go to next record in Access 2003/XP/2000/97

Miscellaneous

• Quit Access using VBA code in Access 2003/XP/2000/97
• Add new record using VBA code in Access 2003/XP/2000/97
• Delete a record using VBA code in Access 2003/XP/2000/97
• Find next incremental value for an AutoNumber field in Access 2003/XP/2000/97
• Retrieve the user name from Windows while in an Access database in Access 2003/XP/2000/97
• Access a value from a table using VBA code in Access 2003/XP/2000/97
• Determine number of records affected by an Execute command in Access 2003/XP/2000/97
• Change the tab order on a form with VBA code in Access 2003/XP/2000/97
• Filter report results using the OpenReport VBA command in Access 2003/XP/2000/97
• Create new database file and transfer tables using VBA in Access 2003/XP/2000/97
• Calculate the number of workdays between two dates in Access 2003/XP/2000/97
• Set up a report to print on legal size paper using VBA code in Access 2003/XP/2000/97
• Test a string for an alphanumeric value in Access 2003/XP/2000/97

Functions - Category

MS Access: Functions - Category

Below is a list of the most commonly used functions in Access. These functions can generally be used in either VBA code or SQL/Queries.

String Functions

Asc Chr Concat with & Format (with Strings) Instr InstrRev LCase

Left Len LTrim Mid Replace Right RTrim

Space Str StrConv Trim UCase

Advanced Functions

Case Choose CurrentUser First

iif IF-THEN-ELSE IsDate IsNull

IsNumeric Last Nz Switch

Domain Aggregate Functions

DAvg DCount DFirst

DLast DLookup DMax

DMin DSum

Numeric/Mathematical Functions

Abs Atn Avg Cos Count Exp

Format (with Numbers) Fix Int Max Min Rnd (random number)

Round Sgn Sum Val

Date Functions

Date DateAdd DateDiff DatePart DateSerial DateValue Day

Format (with Dates) Hour Minute Month MonthName Now Second

Time TimeSerial TimeValue Weekday WeekdayName Year

Data Type Conversion Functions

CBool CByte CCur CDate

CDbl CDec CInt CLng

CSng CStr CVar

Functions used by Group By Feature in queries

Avg Count

Max Min

Sum

File/Directory Functions

ChDir ChDrive CurDir

Dir FileDateTime FileLen

GetAttr MkDir SetAttr

Miscellaneous Functions

• Test a string for an alphanumeric value
• Convert currency into words
• Creating a custom round function
• Retrieve the user name from Windows while in an Access database

Functions - Alphabetical

S Access: Functions - Alphabetical

Below is a list of the most commonly used functions in Microsoft Access. These functions can generally be used in either VBA code or SQL/Queries.

Alphabetical Listing of Functions

Abs Asc Atn Avg Case CBool CByte CCur CDate CDbl CDec ChDir ChDrive Choose Chr CInt CLng Concat with & Cos Count CSng CStr CurDir CurrentUser CVar Date DateAdd DateDiff DatePart DateSerial DateValue DAvg

Day DCount DFirst Dir DLast DLookup DMax DMin DSum Exp FileDateTime FileLen First Format (with Dates) Format (with Numbers) Format (with Strings) Fix GetAttr Hour iif IF-THEN-ELSE Instr InstrRev Int IsDate IsNull IsNumeric Last LCase Left Len LTrim

Max Mid Min Minute MkDir Month MonthName Now Nz Replace Right Rnd (random number) Round RTrim Second SetAttr Sgn Space Str StrConv Sum Switch Time TimeSerial TimeValue Trim UCase Val Weekday WeekdayName Year

Miscellaneous Functions

Test a string for an alphanumeric value Convert currency into words Creating a custom round function Retrieve the user name from Windows while in an Access database