It takes a village

Labels: General, List

2003-2007-2010

Labels: General, Ribbon

Only for the brave

Labels: Customize, General, Tips

I'd Like to Make It Clear

• squinted your eyes trying to see the numbers in a PowerPoint presentation?
  
  
• scratched your head at a charity leaflet with an indecipherable pie chart titled 'Where your donation goes' ... and set it aside?
  
  
• missed discussion at a meeting because you were busy trying to figure out the figures?
  
  
• put aside a graph or table, thinking "I'm not good with numbers."?

• what to include: when unsure what numbers are important, people frequently display them all, overpowering the reader with irrelevance.
  
  
• which format to use: the choice between text and table, table and chart, bar and pie.
  
  
• how to use the technology effectively: computer software generates graphs easily, but the results hide your point behind incomprehensible chartjunk.
  
  
• how to explain the information: selecting the right words for titles, columns and captions.

Labels: General, Tips

Good for all levels of user

• Casual Users - basic tips and explanations;
  
• Serious Users - ideas for forms, combos, reports, and code;
  
• Flaws in Access - unfixed bugs that will bite you;
  
• Traps to avoid - problems that may not be obvious;
  
• Specific Applications - a couple of examples where people get stuck;
  
• Tips for xBase developers - for people who used the old dBase;
  
• Functions you can use - code to copy;
  
• Upgrading - issues when changing versions;
  
• Other sites - links to other sites with free Access tips.
  

Labels: General, Tips

Link in a snap

1. Choose File>Open from the menu bar
   
   
2. Select the appropriate data format from the Files Of Type dropdown list
   
   
3. Open the file as you would any Access database

Labels: General, Tables, Tips

Clever timesavers

CascadingComboBoxes.mdb

This sample illustrates how to restrict the value of one combo box based on the value of another

EmailingSpecificReports.mdb

This illustrates how to email the same report with different data to a variety of users

RelinkOnOpen.mdb

This sample illustrates how re-link, automatically, tables if the database has been moved

UIDesign.mdb

Shows a variety of samples of good and bad User Interface design techniques

Labels: General, Samples, Tips

Use the keyboard

Labels: General, Tips

Data bits and pieces

"Data modeling is probably the most labor intensive and time consuming part of the development process. Why bother especially if you are pressed for time? A common response by practitioners who write on the subject is that you should no more build a database without a model than you should build a house without blueprints.

The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language, it can be reviewed and verified as correct by the end-users.

The data model is also detailed enough to be used by the database developers to use as a "blueprint" for building the physical database. The information contained in the data model will be used to define the relational tables, primary and foreign keys, stored procedures, and triggers. A poorly designed database will require more time in the long-term. Without careful planning you may create a database that omits data required to create critical reports, produces results that are incorrect or inconsistent, and is unable to accommodate changes in the user's requirements."

Labels: General, Relationships, Tables, Tutorials

"In general, a key field should have these characteristics:

Should be One Field

It is possible to define multiple fields as the key fields of a table, but a single field is preferable. ...Far better to have a CustomerID number than a combination of other fields to define a customer.

Should be Numeric

Access offers an AutoNumber field that is a Long Integer which is ideal for key fields. These values are automatically unique for each record and supports multi-user data entry as well.

Should Not Change Over Time

A key field should not change over time. Once identified, like a social security number, it should never change.

Should be Meaningless

To ensure a key field doesn’t change over time, it should have no meaning and therefore no reason to change over time.

• What Data Do You Need?
  
• What Are You Going to Do with the Data?
  
• How Is Your Data Related to Each Other?
  
• What Is Going to Happen to the Data Over Time?
  
• Learn How to Use Queries

Labels: General, Tables

Simple exchange

1. Open the target Office document
   
   
2. Arrange both applications on the screen
   (Right-click an empty part of the Task bar and choose Tile Windows Vertically)
   
   
3. Switch to Access and select the fields or records that you want copied
   
   
4. When you've finished selecting the data, move the mouse pointer near the border of the selection until it turns into an arrow
   
   
5. Finally, drag and drop the data to your target document

Labels: General, Tables, Tips

If one is missing, use the other

1. Select the Queries, and then click "Create query in Design view".
   
