"We have heard a few of you have missed the ALT D shortcut to open an object in design. As you know, ALT in ribbon apps now allows you to access ribbon shortcuts. Next time you want to open an object in design view, try Control Enter."
TechRepublic lists a number of areas that you might explore when training is needed for a new Office version.
Here are a few:
LINKS TO TIP SHEETS AND ARTICLES
"Instead of telling your users to go out to Microsoft.com and do a search, put hyperlinks to the printer-friendly version of tip sheets and articles on your company's main portal page. Providing links to information you know they need will help you cover the training bases. And presenting the links on an internal web site they already use will show your users that it's okay to go outside of their four firewalls to learn something new. Include your favorite hyperlink in your signature line so it goes out in every e-mail you send."
This collection relates to the transition to 2007, but most are also appropriate for 2010.
"The main intent of these tutorials is to guide frequent users of Microsoft Access through the transition of earlier versions to the new 2007 edition. Throughout the different tutorials . . . topics from program specifications through features added, dropped, or unchanged as well as demonstrating implementations of subjects discussed."
Microsoft Access 2007 in the box (Office editions and what's new)
Microsoft Access Head-to-Head (Access 2003 vs. Access 2007)
Microsoft Access Side-by-Side (Access 2003 vs. Access 2007 continued)
Microsoft Access 2007 PDF and XPS support
Microsoft Access 2007 Ribbon/Office menu customization
Microsoft Access 2007 Navigation Pane customization
Microsoft Access 2007 Working with the Attachment DataType
Microsoft Access 2007 Working with the Rich Text Feature
"You can merge Microsoft Office Access 2007 data with a Word 2007 document by using the Mail Merge Wizard. This demo shows you how to create a simple form letter and how to troubleshoot problems. You can also use this feature to create address labels or any other type of Word document in which you want to display Access data."
2007 apps look different because of the ribbon, but the keyboard can still be used to speed up tasks.
Microsoft has an online course that may help
After completing this course you will be able to:
Accomplish tasks by using sequential shortcut keys, known as Key Tips, shown on the Ribbon.
Navigate around the Ribbon using the TAB key and arrow keys.
Accomplish tasks by using key combinations — keys you press at the same time - exactly as you've done in previous versions of Office.
When viewing a table that has many fields in Design view, you have to scroll up and down to review the field names.
This can be tiresome when you're referring to them constantly, and particularly when you're working with several tables.
The following code produces a field listing for a given table. This can then be copied to Notepad and printed for easy reference.
Enter the code into a module, substituting your table's name where appropriate.
Open the Debug/Immediate window, type ListFields,
Press Enter to produce the listing.
Dim dbs As DATABASE
Dim dbfield As Field
Dim tdf As TableDef
Set dbs = CurrentDb
Set tdf = dbs.TableDefs!NAMEOFYOURTABLE
Debug.Print "Name of table: "; tdf.Name
For Each dbfield In tdf.Fields
When you're testing procedures, you can temporarily convert a block of VBA code to comments that will be ignored during a trial run.
Doing so manually by inserting an apostrophe before each line of code can be a real chore.
To simplify this task,
Open any module in the Visual Basic Editor (VBE)
Choose View >Toolbars>Edit from the menu bar to display the Edit toolbar.
Select the lines of code that you want to turn into comments.
Click the Comment Block button on the Edit toolbar (it's the sixth button in from the right end of the toolbar).
Each line of the selected code is now preceded with an apostrophe. To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.
Access provides an easy way to export data to Excel through the Office Links feature.
To use this feature, simply select a relevant database object and choose Tools>Office Links> Analyze It With Excel.
The worksheet Excel creates includes some minor formatting applied to the field headings that appear in row 1. Some formatting in your original Access database affects the worksheet cell formatting as well. For example, if you're exporting from a datasheet, gridline and font attributes are carried over to Excel. If you use the Office Links feature to export data behind a form, text box shading and font properties are applied.
The final result in Excel may not exactly match your Access data; however, you'll probably find that less work is required to get your Excel version of the data into an easily readable state.
They have include sample database downloads and the code needed to make the engine work.
"Using Microsoft Access and Outlook together can reduce manual processing of Ordering emails very substantially. I know this because sometimes it would take up to 15 minutes to undertake all the little steps of saving customer details into tables and newsletter lists. Also without software, it was very difficult to explain to other staff members what to do when an e-mail arrived. Now we can process the orders in a couple of minutes when Outlook email arrives in the correct folder."
When you add controls to forms and reports you'll often need to add several of the same type, such as when creating a group of option buttons or a series of unbound text boxes. In such cases, repeatedly moving between the Toolbox and the object you're designing can quickly become tedious.
Fortunately, you can make the process easier. When you select the control you want to add from the Toolbox, double-click the control button (like double-clicking the Format Painter). Doing so lets you add as many controls of that type as you need. When you finish, click the button again to disable the control tool.
Access tries to prevent user errors that would have calamitous effects on data or an application by throwing up confirmation boxes before potentially dangerous actions. This provides users with an out before committing irreversible changes to the database.
Although you probably want these confirmation dialog boxes in place for end-users, you may find that they slow your work down too much. You may, also, click through the dialog boxes so quickly that they're essentially ineffective.
If you have a programmer's version of hubris, you can prevent Access from displaying confirmation dialog boxes.
To do so, choose Tools>Options from the menu bar and click on the Edit/Find tab. Then, clear the appropriate check boxes in the Confirm panel that correspond to the dialog boxes you want to suppress.
Finally, click OK.
In 2007, click the Office logo and then Access Options
When setting up a macro in VBA, if you want to declare multiple variables in one line of code, be sure to specify the type for each variable, even if the variables are the same type. Avoid code like the following:
Dim strFName, strLName, strMI As String
In such a case, only the last variable, strMI, is actually declared as a String type. The first two variables are designated by default as Variant data types.
To correctly declare the three variables, you would use the statement:
Dim strFName As String, strLName As String, strMI As String
You, probably. take for granted how your direction arrow and Enter keys behave in Access.
When you press Enter the focus moves to the next field and the contents of the field are selected. Likewise, when you press the direction arrow keys, the focus moves to the next field in the appropriate direction. You aren't locked into these behaviors.
If you'd like, you can set up the arrow keys to move from one character to the next in the current field, rather than moving focus to the next field.
You can also configure the Enter key to move to the next record when it's pressed or configure it to do nothing at all.
When you do move focus to another field, you have the option to place the insertion point at the beginning or end of the field, rather than selecting the field's entire contents.
To modify these settings, choose Tools>Options from the menu bar and click on the Keyboard tab. Then, simply make the selections you want and click OK.
In 2007 it's under Access Options>Advanced when you click the Logo in the upper left corner.
If you find the need for Armed Forces photos and art, here is the place to look.
Regardless of your opinion about their present mission, the military does present a spectacular visage.
"06/17/06 - An F/A-18E Super Hornet aircraft sits at the ready as storm clouds pass overhead aboard the Nimitz-class aircraft carrier USS Ronald Reagan (CVN 76) in the Philippine Sea June 17, 2006.
(U.S. Navy photo by Photographer's Mate 2nd Class Aaron Burden)
All of these files are in the public domain unless otherwise indicated. However, we request you credit the photographer/videographer as indicated or simply "Department of Defense."
When you type an entry in a combobox control Access will typically attempt to complete the entry based on the control's lookup list.
This is controlled by the AutoExpand property, which is set to Yes (-1) by default.
Although such behavior is helpful, it can cause problems if your value list contains several items that are close in spelling, since it's easy for users to accidentally let Access choose the wrong item.
You can avoid errors by setting the control's AutoExpand property to No (0) in Design view or using VBA to set the property equal to 0.
Once you've made the change users are forced to type the entire entry or select an item using the combobox control's dropdown list.
XML makes data transferable between applications.
Here is a tutorial with downloadable files.
Some simple guidance of how to transfer data from Excel or Access into HTML web pages using XML data files. VBA programs can be used to export data tables from Excel or Access into simple XML files. There are several examples of using different methods to display the XML and XSL files on web pages in order to quickly share your data with others.
Plain Figures is a method of transforming statistical and financial data into figures, tables and graphs that people readily understand.
Have you ever:
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."?
Then you know how important the clear display of numerical information can be.
People have trouble using numerical information for many reasons. Most commonly, authors don't know:
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.
Plain Figures is a partnership between Sally Bigwood, located in Wakefield, Yorkshire, UK, and Melissa Spore, who divides her time between Toronto and Saskatoon, Canada. Sally and Melissa are sisters and both have dual citizenship in the United States.
[Edited entry from 6/4/2006]
See all Topics
I was disappointed recently when I tried to look up Eric Desart's ToolPak translator. I found his site "niet beschikbaar."
I won't use the boy and wet thumb story, but Ron de Bruin did spring up to save the day and make the download available.
"Ever wanted an oversight of the Analysis-ToolPak Add-In functions, their descriptions, their arguments, their VBA and Procedure names, and all of this in your LOCAL language including translations versus the corresponding English names?
This utility extracts this data from your LOCAL MS Excel edition.
As such this table can be generated for ANY LANGUAGE EDITION of MS Excel, even when this language is not yet integrated in the utility."
If you're working with a replicated database, always compact it twice before you synchronize.
The first time you compact, Access marks objects that need to be deleted, but it doesn't actually remove them.
The flagged objects are removed the second time you compact the database. Although there's no harm in additional compacting, there's no added benefit.
This is particularly important to do when working with the Design Master. Each time you make design changes to the Design Master, a copy of the original object is kept in the database.
If you edit and save a Report or Form 10 times, your database actually has 9 older versions of the form in it. The same is true if you change the schema of a Table or Query If you synchronize before compacting, all of the versions are sequentially sent to the other replicas until everything is in synch, and you can't reclaim the space taken up by the older versions.
However, if you compact the database first, only the most recent form version is sent to the replica.
(change 10.0 to 9.0 for Outlook 2000 SP3,11.0 for Outlook 2003, 12.0 for Outlook 2007)
Under that key, add a new string value named Level1Remove.
For the value for Level1Remove, enter a semicolon-delimited list of file extensions. For example, entering this:
would unblock Microsoft Access files and Internet shortcuts. Note that the use of a leading dot was not previously required, however, new security patches may require it. If you are using "mdb;url" format and extensions are blocked, add a dot to each extension.
Note also that there is not a space between extensions.
If you are using this registry entry, a glance at Help>About Microsoft Outlook will show Security Mode: User Controlled above the license information.
After applying this registry fix or using one of the above tools, the user still has to save the attached file to a system drive before opening it. In effect, the fix rolls the attachment behavior back to Outlook 2000 SR-1, with its included Attachment Security Fix.
An end-user cannot bypass this "save to disk" behavior and open the file directly from the mail message, though an Exchange administrator can.
Do you remember all of the clip art that was available locally with Office XP?
When you have an Internet connection, you have access to the Office Online collection, but if you would like more clip art installed on your machine:
A small amount of sample clip art images was included The 2007 Office systems and Office 2003 and is part of the "local collection" that is searched when you do not have Internet access to the Microsoft Office Online Clip Art and Media Web site. Office 2003 no longer included a media content CD with additional clip art. However, the Microsoft Office XP Media Content CD can still be installed locally or on a network share.
The Office XP Media Content CD contains approximately 35,000 clips that are a subset of the clips that are available on the Microsoft Office Online Clip Art and Media Web site. The Office XP Media Content CD was included with Microsoft Office XP Professional, Microsoft Office XP Standard, and Microsoft Publisher 2002 Deluxe Edition.
To install the contents of the Office XP Media Content CD on a computer, follow these steps:
Exit all programs that are running
Insert the Office XP Media Content CD into the CD drive or into the DVD drive
(Hold down the SHIFT key to prevent the program from automatically starting. If Microsoft Windows Installer automatically starts, click Cancel)
Click Start, click Run, type the following command, and then click OK:
msiexec.exe /i CD_drive:\CAG.MSI ADDLOCAL=ALL /qb
(CD_drive is the letter of the drive that contains the Office XP Media Content CD)
It's good practice to always use the Option Explicit statement in the beginning of your code modules to ensure that all variables are unambiguously declared in your procedures.
With this process in place, you'll receive a "Variable not defined" error if you try to execute code containing undeclared variables. Without this statement, it's possible to mistype variable names, which would be interpreted as new Variant type variables.
This could severely impact the results of your code, and you might not ever know it. If you do find a problem, tracking down where the error is can be a chore.
Although you can manually type the statement into your modules, changing a setting in Access can ensure that the statement is always added to new modules.
Open a module (start the VBA Editor)
Choose Tools>Options from the menu bar
On the Editor tab of the Options dialog box, select the Require Variable Declaration check box in the Code Settings panel
yes, you are telling the truth we are able to repair our access file or mdb file in such a way but those file which are very less corrupted. For badly corrupted file you need the Access repair software which repair and recover your corrupted and damaged access file.
Data modeling can be an exhausting, time consuming process. Here are some samples that may establish a starting point.
The website has close to 100 database models for experimentation, practice and examples.
"My intention is to provide a wide range of 'Kick Start' Models that anyone can use as a starting-point, and could extend cleanly and logically, with appropriate reference to the Business Rules.
It is not my intention to provide Models that can be used off-the-shelf to meet the requirements of a large commercial organization.
After all, that is one of the things I do for a living !!!
None of the Models is the complete and final solution in its area, but any of them can be added to easily and quickly to meet a specific requirement. The logic in each Model is intended to be correct and to contain the minimum Entities for the area being modeled."
"Find out how to reap the benefits of data normalization in Access while ensuring that your system provides users with all the information they need. Learn to relate your application's tables to each other, so that your users can view the data in the system as a single entity. After you define relationships between tables, you can build queries, forms, reports, and data access pages that combine information from multiple tables."
Have you found that you cannot update linked Excel tables in Access 2003?
If you have installed Microsoft Office 2003 Service Pack 2 (SP2):
Because of legal issues, Microsoft has disabled the functionality in Access 2003 and in Access 2002 that let users change the data in linked tables that point to a range in an Excel workbook. However, when you make changes directly in the Excel workbook, the changes appear in the linked table in Access.
"A jury in U.S. federal court found that Microsoft infringed on a Guatemalan inventor's 1994 patent on technology linking the company's Access and Excel programs, and ordered the world's largest software maker to pay $8.9 million in damages.
Create your template form by specifying all the properties you want to maintain from form to form.
Then, save the form using any name.
Next, select Tools>Options from the menu bar and select the Forms/Reports tab.
Enter your template's name in the Form Template box to replace the Access default (Normal) and click OK.
The next time you create a form, Access will base it on your form template rather than the typical Normal template.
In Access 2007, click the Office button in the upper left corner and the click Access Options.
The design options are located under Object Designers: Forms/Reports.
The same thing can be done with Reports.
(The settings are saved in the Access workgroup information file, not in a particular data base)
As you work with a form, you will, sometimes, find controls would benefit from minor tweaking. You may want to change the color of a text box, change the font that's used, or remove scroll bars.
You may have been told switch to Design view to make changes. However, you can display the property sheets for a control while you're in Form view.
To do so, click the Properties on the View menu, Right-click, or press [Alt][Enter].
As in Design view, the displayed properties relate to the control that is selected on the form. The property changes you make are reflected on the fly.
You will be prompted to resave your form when you close it.
"When you click Practice in Access at the bottom of this page, a practice database will download to your computer and open in Access, and a separate window with practice instructions will appear alongside."
While a few of us may be guilty of constructing databases on the run, and then trying to clean up the mess later, the wise ones plan ahead.
What information will you need in the future? What reports are going to be requested? If you're the bean counter, do you need to count all the legumes?
"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."
Here is an understandable article about how to put together a normalized database.
One topic covered is what should be used for a Primary or foreign key.
"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.
To transfer data from an Access query or table in another Office program, such as Word, there's no need to manually export the data.
Open the target Office document
Arrange both applications on the screen
(Right-click an empty part of the Task bar and choose Tile Windows Vertically)
Switch to Access and select the fields or records that you want copied
When you've finished selecting the data, move the mouse pointer near the border of the selection until it turns into an arrow
Finally, drag and drop the data to your target document
You can also select a whole table, go to Edit>Copy. Switch to Word or Excel and Paste.
It works in the other direction too. Select some Excel data. Switch to Access. While viewing the Tables Objects, Paste the Excel data. It will form a new table.
It can be difficult to tell which text box on a form you're currently working with.
One solution is to highlight the current position, with a different background.
Access 2000+ allows you to do this with conditional formatting, but you can also get a similar result using code.
To do so, create a new Module and add the following code:
Function Highlight(Stat As String) As Integer Dim ctrl As Control On Error Resume Next Set ctrl = Screen.ActiveControl If Stat = "GotFocus" Then ctrl.BackColor = 65535 ElseIf Stat = "LostFocus" Then ctrl.BackColor = 16777215 End If End Function
Save and close the Module, then open the appropriate Form in Design view.
Click the Code button and insert =Highlight("GotFocus") in each of the Form's textbox control's GotFocus event procedure.
Likewise, add =Highlight("LostFocus)") to each textbox's LostFocus event procedure.
When you've finished, save the changes, close the VBE, and switch to Form view.
When you tab to a field, it's shaded yellow. When you tab away from the field, its background is restored to white.
This site provides quite complete on line manual about the care and feeding of MS Access.
What makes this tutorial special are the sample database files that can be used to demonstrate the various features.
"Microsoft Access is a development environment used to create computer databases for the Microsoft Windows family of operating systems. This site provides lessons, examples, and links on how to use and explore MS Access. We also separately provide some guidance on VBA, the programming language that ships with Microsoft Access."
You can change how your direction arrow and Enter keys behave in Access.
By default, when you press Enter the focus moves to the next field and the contents of the field are selected. When you press the direction arrow keys, the focus moves to the next field in the appropriate direction.
If you would like, you can set up the arrow keys to move from one character to the next in the current field, rather than moving focus to the next field.
You can configure the Enter key to move to the next record when it's pressed or configure it to do nothing at all.
When you do move focus to another field, you have the option to place the insertion point at the beginning or end of the field, rather than selecting the field's entire contents.
To modify these settings, choose Tools> Options from the menu bar and click on the Keyboard tab. Make the selections you want and click OK.
Normalization, simplistically, is setting up tables of data so that information is entered only one time. Access is not as malleable as Excel, so for demonstration purposes, spreadsheet entries can be used to show the concept.
"This article explains how to use Microsoft Excel to create prototypes of your Access databases and how to make sure your data adheres to a set of rules called normal forms. Normal forms aren't hard to understand, and they're critical to designing useful databases."
Digit (0 through 9 entry required; plus [+] and minus [-] signs not allowed).
Digit or space (entry not required; plus and minus signs not allowed).
Digit or space (entry not required; blank positions converted to spaces, plus and minus signs allowed).
Letter (A through Z, entry required).
Letter (A through Z, entry not required).
Letter or digit (entry required).
Letter or digit (entry not required).
Any character or a space (entry required).
Any character or a space (entry not required).
, : ; - /
Decimal placeholder and thousands, date, and time separators. (The actual character used depends on the regional settings specified in Microsoft Windows Control Panel.)
Causes all characters that follow to be converted to lowercase.
Causes all characters that follow to be converted to uppercase.
Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.
Causes the character that follows to be displayed as a literal character. Used to display any of the characters listed in this table as literal characters. (For example, \A is displayed as just A.)
You can also enclose any literal string in double quotation marks.
Setting the InputMask property to the word Password creates a password entry text box. Any character typed in the text box is stored as the character but is displayed as an asterisk (*).
If you don't like the error message that appears by default i.e.:
"The value you entered isn't appropriate for the input mask '!\(999") "000\-0000;;_' specified for this field"