Bookmark and Share

Enter your email address:

Delivered by FeedBurner



Home Page













Subscribe here
Add to My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com



  Saturday, February 27, 2010 – Permalink –

Database Genesis

Gotta start somewhere


Microsoft has a tutorial on how to create a database.

Office.Microsoft.com






See all Topics

Labels:


<Doug Klippert@ 3:40 AM

Comments: Post a Comment


  Friday, February 19, 2010 – Permalink –

Navigate Navigation Pane

Ahoy


Here's the Microsoft tutorial:

Meet the Navigation Pane

Also:
"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."
Navigation pane tip




See all Topics

Labels:


<Doug Klippert@ 3:08 AM

Comments: Post a Comment


  Thursday, February 18, 2010 – Permalink –

Custom QAT

Access additions


Applications put most of the most-used commands on the Home tab's Ribbon, not everything is there. You may want to add Close, Close All, or Print commands, for example.

In the upper Left corner is the Quick Access Tool bar.

To update the QAT:
Click the down-pointing arrow to the right of the QAT.
Choose any common commands (New, Close, Print, etc.) by checking the option.






See all Topics

Labels: , , , ,


<Doug Klippert@ 3:28 AM

Comments: Post a Comment


  Wednesday, February 10, 2010 – Permalink –

Access Communities

It takes a village


Here are some links the Access groups. Someone has probably asked your question before.
Access Communities



See all Topics

Labels:


<Doug Klippert@ 3:49 AM

Comments: Post a Comment


  Tuesday, February 02, 2010 – Permalink –

Office Training

Suggestions

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."
  • ONLINE TRAINING
  • E-LEARNING
  • WEBCASTS
  • VIRTUAL TRAINING
  • MULTILINGUAL SCREENTIPS AND TRANSLATIONS
  • COMMAND REFERENCE GUIDES
  • OFFICE ONLINE AT WORK
10 ways to train your users on Office 2007 for free




See all Topics

Labels: , , , ,


<Doug Klippert@ 3:51 AM

Comments: Post a Comment


  Tuesday, January 26, 2010 – Permalink –

Where's the Template

Find and/change storage spots



Describes the different template categories and the locations of templates in 2007 Office programs. Also describes the registry settings that control where to find your custom templates.

Support.Microsoft.com






See all Topics

Labels: , , ,


<Doug Klippert@ 3:44 AM

Comments: Post a Comment


  Monday, January 18, 2010 – Permalink –

Access Transition Tutorials

Available in PDF


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

  • Microsoft Access 2007 Collect Data Through Emails

Access-Freak.com See all Topics

Labels:


<Doug Klippert@ 3:38 AM

Comments: Post a Comment


  Monday, January 11, 2010 – Permalink –

Merge Access with Word

Database integration



"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."
Office.Microsoft.com





See all Topics

Labels: ,


<Doug Klippert@ 3:40 AM

Comments: Post a Comment


  Thursday, January 07, 2010 – Permalink –

Automate Shutdown

Close everything

It's generally considered good form to close all forms and reports when you're shutting down a database.
Here's a link to some code that takes care of it for you.



TechRepublic.com





See all Topics

Labels:


<Doug Klippert@ 3:59 AM

Comments: Post a Comment


  Sunday, January 03, 2010 – Permalink –

Keyboard and Key Tips

Finger it out



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.
Office.Microsoft.com/Training







See all Topics

Labels: , , , ,


<Doug Klippert@ 3:52 AM

Comments: Post a Comment


  Tuesday, December 29, 2009 – Permalink –

Close Forms

Auto Shutdown

Here's how to close a form after it’s used:

  1. Open the first form in Design view.
  2. Double-click the Form Properties button.
  3. Click on the Event tab.
  4. Click in the On Deactivate text box.
  5. Select Event Procedure.
  6. Click the Build button.
  7. At the prompt, enter: Me.TimerInterval =1.
    (Try something like 30000 milliseconds)
  8. Press [Alt][Q].
  9. In the Form Properties window, click in the On Timer property text box.
  10. Select Event Procedure.
  11. Click the Build button.
  12. At the prompt, enter: DoCmd.Close.
  13. Press [Alt][Q].
  14. Save the form.

TechRepublic


See all Topics

Labels:


<Doug Klippert@ 3:36 AM

Comments: Post a Comment


  Thursday, December 24, 2009 – Permalink –

List Fields in Access Table

Bit o' code


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.
Sub ListFields()
Dim dbs As DATABASE
Dim dbfield As Field
Dim tdf As TableDef

Set dbs = CurrentDb
Set tdf = dbs.TableDefs!NAMEOFYOURTABLE

Debug.Print ""
Debug.Print "Name of table: "; tdf.Name
Debug.Print ""

For Each dbfield In tdf.Fields
Debug.Print dbfield.Name
Next dbfield
End Sub


[Edited entry from 12/24/2006]




See all Topics

Labels:


<Doug Klippert@ 3:31 AM

Comments: Post a Comment


  Thursday, December 17, 2009 – Permalink –

Combo Box Queries

How to



Parameter queries add flexibility to filtering records in a database. To make it easy, take a look at this approach from Martin Green's Office Tips site:

Drop down box in a Parameter Query

  1. Build a dialog box with as many combo boxes as you need.
  2. Design a query to read its criteria from the information on the dialog box.
  3. Create a macro or visual basic procedure to tell them both what to do.