2. Choose the table you want to sort, click Add.
   
3. Click Close.
   
4. Drag down all the fields you want to display in your form, including the two separate fields you want to alphabetize.
   
5. Insert a new column on the left side of the QBE grid.
   
6. In the Field cell, enter the expression
   NZ([CompanyName],[LastName])
   
7. Select Ascending for the Sort option.

When you run the query, if CompanyName is null (empty — no entry), the NZ() function uses the contents in LastName instead.

Here's another way to do it:

See all Topics

access

Labels: Forms, General, Tips

Ask your peers

• The Use of LIKE
  
  
• Calculated Field
  
  
• Conditional formatting
  
  
• Hide/Show Toolbars
  
  
• Change Default Printer without using print dialog
  
  
• Problems with duplicate matches in my queries
  
  
• Import Excel into Access Table
  
  
• Sending Email Via Lotus Notes
  
  
• Automated E-mail For Lotus Notes and Microsoft Outlook
  
  
• Print Report of the Form onscreen
  
  
• Table/Field Structure
  
  
• Hiding Access Background and taskbar

Labels: General, Tips

More data than the pie will hold

"Chris Weber provides you with a generic method to control the data for pie charts that are actually readable."

Labels: General, Tips, Tutorials

Definition and explanation

"If you're searching for a more flexible data management system, a database might be just the salvation you're looking for.

What is a database? Quite simply, it's an organized collection of data. A database management system (DBMS) such as Access, FileMaker Pro, Oracle or SQL Server provides you with the software tools you need to organize that data in a flexible manner. It includes facilities to add, modify or delete data from the database, ask questions (or queries) about the data stored in the database and produce reports summarizing selected contents.

Microsoft Access provides users with one of the simplest and most flexible DBMS solutions on the market today. Regular users of Microsoft products will enjoy the familiar Windows "look and feel" as well as the tight integration with other Microsoft Office family products.

Labels: General, Tutorials

Use local Help

1. Bring up the Help Task Pane (The F1 key will do this.)
   
   
2. At the bottom of the "See also" box there is a hyperlink: "Office Online Settings"
   
   
3. Click this link; you will get the Service Options dialog box
   
   
4. Uncheck the option: "Search online content when connected"

Office will now use the help files on the local hard drive. It is much faster!
(Editing will affect all Office applications)

In office 2007, left click on the "Connected to Office Online" and choose local



If you need to disable its use through a Group Policy, or in the Registry, see:

Microsoft Support:
How to disable Microsoft Office Online featured links in Office

Be aware that if you do turn it off, you might miss some of the Office online feature, like tutorials and downloads.

Office Online: Get More Out of the Microsoft Office System



See all Topics

access

Labels: General

PowerPoint in Access

"Create a PowerPoint slide presentation from scratch using Access data. In addition, display and control a slide show from within an Access form. Walk through the solution and explore ways to extend the sample for your own applications.

This article looks at two ways of interaction between Access and PowerPoint.

The first sample illustrates how to create a PowerPoint presentation from the data in an Access table using Automation.

The second sample shows how to display and manipulate an existing PowerPoint presentation inside of an Access form, also using Automation."

Here is an MSDN article:
Working with PowerPoint Presentations from Access Using Automation

If you have some knowledge of VBA, you can probably figure it out from the code on the Access Form.



Office 2003 Sample:
Working with PowerPoint 2003 Presentations from Access 2003 Using Automation



See all Topics

access

Labels: Forms, General, Tutorials

Multiple selections

"How to open a report based on a multiple selection in a list box. For example, you may have a list of makes of car and a report that shows various details for each make of car. You want to allow the users to select a range of cars and pull up the report according to that selection."

Microsoft KB:
How to Use a Multi-Select List Box to Filter a Form

Customize a list box, combo box, or drop-down list box



See all Topics

access

Labels: Forms, General, List, Tips

Choose your own location

Word

you can change the default location by going to Tools>Options. On the "File Locations" tab you can modify the storage location.

Excel

Tools>Options. On the "General" tab change the default location.

PowerPoint

uses Tools>Options and the "Save" tab.

Access

Tools>Options and the "General" tab for Databases and Projects

Publisher

Tools>Options "General".

Outlook

will make you take an underground tour into the Registry to change the location to save e-mail attachments.

FrontPage/Expression Web

appears to require the same sort of spelunking.

Labels: Customize, General

More dimensions than Star trek

When a company accumulates a great deal of information, it becomes un-wieldy to work with just basic Excel or Access databases.

There is a database concept called on OLAP cube (On-Line Analytical Processing).

This multidimensional collection of data can be thought of as a 3-D pivot table viewed from flat land.

MSDN:
Just What Are Cubes Anyway?
(A Painless Introduction to OLAP Technology)

OLAPReort.com:
What is OLAP


Wikipedia:
OLAP

Wang.se (Wang Sweden) a Swedish software company:

Create an OLAP Cube



See all Topics

access

Labels: General, Relationships, Tutorials

Go back

"When you use Windows Explorer or the desktop to create a new 2007 Microsoft Office file, a new Office file is created in an XML file format (.dox or .xlsx). For example, this behavior occurs when you right-click the desktop, you point to New, and then you click Microsoft Office Word Document. By default, files that you create in the 2007 Office system are in XML file formats.

This article is about how to create legacy Office files, such as .doc files, .xls files, .ppt files, or .mdb files in the 2007 Office system. You can create legacy Office files without opening any Office applications. To do this, you must modify some settings. The modified settings will apply to all the users who log on to the computer."

Labels: General

Labels: General, Tables, Tips

Avoid the dark spots

A "sandbox" allows the kids to play in a confined area. They can't get into as much trouble as when they are allowed to run around the park.


Frequently asked questions about Access security warnings

"To help enhance the security of your data, you have the option of running Access 2003 in sandbox mode. In sandbox mode, Access evaluates only those expressions in field properties and controls that are safe. An expression is considered safe if it does not use functions or properties that could be exploited by malicious users to access drives, files, or other resources for which they do not have authorization. For example, functions such as Kill and Shell could be used to damage data and files on a computer, so they are not considered safe.

Sandbox mode is implemented by using the Microsoft Jet Expression Service to evaluate expressions. Microsoft Jet 4.0 Service Pack 8 (SP8) or later allows Access to be fully functional, yet still have Jet block unsafe expressions by enabling sandbox mode. However, the Jet expression service is not part of Access. You need to download and install Microsoft Jet 4.0 Service Pack 8 (SP8) or later before you can enable sandbox mode. "

You will see this box if your computer does not have Microsoft Jet 4.0 Service Pack 8 (SP8) or later installed, and/or sandbox mode has not yet been enabled on your computer.

Installing the latest Jet service pack does not automatically turn on sandbox mode and block unsafe expressions. You need to click Yes in the message box to complete the process of enabling sandbox mode.

If you enable sandbox mode without installing Jet 4.0 SP 8 or later, certain features will not work. For example, wizards will not start, and instead, you will see the message "This feature isn't installed, or has been disabled".

About Microsoft Jet Expression Service sandbox mode

You may also see this warning.

Access cannot verify that the file is from a trusted source and that it has not been tampered with since you received it or last worked on it.

You can avoid this bother by digitally signing your file or reducing security to the Low level. This effectively puts Access back on the playground.



Microsoft warns:

"Unless yours is a stand-alone computer (not part of a network), and you don't ever connect to the Internet, and no one ever has physical access to your computer, or your databases and other files are protected with strong passwords, you should not even consider setting the macro security level to Low."

There is another way to do this. Via registery key.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines]
"SandBoxMode"=dword:00000003

2007 has fewer warning messages. Earlier versions of Access forced you to deal with a variety of alert messages - macro security and sandbox mode, just to name two. By default, if you open an Office Access 2007 database outside of a trusted location, you see a single tool called the Message Bar.

VB123.com:

How To Avoid An Access 2003 Sandbox Storm
By Garry Robinson

AccessAdvisor.com:
Microsoft:

How to configure Jet 4.0 to prevent unsafe functions from running in Access 2003

See all Topics

access

Labels: General, Tips

Things you need to know

Labels: General, Tips

Reformat reports

Access is not the most versatile instrument for complex formatting.

If you wish to share your findings, Access does have the ability to re-format Reports into Word documents.