Also:
Base Combo Box on Parameter Query to Filter Values



[Edited entry from 12/17/2006]


See all Topics

Labels:


<Doug Klippert@ 3:23 AM

Comments: Post a Comment


  Friday, December 11, 2009 – Permalink –

Hungarian Notation

Belépés



This tip is useful in a number of applications.

When you name an object, include a prefix that identifies the type of object.

When naming a table for Customers, use "tblCustomers" .
You could also have a form for customers. It would be "frmCustomers" .


It's called Hungarian notation because with the prefix, it does not look like an English word.

Dr. Charles Simonyi developed the convention at Microsoft, and he is from Hungary.

He wrote an article on Hungarian notation for MSDN, the Microsoft Developer's Network.

Hungarian notation


Here are some prefixes:
  • tbl- Table
  • qry- Query
  • frm- Form
  • rpt- Report

[Edited entry from 12/9/2006]




See all Topics

Labels:


<Doug Klippert@ 3:32 AM

Comments: Post a Comment


  Thursday, November 19, 2009 – Permalink –

Control Access Text Alignment

Distribute Text Evenly Within Controls


When you add form or report controls, the Text Align property defaults to General setting: characters align to the left while numbers and dates align to the right.

When you set up controls to act as headings or titles, you can achieve interesting visual results by changing the Text Align setting to Distribute (This is called Justify in Word).

This setting distributes characters within the control evenly to span its entire width.

If you apply this setting to a textbox control, the alignment switches to Left alignment when you click inside the control to allow for easy data entry.
General (Default)
The text aligns to the left; numbers and dates align to the right.
Left
The text, numbers, and dates align to the left.
Center
The text, numbers, and dates are centered.
Right
The text, numbers, and dates align to the right.
Distribute
The text, numbers, and dates are evenly distributed.



[Edited entry from 11/14/2006]




See all Topics

Labels:


<Doug Klippert@ 3:29 AM

Comments: Post a Comment


  Sunday, November 15, 2009 – Permalink –

Color News

A multidiscipline subject


Here is a study about how color effects a reader's choice of concentration.

It was intended for newspaper publishers, but the same knowledge can be used in Web design, PowerPoint, or any other reporting application. Word and Excel will also benefit.

Color, Contrast, and Dimension in News Design

ColorProject

The Poynter Institute is a school for journalists, future journalists, and teachers of journalists.
Poynter.org



[Edited entry from 11/8/2006]




See all Topics

Labels: , , , , , , ,


<Doug Klippert@ 3:01 AM

Comments: Post a Comment


  Friday, November 13, 2009 – Permalink –

Link to Office Documents from Access

Click to Word, PowerPoint, Excel


You can create hyperlinks in Access that jump to other Office documents. The process of specifying the document and the bookmark you want to jump to can be cumbersome.

There's an easy way to specify where in a Word, Excel or PowerPoint document that a hyperlink should jump to, without even having to open the Insert Hyperlink dialog box.

  1. Open the target document and the Access table that contains a hyperlink field.

  2. Select some of the text at the beginning where you want the hyperlink to jump.

  3. Hold down the Ctrl key, drag the selection to the Access hyperlink field you want to set up.

  4. When you release the mouse button, the previously selected text is used as the hyperlink text and the link becomes active.


[Edited entry from 11/7/2006]


See all Topics

Labels:


<Doug Klippert@ 3:40 AM

Comments: Post a Comment


  Thursday, November 05, 2009 – Permalink –

Change Code to Comments

Fast solution


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,
  1. Open any module in the Visual Basic Editor (VBE)
  2. Choose View >Toolbars>Edit from the menu bar to display the Edit toolbar.
  3. Select the lines of code that you want to turn into comments.
  4. 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.



[Edited entry from 10/27/2006]


See all Topics

Labels: , , ,


<Doug Klippert@ 3:39 AM

Comments: Post a Comment


  Saturday, October 31, 2009 – Permalink –

Hiding Duplicates in Query Results

Once is enough


It's easy to hide duplicate entries when you run a query, even though Access doesn't go out of its way to call attention to this ability.
  • Set up a query as usual using the design grid.

  • Choose View>Properties from the menu bar to display the Query Properties dialog box.

  • Change the Unique Values property to Yes.


Access displays unique records based on each field returned by the query.



[Edited entry from 10/17/2006]




See all Topics

Labels:


<Doug Klippert@ 3:10 AM

Comments: Post a Comment


  Sunday, October 25, 2009 – Permalink –

Export Formatted Sheets

Access to Excel


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.

In Office 2007-10 it's External Data>Excel




[Edited entry from 10/10/2006]




See all Topics

Labels: ,


<Doug Klippert@ 3:05 AM

Comments: Post a Comment


  Tuesday, October 20, 2009 – Permalink –

Display Data Once

Report Trick


Sometimes, you may have data that needlessly clutters a report.

For instance, suppose your report is listing the fields strCompany, strFirstName, and strLastName.

If there are multiple names listed for each company, and the report is sorted by company name, repeating the company information is unnecessary.

You may want to set a group header based on the company name, but there's an easier way to hide the redundant data.
  1. Open the report in Design view.

  2. Select the control that displays repetitive information.

  3. Display the control's Property sheet.

  4. Set the Hide Duplicates property equal to No.

  5. Finally, Save and preview your report.


If the data in the modified control is the same as the data from the previous record, the control is hidden.


 [Edited entry from 10/2/2006]


See all Topics

Labels:


<Doug Klippert@ 3:34 AM

Comments: Post a Comment


  Wednesday, October 14, 2009 – Permalink –

Access to E-mail

What's the Outlook?


Garry Robinson from GR-FX Pty Limited of Australia and Scott McManus from Skandus, have a tutorial about:

Processing E-Mail Orders using Outlook and Access.

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."

[Edited Entry from 9/24/2006]




See all Topics

Labels: ,


<Doug Klippert@ 3:02 AM

Comments: Post a Comment


  Thursday, October 08, 2009 – Permalink –

Copy Paste

Excel tables


One way to create a new table in an Access database from information included in an Excel spreadsheet is to select the pertinent data on the spreadsheet, including the field names.

Copy the selection (Edit>Copy, or CTRL+C)

Switch back to Access .

With Tables objects being shown in the database window, choose Edit>Paste, or use the CTRL+V shortcut.

Access will ask if the first row contains the field names and then will paste the information as a new database table.

[Edited entry from 9/16/2006]




See all Topics

Labels: ,


<Doug Klippert@ 3:44 AM

Comments: Post a Comment


  Saturday, September 26, 2009 – Permalink –

Reduce Trips to the Toolbox

Save shoe leather


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.



[Edited entry from 8/31/2006]




See all Topics

Labels:


<Doug Klippert@ 3:07 AM

Comments: Post a Comment


  Saturday, September 19, 2009 – Permalink –

Disable Confirmation

Only for the brave


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



Good luck, you've been warned.



[Edited entry from 8/22/2006]




See all Topics

Labels:


<Doug Klippert@ 3:58 AM

Comments: Post a Comment


  Saturday, September 12, 2009 – Permalink –

Declaring Multiple Variables

Declare each one


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



[Edited entry from 8/14/2006]




See all Topics

Labels: , , ,


<Doug Klippert@ 3:52 AM

Comments: Post a Comment


  Friday, September 11, 2009 – Permalink –

AutoShapes

Drawing bar objects



Kim Hedrich has put together a series of basic articles on AutoShapes for TechTrax.

AutoShapesPart 1 - How to draw circles, ovals, squares and rectangles; also modifying fill and line colour

AutoShapes Part 2 - Fill Effects

AutoShapes Part 3 - Shadows and 3-D

AutoShapes - Text Inside a Shape




[Edited entry from 8/13/2006]




See all Topics

Labels: , , ,


<Doug Klippert@ 3:09 AM

Comments: Post a Comment


  Friday, August 28, 2009 – Permalink –

Parameter v. Form

You have a choice



There are a couple of ways to limit the data that is displayed in a Query or in a report.
One is a Parameter Query
  1. Create a query to use as the RecordSource of your report.
  2. In query design view, in the Criteria row under your date field, enter:

    Between [StartDate] And [EndDate]
The other is to create a specific form. The unbound form has the following advantages:
  • Flexible: user does not have to limit report to from and to dates.
  • Better interface: allows defaults and other mechanisms for choosing dates.
  • Validation: can verify the date entries.
Allen Browne has supplied a clear explanation along with some typical code.

Also see FontStuff.com:
Using Parameter Queries


[Edited entry from 7/29/2006]


See all Topics

Labels:


<Doug Klippert@ 3:00 AM

Comments: Post a Comment


  Saturday, August 22, 2009 – Permalink –

Self Help

Get started in the right direction


The Office of Technology Services of Towson University, located in Towson, Md., provides Self-Help Training Documents for many applications.

They are available for many levels of knowledge. They’re clean, clear, and concise.
  • Access

  • Adobe Acrobat

  • Dreamweaver

  • Excel

  • FrontPage

  • Microsoft Office Tools

  • Outlook

  • Outlook Web Access

  • PowerPoint

  • Publisher

  • Visio

  • Windows

  • Word Art

  • Word
Tech Docs



[Edited entry from 7/21/2006]



See all Topics

Labels: , , , , , , , ,


<Doug Klippert@ 3:03 AM

Comments: Post a Comment


  Sunday, August 16, 2009 – Permalink –

Change Keyboard Navigation

It's your choice


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.




[Edited entry from 7/13/2006]




See all Topics

Labels:


<Doug Klippert@ 3:15 AM

Comments: Post a Comment


  Monday, August 10, 2009 – Permalink –

Military Clipart

Thousands of items


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."


HqDA.Army.Mil - Clipart


[Edited entry from 7/7/2006]




See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:59 AM

Comments: Post a Comment


  Saturday, August 08, 2009 – Permalink –

Reduce Entry Mistakes

Disable AutoExpand


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.

(Works the same in Access 2007)




[Edited entry from 7/5/2006]




See all Topics

Labels:


<Doug Klippert@ 3:04 AM

Comments: Post a Comment


  Saturday, August 01, 2009 – Permalink –

Data Security

Access style


With all the news about database spying, here's some information about protecting your own Access db.
Don't tell the New York Times.