Open the Report in Access and go to Tools>Office Links.
One of the choices is to "Publish It with Microsoft Word."
Here's the command in 2007:



When the data is sent to Word, you will be asked to confirm that you wish to convert the file to "Rich Text Format (RTF)" Click OK.

(RTF is a "universal" format. The Report can be re-saved as a Word "DOC" or "DOCX" file.)

Microsoft KB:
How to send the current record to Word 2000 with automation

Teachers on the web: Aussie SchoolHouse:
Merging Access Data with Word Documents

Use a table or query as a mail-merge data source (2007)



See all Topics

access

Labels: General

Just a keystroke away

[Ctrl][Alt][Spacebar]

Labels: General, Shortcuts

Geek fame

"You probably have a document you use over and over again, something you created to solve a particular problem. You may even find yourself occasionally sending the document to others in e-mail because it's so useful. Now you can share your clever solution with everyone who uses the 2007 Microsoft Office system!

People like you all over the world are allowing others to download and use their document templates on Office Online. Some of these templates have tens of thousands of downloads. They were submitted by people who either wanted to help others or show their great solutions. You can, too.

Labels: General

From reports and forms

Sometimes, you may not want to go to the effort of creating a report; you just need a quick hardcopy of data.

In such cases, you can simply print the Form view of your data. However, doing so also prints the background and shading associated with the form.

If you just need a quick data reference, you probably don't want to waste the resources and time to print such a detailed view. Fortunately, Access has a feature that lets you quickly print just the data from a form or report.

To do so, view the data you want to print in the form or report.

Then, choose Office button>Print>Print Preview (File >Page Setup) select the Print Data Only check box and click OK.

Doing so hides any graphics, lines, control borders, and label controls so that the print out simply contains data.





See all Topics

access

Labels: General, Tips

Add-in's complete

Great news, you can now redistribute and use the PDF and XPS add-in with your Runtime solution.
The Runtime's code has not been changed. Your existing runtime solutions can now officially be distributed with the PDF and XPS add-in by chaining the .msi for PDF and XPS into your install process for your app (after the Runtime).
PDF-XPS Supported

You may copy and distribute the object code form of the add-in listed below together and for use only with the Microsoft Office Access 2007 Runtime software, subject to the license terms accompanying the Microsoft Office Access 2007 Runtime software download:
2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS (located at PDF add-in download)

Labels: 2007, Add-in, General

Better view

With the insertion point in the field, hit: SHIFT+F2.
A Zoom box opens. Enter the formula and hit OK.

BTW: If you enter Field names in the Zoom box without square brackets. If the fields are recognized, Access will add the brackets.



See all Topics

access

Labels: Entries, General, Tips

Document database

When you're documenting your database applications, you may want to include the same visual diagram of your table relationships that's available through the Relationships window.

In Access 2000 thru 2003, this is easy. Simply display the Relationships window as usual and then choose File>Print Relationships from the menu bar. Doing so displays a report preview that you can then print or save.

In 2007, to just print out a report, find Database tools on the Ribbon and click on Database Documenter.

Relationships are at the bottom of the All Object Types tab





See all Topics

access

Labels: General, Properties

Your choice

"This article shows you how to customize the way that Microsoft Office Access 2007 starts by adding switches and parameters to the startup command. For example, you can have Office Access 2007 open a specific file or run a specific macro when it starts."

Labels: Customize, General, Tips

Learning Sites

2007 Access Tutorials

(You will see something different if Office 2003 or 2007 is not installed)

Here are some other sites:

FunctionX Access tutorials

TutorialUSA.com

Access Tutorials, Basic

Access Resources

Tech Tutorials.net

Zicklin School of Business - Baruch College



See all Topics

access

Labels: General, Tips

Separate tables

"Even if all your data is in Access itself, consider using linked tables. Store all the data tables in one MDB file - the data file - and the remaining objects (queries, forms, reports, macros, and modules) in a second MDB - the application file.

In multi-user situations, each user receives a local copy of the application file, linked to the tables in the single remote data file."

• Maintenance: To update the program, just replace the application file.
  Since the data is in a separate file, no data is overwritten.
  
  
• Network Traffic: Loading the entire application (forms, controls, code, etc)across the network increases traffic making your interface slower.
  