This is Margaret Blauvelt's TOC from Mendoclick.com:
  • General Protection from Unauthorized Users
  • Protect your Logon Identity
  • How to Change your Password in Access
  • User Logon, Security and Data Integrity
  • The Access Workgroup Security File
  • Object Permissions and User Logon
  • Access Sessions and the Workgroup File
  • Joining to another Workgroup Security File from within Access
  • Opening a Database from the Command Line
  • Opening a Database from a Shortcut
  • Backup
  • Compact and Repair
Mendoclick.com
Access Security for End Users

Support.microsoft.com:
Frequently asked questions about Access security warnings

For Access 2007:
Data Security in Microsoft Access 2007



[Edited entry from 6/27/2006]


See all Topics

Labels:


<Doug Klippert@ 3:09 AM

Comments: Post a Comment


  Sunday, July 26, 2009 – Permalink –

Fiscal Year in Access

Make up your own year


You can show a custom Fiscal Year starting June, 1 and ending May 31.


BeginFiscalYr = DateSerial(Year(Date), 6, 1)


EndFiscalYr = DateSerial(Year(Date) + 1, 6, 1) - 1


Also:
Calculating a future or past date in Access


[Edited entry from 6/19/2006]




See all Topics

Labels:


<Doug Klippert@ 3:26 AM

Comments: Post a Comment


  Saturday, July 18, 2009 – Permalink –

Auto Form

Just add Data


Highlight a Table in the Objects Table view.

Go to the main menu bar and click on Insert.

Towards the bottom of the menu, you'll find an entry for
"AutoForm"

Click on it and Access will create a data entry form based on the selected table.

When you close the AutoForm, you will be asked if you want to save changes.
Click Yes.

(In 2007 go to Create>Form)

The default name will be the name of the table. If you are using Hungarian notation, change the name from

"tblCustomers" to "frmCustomers"

and click OK.


Also see:
Hungarian Notation

[Edited entry from 6/9/2006]




See all Topics

Labels:


<Doug Klippert@ 3:46 AM

Comments: Post a Comment


  Thursday, July 16, 2009 – Permalink –

Access-Excel-XML-HTML

Transfer data


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.


An introduction to Excel and XML data files

 
Also:
Some nice photos and calendar layout:
Monthly calendar with photos


[Edited entry from 6/8/2006]




See all Topics

Labels: , ,


<Doug Klippert@ 5:43 AM

Comments: Post a Comment


  Wednesday, July 15, 2009 – Permalink –

Restore Defaults

Office 2003 redo


To reset the original settings in Office 2003, follow these steps.
Make sure that you back up your files before you follow these steps.
  1. 1. Start any Office 2003 program.
  2. On Help menu, click Detect and Repair.



  3. Click to select the Discard my customized settings and restore default settings check box, and then click Start.
  4. Quit the application, and then click Ignore.
  5. Click OK when you receive the following message:
    Reset of setting to default succeed.

Microsoft Office Diagnostics in 2007 replaces Diagnose and Repair:

Howtogeek.com


[Edited entry from 6/7/2006]


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:22 AM

Comments: Post a Comment


  Sunday, July 12, 2009 – Permalink –

Plain Numbers

I'd Like to Make It Clear


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. Common problems 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. PlainFigures.com [Edited entry from 6/4/2006] See all Topics

Labels: , , ,


<Doug Klippert@ 3:59 AM

Comments: Post a Comment


  Saturday, July 11, 2009 – Permalink –

A Hoard of Tips

Good for all levels of user


Allen Browne's tips for Access users

Here is a list of the sections on this site; something for everyone.
  • 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.

[Edited entry from 6/3/2006] See all Topics

Labels:


<Doug Klippert@ 3:17 AM

Comments: Post a Comment


  Wednesday, July 08, 2009 – Permalink –

Polyglot Polynomials

ToolPak Translator


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."


Analysis ToolPak Translator


[Edited entry from 5/31/2006]




See all Topics

Labels: ,


<Doug Klippert@ 3:59 AM

Comments: Post a Comment


  Monday, June 29, 2009 – Permalink –

Access Tools for Free or Fee

Shareware/Freeware


Peter De Baets has some for pay and some for free downloads at Peter's Software.

In the past I have used his Shrinker-Stretcher

ShrinkerStretcher will automatically rescale/resize your MS Access forms, controls, and fonts to fit any screen resolution, desktop scheme, or font setting.

Using ShrinkerStretcher means you can develop applications without concern for your end user's display settings! Scale your 800x600 application down to 640x480, or to any other screen resolution!

For free includes:
ChromaForm
Let your users set their own form color schemes.
Custom Time Functions -
Perform mathematical operations on custom time values that include fractions of a second.
TableLinker
Manage linked tables from multiple databases, ODBC sources, or any combination of the two. Easily refresh table links or connect to tables in a different data environment.


[Edited entry from 5/14/2006]




See all Topics

Labels:


<Doug Klippert@ 3:35 AM

Comments: Post a Comment


  Thursday, June 25, 2009 – Permalink –

Compact Before Synchronizing

A DB two step


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.


[Edited entry from 5/10/2006]




See all Topics

Labels:


<Doug Klippert@ 3:26 AM

Comments: Post a Comment


  Tuesday, June 23, 2009 – Permalink –

Mail Your MDB-ACDB

Don't let Outlook stop you


There was a lot of frustration with Outlook 200x. If an Access MDB file is received, Outlook expects the worst and blocks the attachment.

The trick has been to ZIP the file or change the extension to something like .DAT.

Here's a registry trick that restores Outlook to the good old days.
Outlook 2007, 2003, 2002 and Outlook 2000 SP3 allow the user to use a registry key to open up access to blocked attachments. (Always make a backup before editing the registry.) To use this key:
  1. Run Regedit, and go to this key:

    HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Outlook\Security 

    (change 10.0 to 9.0 for Outlook 2000 SP3,11.0 for Outlook 2003, 12.0 for Outlook 2007)
  2. Under that key, add a new string value named Level1Remove.

  3. For the value for Level1Remove, enter a semicolon-delimited list of file extensions. For example, entering this:

    .mdb;.url

    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.
Slipstick.com:

Opening .exe Attachments

Also see:
Shortcuts for Sending Access Objects via Email

[Edited entry from 5/8/2006]


See all Topics

Labels: ,


<Doug Klippert@ 3:27 AM

Comments: Post a Comment


  Sunday, June 21, 2009 – Permalink –

Clip Art at Home

Install more


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:
  1. Exit all programs that are running

  2. 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)

  3. 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)
Support.Microsoft.com
How to add clip art to Clip Organizer in a 2007 Office system and in Office 2003



[Edited entry from 5/5/5006]



See all Topics

Labels: , , , , , , ,


<Doug Klippert@ 3:53 AM

Comments: Post a Comment


  Wednesday, June 17, 2009 – Permalink –

VBA Variable Problems

Explicit protection


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.

  1. Open a module (start the VBA Editor)

  2. Choose Tools>Options from the menu bar

  3. On the Editor tab of the Options dialog box, select the Require Variable Declaration check box in the Code Settings panel

  4. Finally, click OK




[Edited entry from 5/2/2006]



See all Topics

Labels: , , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment


  Wednesday, June 10, 2009 – Permalink –

Automaticaly Setup a Database

Link in a snap


When you're creating a new database that will link to data that isn't in an Access format, you can speed up the setup process.

Rather than creating a new database and then using the File>Get External Data>Link Tables,

  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
Access will automatically create an MDB file with the same name as the data source you selected and will set up links to the data.



[Edited entry from 4/24/2006]


See all Topics

Labels:


<Doug Klippert@ 3:53 AM

Comments: Post a Comment


  Thursday, June 04, 2009 – Permalink –

Database Examples

Clever timesavers


Roger Carlson, RogerCarlson.com, has collected a group of Access databases that demonstrate different functions.

The Library includes tutorials and a good list of files that can be downloaded.

See the Table of Contents
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

And many more

[Edited entry from 4/16/2006]




See all Topics

Labels:


<Doug Klippert@ 3:32 AM

Comments: Post a Comment


  Saturday, May 23, 2009 – Permalink –

Compact-Repair Shortcut

Desktop convenience


You can make compacting and repairing databases easier by providing a desktop shortcut.

Right-click on the Windows desktop and select New>Shortcut from the shortcut menu. Then, set up a Command Line entry in the form:

"Path to Access.exe" "Path to Database.mdb" /compact

For example, to create a shortcut to compact Northwind, you might use:

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" /compact

Click Next and continue through the shortcut setup wizard, naming the shortcut appropriately.

In Access 2000+, the database is both compacted and repaired when the /compact switch is applied.

Access 97 executes these processes separately, so Access 97 shortcuts should use a Command Line in the form:

"Path to Access" "Path to Database" /compact /repair

Also note that you can compact to a different location by specifying a target database name after the /compact switch.

If you omit a target file name following the /compact option, the file is compacted to the original name and folder. To compact to a different name, specify a target file.

If you don't include a path in target database or target Access project, the target file is created in your My Documents folder by default.

(Even though a shortcut will open the database without it, in order for the command line flag to work, you MUST include the path to the executable - Access.exe)


[Edited entry from 4/1/2006]




See all Topics

Labels:


<Doug Klippert@ 3:29 AM

Comments:
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.
 
Post a Comment


  Sunday, May 17, 2009 – Permalink –

New Fields from Datasheet View

Table fields on the fly



We all, of course, pre-plan our table layouts. However, if you need to create a table field while working in Datasheet view, you don't have to switch to Design view.

There's a quick way to create a field from Datasheet view.

Right-click on the column heading of the column you want to the right of your new field.
Choose Insert Column from the shortcut menu.

Access creates a field with a name such as "Field1".

You can then immediately start entering data in. You can also use the shortcut menu to rename or delete the newly created field.

Later you can go to Design view to set the field's properties.



[Edited entry from 3/24/2006]




See all Topics

Labels:


<Doug Klippert@ 3:17 AM

Comments: Post a Comment


  Sunday, May 10, 2009 – Permalink –

Shortcuts to Access Objects

Quick way in


If you often work with a certain Access objects (specific forms, queries, etc.) in a database, you can create a shortcut to it on your desktop.

Click on the Object and drag it to the desktop..


Access will create the shortcut on your desktop, or another location.



[Edited entry from 3/15/2006]




See all Topics

Labels:


<Doug Klippert@ 3:10 AM

Comments: Post a Comment


  Wednesday, May 06, 2009 – Permalink –