• Static look-up data such as postal codes might be kept in its own file.
  
• Linked temporary tables might avoid the need to compact the application file.
  

From Allen Brown's tips for Access users

Access has a tool to do the splitting for you, go to:
Tools>Database Utilities Database Splitter

In Access 2007:

1. On the Database Tools tab, in the Move Data group, click Access Database.
   
2. In the Database Splitter dialog box, click Split Database.
   
3. Type a name for the back-end database, and then click Split.

Also:

Knowledgebase:
How to manually split a Microsoft Access database

MSDN:
About sharing an Access database on a network



See all Topics

access

Labels: Customize, General

Nothing's a problem

"When you import data into Microsoft Access, trailing zeros may be lost. This will happen when you import data that is formatted to show these zeros, but where the zeros are not actually part of the data.

For example, in a Microsoft Excel workbook, you can format the number 1234 so that it will be displayed as 1234.000. When you import this workbook into a Microsoft Access table, the number will be displayed as 1234.

This article shows you how to preserve trailing zeros when you import data into Microsoft Access."

How to Preserve Trailing Zeros When Importing Data

Also:
Word — Decimal Point or Trailing Zeros Missing When You Merge Microsoft Access Database

Excel — Using a Custom Number Format to Display Leading Zeros

Zero Padding Numeric Strings

Add leading zeros to numeric strings in Access



See all Topics

access

Labels: Entries, General

In Datasheet view

When you're working in Datasheet View, you can easily hide columns containing data that you don't need to immediately work with.

To do so, select any field in the column and choose Format >Hide Columns from the menu bar.

As an alternative, right- click on the column's field name and select Hide Columns from the shortcut menu.

To redisplay hidden columns, select Format>Unhide Columns from the menu bar.
Then, select the check boxes next to the field names of any columns you want displayed and click OK.

You can select the Unhide Columns command even if no columns are hidden, allowing you to easily hide multiple columns by clearing the appropriate check boxes.



See all Topics

access

Labels: Customize, General

Writ on tables

"And it came to pass that the cries and lamentations of the Access newbies were heard on high by the gods of the Database, and their hearts were moved to pity for their followers. And they opened their mouths and spake, saying: "Nevermore shall the young and innocent wander witless on their journeys!

1. Thou shalt design normalized tables and understand thy fields and relationships before thou dost begin.
   
2. Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One.
   
3. Thou shalt choose a naming convention and abide by its wisdom and never allow spaces in thy names.
   
4. Thou shalt write comments in your procedures and explain each variable.
   
5. Thou shalt understand error handling and use it faithfully in all thy procedures.
   
6. . . .
   

Thus spake the gods of the Database, and blessed be their names!"

The full list can be found on the Access Web



See all Topics

access

Labels: General

More than you wanted to know

1. Click the Microsoft Office Button, and then
   
   
2. Cick Access Options, Excel Options, PowerPoint Options, or Word Options.
   
   
3. Click Resources
   
   
4. About Program Name, click About.
   Note Program Name is the name of the program you are in, for example, About Microsoft Office Word 2007.
   
   
5. To see information about your computer, in the About Program Name dialog box, click System Info.

Labels: General

When to use one or the other

Use Access when you:

• Require a relational database (multiple tables) to store your data.
  
  
• Might need to add more tables, in the future, to an originally flat or nonrelational data set.
  
  
• Keep a very large amount of data (thousands of entries).
  
  
• Keep data that is mostly text.
  
  
• Rely on multiple external databases to derive and analyze the data that you need.
  
  
• Need to maintain constant connectivity to a large external database, such as one built by using Microsoft SQL Server.
  
  
• Want to run complex queries.
  
  
• Need many people working in the database and you want robust options that expose that data for updating.

Use Excel when you:

• Require a flat or nonrelational view of your data (that is, you do not need a relational database with multiple tables).
  
  
• This is especially true if that data is mostly numeric - for example, if you want to maintain a financial budget for a given year.
  
  
• Want to run primarily calculations and statistical comparisons on your data - for example, if you want to show a cost/benefit analysis in your company's budget.
  
  
• Know that your dataset is manageable in size (no more than 15,000 rows).

Labels: General