Who was that font I saw you with last night?

That was no font, that was my typeface


You can find the Fonts supplied with some Microsoft products
Select a product name from the list to get a list of fonts supplied with that product.

Microsoft's Typography is an interesting site to poke around in.

Here are some books I use for reference material:

Words into Type

by Marjorie E. Skillin, Robert Malcolm Gay ISBN 0139642625


Stop Stealing Sheep & Find Out How Type Works


by Erik Spiekermann, E.M Ginger ISBN 0201703394


The Elements of Typographic Style

by Robert Bringhurst ISBN 0881791326

A font can be defined as a collection of characters with the same style and size. A typeface is the design of the characters regardless of size or style. The terms are used interchangeably today.


[Edited entry from 3/11/2006]




See all Topics

Labels: , , , , , ,


<Doug Klippert@ 3:06 AM

Comments: Post a Comment


  Sunday, May 03, 2009 – Permalink –

Crosstab Query Column Headings

Using Month Numbers


If you display a crosstab query as a datasheet, consider using a month's or day's number as a column heading instead of a text abbreviation (e.g., 1 instead of Jan or January, or 2 instead of Mon).

Text abbreviations are sorted alphabetically. Apr appears before Feb, Mon appears before Sun, etc. Number representations will sort in their proper order.


[Edited entry from 3/8/2006]




See all Topics

Labels:


<Doug Klippert@ 3:01 AM

Comments: Post a Comment


  Sunday, April 26, 2009 – Permalink –

Composite Keys

Multi-Field Keys


To quickly create a multi-field primary key, while in the table's Design View:
  1. Hold the Ctrl key and click each field that you want to make up the primary key.
  2. Choose Edit>Primary Key from the menu bar, or click the Primary Key button on the toolbar
Access creates the composite primary key for you.

University of Texas at Austin:
Primary and Foreign Keys


[Edited entry from 2/28/2006]

See all Topics

Labels:


<Doug Klippert@ 3:03 AM

Comments: Post a Comment


  Saturday, April 18, 2009 – Permalink –

Duplicate Table Structures

No need to reinvent


When setting up a database, you may find that there is a table structure in another database that would be appropriate. You could Import the table, but then you have to clear out the useless records.

Here's an easier way to do it:
  1. Choose File>Get External Data>Import from the menu bar
    (External Data tab in 2007, then select Access in the Import group)
  2. Select the database that contains the tables you want to copy and click the Import button.
  3. Select the appropriate tables on the Tables sheet
  4. click the Options button, select the Definition Only option and click OK
.

To export a table definition from your current database to another:
  1. Select the appropriate table in the Database window.
  2. In Access 2000 and above, choose File>Export.
    (If you're using Access 2007, choose More in the Export group)
  3. Click OK.
  4. Now, regardless of version, select the target database and press Enter
When the Export dialog box appears, select the Definition Only option button and click OK. See all

[Edited entry from 2/20/2006]

Topics

Labels:


<Doug Klippert@ 3:35 AM

Comments: Post a Comment


  Friday, April 10, 2009 – Permalink –

Business Rules!!! ... Rules

Bean Counting Models


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."


Database Answers

Barry Williams - Founder and Principal Consultant.


If you're attempting a "Big Year" list of blogs, you can add Viewpoint of a Database Analyst: A Database Design Principles Blog

[Edited entry from 2/12/2006]




See all Topics

Labels:


<Doug Klippert@ 3:47 AM

Comments: Post a Comment


  Thursday, April 02, 2009 – Permalink –

Relationships

How it all ties together



"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."


Relationships: Your Key to Data Integrity in Access 2003

An article from Informit.com and Alison Balter.

Alison is the founder of InfoTechnology Partners, Inc., a computer consulting firm in California. She is a highly experienced trainer and consultant, specializing in Windows applications.



[Edited entry from 2/4/2006]




See all Topics

Labels:


<Doug Klippert@ 3:24 AM

Comments: Post a Comment


  Monday, March 30, 2009 – Permalink –

Linked Table Problems

Lost functionality


Have you found that you cannot update linked Excel tables in Access 2003?


If you have installed Microsoft Office 2003 Service Pack 2 (SP2):


MORE INFORMATION
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.


Support.Microsft.com:
You cannot change data in linked tables that are to an Excel workbook

You might try reinstalling and then only installing SP1.


Also:

VB123.com:
Guacamole dipped - Access to Excel linked table gotcha

PCWorld.com:
Patent ruling costs Microsoft $8.9 million


"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.



[Edited entry from 2/1/2006]




See all Topics

Labels: ,


<Doug Klippert@ 3:14 AM

Comments: Post a Comment


  Wednesday, March 25, 2009 – Permalink –

Toggle Object Views

Use the keyboard


When you are putting a database together you often want to switch between views of Access objects to see the changes.

For instance, you'll switch the view to examine a Table in Design view and then back to data view. It is can be faster to switch between views using keyboard shortcuts, rather than the View menu.

You can cycle through the views of an open object using the

Ctrl + .

or

Ctrl + ,

shortcut keys.
These shortcuts can be used with tables, queries, forms, reports, and data access pages.


[Edited entry from 1/27/2006]




See all Topics

Labels:


<Doug Klippert@ 3:26 AM

Comments: Post a Comment


  Monday, March 16, 2009 – Permalink –

New Default Access Form Template

Set new standards



You can change Access's default form.

To set up a custom template,
  1. Create your template form by specifying all the properties you want to maintain from form to form.
  2. Then, save the form using any name.
  3. Next, select Tools>Options from the menu bar and select the Forms/Reports tab.
  4. 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)

Also: MeadinKent.co.uk: Making a report template

Here's a calendar from MeadinKent: Calendar page



[Edited entry from 1/19/2006]


See all Topics

Labels:


<Doug Klippert@ 3:05 AM

Comments: Post a Comment


  Monday, March 09, 2009 – Permalink –

Change Properties in Form View

Alterations on the run


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.


[Edited entry from 1/10/2006]




See all Topics

Labels:


<Doug Klippert@ 3:47 AM

Comments: Post a Comment


  Saturday, February 28, 2009 – Permalink –

Parameter Queries Deux

Another look at parameters

The ability to use dynamic criteria in a Query makes Access even more valuable.

David Badurina operates NlueMooseTech.com.

He has produced an Access Parameter Query Tutorial video that walks the viewer gently through the process.

The site also has information on Excel and Word.

(Parameter queries are also referenced here:
Parameter v. Form)

How to create a parameter query

Using Parameter Queries

[Edited entry from 2/26/2005]




See all Topics

Labels:


<Doug Klippert@ 3:21 AM

Comments: Post a Comment


  Friday, February 20, 2009 – Permalink –

Access on Line

Free Basic Instruction


Microsoft has developed a very good collection of tutorials and samples for Office applications. They're located at Office.Microsoft.com

Even if you haven't upgraded to 2007, these suggestions will work with most earlier versions.

For Access look at:
Access 2007

If you have avoided Access in the past, a least glance at "Getting started with Access and Database applications"

Also:

Access 2003 Training Modules

These are 30 to 55 minute demonstrations.

"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."





[Edited entry from 12/17/2005]




See all Topics

Labels:


<Doug Klippert@ 3:13 AM

Comments: Post a Comment


  Friday, February 13, 2009 – Permalink –

Data Modeling

Data bits and pieces

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."

University of Texas at Austin
Introduction to Data Modeling

Finding the Perfect Fit
By Tim McLellan

AgileData.org:
Data Modeling 101

DataModel.org


[Edited entry from 12/9/2005]




See all Topics

Labels:


<Doug Klippert@ 3:44 AM

Comments: Post a Comment


  Thursday, February 05, 2009 – Permalink –

Other Right Clicks

A couple overlooked


Right-clicking on objects, such as Tables, Reports or Queries, gives you the opportunity to print, copy, and work with them.

There are other options revealed by right-clicking on the Database window itself.
Right-click on the empty white space of the Database window can lead to Relationships.



If you right-click on the edge of the window, you'll find a link to Access database properties, startup activity for the current database, or open a new database.

Right-clicking on the Database window also lets you configure the items in the Groups bar.

[Edited entry from 12/1/2005]




See all Topics

Labels:


<Doug Klippert@ 3:58 AM

Comments: Post a Comment


  Thursday, January 29, 2009 – Permalink –

Sort Forms

Create a sorting function

Here is one way to provide your users with a means to reorder fields in forms.

"Chris Weber develops a solution that allows users to sort the data in their forms (or subforms) that you can add to your application easily.

  • The sorting interface should be generic to any form in any database.

  • It should also work with subforms.

  • It needs to be implemented as expediently as possible to fulfill the bid, and be easily maintained in the future.

  • It should be intuitive or reminiscent of other interfaces in Access and Windows.
Let Your Users Sort it Out



Scroll down to the bottom of the page. There is a file that can be downloaded with the code.

[Edited entry from 11/23/2005]

See all Topics

Labels:


<Doug Klippert@ 3:01 AM

Comments: Post a Comment


  Sunday, January 18, 2009 – Permalink –

Normalization

Designing an efficient database

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.


Here are a few of the other topic headings:

Understanding Your Data
  • 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
Database Normalization Tips By Luke Chung, Founder and CEO of FMS Inc
Also see: Knowledgebase #283878: Description of the database normalization basics
15seconds.com: Introduction to Relational Databases
and:
Normalization Model

[Edited entry from 11/15/2005]

See all Topics

Labels:


<Doug Klippert@ 3:52 AM

Comments: Post a Comment


  Tuesday, January 06, 2009 – Permalink –

Clip Art Gallery

Sprinkle carefully


Judicious use of Clip art can spice up a document. Here's an article about how to customize existing pictures including:
  • Display clip-related toolbars
  • Customizing your clip art
  • Cropping
  • Sizing
  • Adding text wrapping
  • Blurring
  • Rotating and flipping
  • Adding a drop shadow
Edit clip art in Word

Also:
Clip art gallery

 

Halloween clips
Clip Art demo
5 new things about the Clip Art and Media site
Mary Sauer's Design Gallery Help
Microsoft Clip Art & Media Help

[Edited entry from 10/12/2005]

 See all Topics

Labels: , , ,


<Doug Klippert@ 3:15 AM

Comments: Post a Comment


  Monday, December 29, 2008 – Permalink –

Drag Data

Simple exchange


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.
  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
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.

[Edited entry from 10/21/2005]


 See all Topics

Labels:


<Doug Klippert@ 3:55 AM

Comments: Post a Comment


  Thursday, December 18, 2008 – Permalink –

Source of Tables

Identify a linked Access table's source



When a database contains linked tables, you may need to find the location of the source file.
One way is to:
  1. Open the table in Design view

  2. Clicking Yes when Access informs you that some properties can not be modified.

  3. Right-click on the window's title bar

  4. Select Properties from the shortcut menu.

  5. The location will appear in the Description text box in the Table Properties dialog box.


(Use Shift+F2 to open the Zoom box, if necessary)



ComputerBooksOnline


[edited entry from 10/16/2005]



See all Topics

Labels:


<Doug Klippert@ 3:37 AM

Comments: Post a Comment


  Thursday, December 11, 2008 – Permalink –

Alphabetize by One Field or the Other

If one is missing, use the other


Let's say you have a database that has the company name and a contacts name.

In some cases the CompanyName field is empty. If that happens, you want to continue the alpha sort using the contact's LastName.

To do this, you need to create an extra query field to provide the sort, using the NZ() function to replace the contents of one field for null values in another.

(Nz(variant, [valueifnull])

  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:






[Edited entry from 10/5/2005]



See all Topics

Labels:


<Doug Klippert@ 3:43 AM

Comments: Post a Comment


  Tuesday, December 02, 2008 – Permalink –

Text Box Highlights

Change background


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.

Also:

ComputerBooksOnline:
Field highlighting solutions

[updated entry from 9/18/2004]



See all Topics

Labels:


<Doug Klippert@ 6:53 AM

Comments: Post a Comment


  Wednesday, November 26, 2008 – Permalink –

Access Forum

Ask your peers


Here is a forum populated by Access users and developers around the world.

Just a few of the recent inquiries:


  • 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



      Access World Forums


      [Edited entry from 9/9/2005]



      See all Topics

    Labels:


    <Doug Klippert@ 6:54 AM

    Comments: Post a Comment


      Friday, November 21, 2008 – Permalink –

    An Access Primer

    No cost lessons


    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."

    FunctionX.com Tutorials


    [Edited entry from 9/2/2005]



    See all Topics

    Labels:


    <Doug Klippert@ 6:43 AM

    Comments: Post a Comment


      Sunday, November 16, 2008 – Permalink –

    Keyboard Navigation

    Change directions


    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.




    Access Options.Advanced for 2007:



    [Edited entry from 8/27/2005]



    See all Topics

    Labels:


    <Doug Klippert@ 3:10 AM

    Comments: Post a Comment


      Friday, November 07, 2008 – Permalink –

    Null, Nothing, Nada

    Empty entries


    "An example might be fax numbers in a customer database. If you store a Null, it means you don't know whether the customer has a fax number.

    If you store a zero-length string, you know the customer has no fax number.
    Access gives you the flexibility to deal with both types of 'empty' values."


    Nulls and Zero-Length Strings

    From John L. Viescas at Viescas.com


    Also:
    Make Null Zero

    [Edited entry from 8/16/2005]




    See all Topics

    Labels:


    <Doug Klippert@ 4:08 AM

    Comments: Post a Comment


      Tuesday, November 04, 2008 – Permalink –

    Too Many Slices

    More data than the pie will hold


    A pie chart displays the per cent of the whole is represented by the component elements. Four salesmen, four slices of pie.

    The problem arises when there are 10 or so components that vary in size. The labels begin to overlap and the chart is difficult to read:



    One suggestion that Chris Weber offers is to rearrange the order of the slices:



    The article uses MS Graph in Access, but the techniques are applicable in all the other applications that can use graphs.

    SmartAccess:
    Easy as Pie. . .

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

    (A downloadable example file is also provided


    [Edited entry from 8/8/2005]



    See all Topics

    Labels: , , ,


    <Doug Klippert@ 3:38 AM

    Comments: Post a Comment


      Tuesday, October 28, 2008 – Permalink –

    Normalization Model

    Excel Mock-up


    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."




    Design Access databases with normal forms and Excel

    Also see:

    Database normalization Webcast

    An Introduction to Database Normalization

    Database normalization basics

    Database Normalization Basics for Developers

    Also see:

    Tushar-Mehta.com:
    Building and using a relational database in Excel

    [Edited entry from 8/2/2005]



    See all Topics

    Labels:


    <Doug Klippert@ 3:43 AM

    Comments: Post a Comment


      Wednesday, October 22, 2008 – Permalink –

    Input Characters

    Change data display


    Character
    Description

    0
    Digit (0 through 9 entry required; plus [+] and minus [-] signs not allowed).

    9
    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).

    L
    Letter (A through Z, entry required).

    ?
    Letter (A through Z, entry not required).

    A
    Letter or digit (entry required).

    a
    Letter or digit (entry not required).

    &
    Any character or a space (entry required).

    C
    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.)

    "Literal"
    You can also enclose any literal string in double quotation marks.

    Password
    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"

    See:
    How to Replace the Default Input Mask Error Message

    Also see:
    Using an input mask to restrict data

    Hidden Passwords


    [Edited entry from 7/20/2005]



    See all Topics

    Labels:


    <Doug Klippert@ 3:03 AM

    Comments: Post a Comment