Do you frequently copy Access data into your word processing documents? Access makes it easy to export a table to a Word document, and this is how we do it:

1. In the Database Window, select the Table or Query whose data you want to export.

2. Click the Office Links button and select Publish It With Microsoft Word from the drop-down list.

Access publishes the report as a Word table in a new rich text-formatted (.rtf) document that users can open in any word processing program. They can then format the table using their word processor's formatting tools, and copy it to their main document.

You can reduce typing time by basing your Access forms on AutoLookup queries. For example, let's say you need to develop an Order form that includes fields from both the Customers table and Orders table. Rather than creating the form from these tables, you can base the form on an AutoLookup query that will automatically fill in the customer information once the CustomerID is entered, and this is how we do it:

1. Create a new query with the Customers and Orders tables.

2. Drag the CustomerID field (that joins both tables) from the Orders table to the query design grid.

3. From the Customers table, drag the fields you want to be filled in by the query, such as Name and Billing Address fields.

4. Add additional fields needed from the Orders table.

5. After running and testing the query, create an Order-entry form based on this query.

When the user enters the CustomerID to enter a new order, Access automatically fills in the data from the Customers table.

Access corruption is not only notorious among developers and IT managers, but it is also one of the most annoying computing disaster areas you're likely to encounter. If you haven't taken steps to prevent Access corruption, here are steps you can take to repair corruption after it occurs.

How do you know an Access database is corrupt?

The typical symptoms of Access database corruption can easily be confused with unrelated problems. When you start seeing strange VBA error messages, then it could be a problem with your code. Access is most often used in smaller organizations where testing budgets are limited, either in terms of time or money. Testing a multi-user application is a lot more complex than running the software on one machine and making sure that it works there. You may get “lucky” and receive an "unrecognizable database format" message, which will immediately let you know that the database has failed.

The cryptic error message

More likely, however, you'll start getting error messages that make no sense. “Out of memory” is not something that you would expect to see on a modern office PC, so that message is suspect. Also look out for any error messages that refer to invalid or missing bookmarks, or objects that cannot be referenced. If you see Nulls throwing up error messages in places where the data doesn't contain any Null entries, then that is also a sign. The golden rule with these weird errors is if the message sounds impossible for the current state of the application, then the database is probably corrupted.

Warning: Database recovery risks

Below are the methods I recommend for recovering a corrupt database, in the order in which I would suggest you try them. NOTE: Each of these methods could cause more damage to your database, so don't work on only one copy! Make a primary backup and several copies and try each stage on a new copy. Label each file with the time and date you last attempted to repair it so that you don't lose my “good” copy of the original, corrupted database. Also, copy the database MDB file to your local hard disk before you start. Most of these methods take awhile for the computer to complete but will be expedited if you work off a local hard disk rather than over a network.

1.    Try to “compact and repair” the database

This is the obvious first step and much of the time it will apparently repair the file. I'd recommend that you don't use the compact and repair utility that is built into Access. Instead, use the JetComp utility that Microsoft provides, which, according to MS support, has some improvements over the built-in utility. You can find more information on JetComp, including where to download it, from the Microsoft Knowledge Base . A word of warning about this approach: The utility will only repair tables. Forms and reports will not be repaired and if one of these has gotten corrupted, you'll have no choice than to rebuild it from scratch. You should, however, be able to copy and paste any VBA code between the old damaged form and your new one. Finally, this utility doesn't always repair all of the data in your tables, although it may do enough to allow you back into the database. If you put the database straight back into production, you could find that it will become corrupted again very quickly, and chances are, you won't be able to recover it a second time.

2. Try importing all data

Create a new, blank database and import all objects from the corrupted database. This will force Access to re-create each object, including tables and indexes, which seem to get corrupted more often than anything else.

3.  Check the last few rows of each table

If you receive repeating error messages when opening a damaged table or when importing its data into a new database, take a look at each row, starting at the bottom. If you see rows containing ““###”” and other gibberish, then this is probably the only point at which the database is corrupted you may have lost a row or two, but you can probably recover everything else. Most of the time, Access won't allow you to select and delete the row because it will bombard you with error messages as soon as this line is even visible on the datasheet. The trick is to move it just off the screen and select every other record. Copy the data and paste it into a new table.

4. Look for corruption relationships

If you're dealing with related tables, then you'll probably have real problems with primary and foreign keys. If you're using AutoNumber fields to generate a unique key for each row, then pasting the data will cause new keys to be created, thus breaking every single relationship and rendering your data useless. In this situation, there is very little you can do other than write an append query to copy each row individually into a new table. Create a temporary field in the new table to store the old key value in and then re-create the relationships using these old values in an update query. You should then be able to run an update on each row to match the new keys, while keeping the integrity of the data by matching the old keys.

If you find that you have damaged data in certain rows and Access won't let you delete the rows, try exporting the table to a comma separated values (CSV) file. Load this into Excel and look for any data that looks like garbage: “###” and “???” are good starting points. If you have the time and patience, then it is possible to clean up an Access table in Excel and then import it back into the database.

5. Check that your primary keys are where you expect them to be

Once repaired a corrupted database with JetComp and found that a certain form that had previously worked was now not allowing any data to be entered or changed. The status bar read “This recordset is not updateable”an error that you would expect to see in certain types of recordsets with SQL joins (relationships) in them. The form in question was drawing data from three separate tables and joining them in a query that was the datasource property of the form. The SQL in the query was identical to before corruption, as was the data and the form. So what was causing this error message when the form had worked without a hitch for months? After several hours of frantic head scratching, I happened upon the cause: The primary key was no longer identified as a primary key in the design of the table. This factor was breaking the relationships in the SQL statement of the query, so the form was falling over as soon as I tried to change the data. Marking the field as a primary key and saving the table design fixed the problem.

Make backup copies

If you haven't yet found the source of corruption, make frequent copies of your database. A little VB program can copy the file over the network to another computer every hour, or even every half hour. Telling your users that they're going to have to reenter all data since the last evening's backup is a surefire way to get lynched before you make it to the door. If you tell them that they've lost the last hour's work, they're likely to be a lot more forgiving.

Corruption is always possible to recover from assuming you take the necessary precautions. Your data is far more valuable than your hardware and should be your number one priority. If bits do start falling off your database, then don't panic; stay calm and start making copies. Chances are high that you'll be able to recover all the data within an hour or two, and then you can start concentrating on preventing it from happening again!

When you need quick answers to everyday business questions, let Access' Crosstab Query Wizard help...

Example, say you need to know the average number of hours each employee works per week. To find the answer, you need to analyze the data in the Hours Worked table, which contains three fields: EmployeeID, Week Ending, and Hours, and this is how we do it:

 
1.    In the database window, click Queries, and click New.

2.    Choose Crosstab Query Wizard, and click OK.

3.    Choose the Hours Worked table, and click Next.

4.    Select EmployeeID for the field values you want for row headings, and click Next.

5.    Select Week Ending for the field values you want for column headings, and click Next.

6.    Select Date to coincide with the date value in the Week Ending field, and click Next.

7.    Select Hours from the Fields list box, choose Avg from the Functions list box, and click Next.

8.    Name the query, and click Finish.

Included in the results of the crosstab query is a column that displays the average number of hours worked by each employee per week.

The Crosstab Query Wizard works with only one table at a time. If you need to create a query with fields from more than one table, first create a select query that includes the necessary fields, and specify that query in step 3.

Data Access pages provide an online viewer that lets you display and edit Access databases over the Internet. It's possible to create a form-type data access page for a single table or query using the Page Wizard.

Example, say your managers need to be able to access their employees' addresses, phone numbers, and e-mail addresses online. They can do just that if you create a form-type Data Access page. Here's how:

1.    Open the Employees database.
2.    Click the Pages object in the database window.
3.    Click New and then select Page Wizard.
4.    Select the Employees table on which to base the object data and click OK.
5.    Select the fields that you want displayed online and click Next.
6.    Click Next twice.
7.    Enter a title for the page, select Open The Page, and click Finish. The new data page is displayed.
8.    Close and Save the Data Access page to a folder that you (and your managers) can access online.

After the Data Page is saved, Access places a shortcut to it in the database window. The actual Data Page exists as an HTML file separate from the Access database to which it is connected.

'

Creating reports can be a laborious process, but it doesn't need to be with this Microsoft Access trick. Discover how to create a report in minutes by using the Simple Query Wizard to find summary information on two or more joined tables.

You can use Microsoft Access' Simple Query Wizard to quickly find summary information on two or more joined tables. For example, suppose at the end of each month you need to create a report summarizing each employee's average, minimum, and maximum hours worked per week for the month of June. You can find the information in two tables, Employees and Hours Worked, which are joined by Employee ID number.

To produce the report in minutes, simply follow these steps.

1. In the database window, click Queries under Objects.

2. Double-click the Create query by using the wizard.

3. Under Tables/Queries, select Employees, and move Employee ID, First Name, and Last Name fields from the Available fields column to the Selected Fields column.

4. Under Tables/Queries, select the Hours Worked table and move the Hours and WeekEnding fields from the Available Fields column to the Selected Fields column.

5. Click Next and select Summary.

6. Click the Summary Options button, then click the Avg, Min, and Max check boxes, and click OK.

7. Click Next, select Month, and click Finish.

If you require additional information or assistance with this item, please give us a call.

Did you know that you can use a select query to summarize the data in your Access table by group? For example, suppose you need to determine the total hours billed by each region using data from your Employee table, and this is how we do it:

1.    Create a select query from your employee database that includes two fields: Location and Hours Worked.

2.    In Design View, click the Totals button on the Query Design toolbar.

3.    Under Hours Worked, click the cell in the Totals Row, and choose Sum from the drop-down list.

When you run the query, the results will show the total number of hours worked by region.

If you need to send the same letter to people listed in two different database tables, you don't have to create two different mailing lists. You can use Microsoft Access's Union query to combine the two tables into one list.

Example:

suppose you want to send the same letter to all your company accounts and the interns working on those accounts. The Customer database contains the name of each company in the Name field. The Intern database contains the name of each Intern in two fields, Last Name and First Name. You want the results of the Union query to combine the Name fields from both databases into one Mailing Name field. The Intern table's Mailing Name field is a combination of the Last Name and First Name fields and the Customer table's Mailing Name field comes from the Name field, renamed Mailing Name.

And this is how we do it::

1. In the Database window, click Queries under Objects and then click New.

2. Double-click the Design View.

3. Click Close.

4. Go to Query | SQL Specific | Union.

5. Enter the following code at the prompt:

SELECT [First Name] & " " & [Last Name] As [Mailing Name], [Address], [City], 

[State], [Zip Code]

FROM Intern

UNION SELECT [Name] As [Mailing Name], [Address], [City], [State], [Zip
  Code]

FROM Customer;

6. Click Run on the Query toolbar.

The Union query results contain both the interns' and companies' names and addresses.

If you require additional information or assistance with this item, please give us a call.

Rather than interrupting the flow of your presentation by having to scroll to and position each area as needed, you could create custom views in Microsoft Access, which you can readily access by clicking on a list in your toolbar, and this is how we do it...

First, you will need to create custom views, and this is how we do it:

1.    Go to and select the worksheet area for your view.

2.    Go to View | Custom Views.

3.    Click the Add button, and then enter a name for the view and click OK.

Repeat these steps for each custom view you want to present. To build the custom toolbar for your presentation, follow these steps:

1.    Go to Tools | Customize.

2.    Click the Toolbar tab and click New.

3.    Enter a name for your toolbar (for example, Presentation).

4.    Click the Commands tab.

5.    Click View under Categories.

6.    Click Custom Views under Commands and drag it to your custom toolbar.

7.    Click Close.

No more importing and exporting data; Don’t need to cut ‘n’ paste. Use the OfficeLinks to open Microsoft Word’s Mail Merge Wizard from within Access, and this is how we do it:

1. From the database window, select the table or query that contains the information you want to merge into your mailing documents.

2. On the Database toolbar, click the drop-down arrow of the OfficeLinks button.

3. Select Merge It With MS Word.

If you’re using Word 2002, Access displays the Microsoft Word Mail Merge Wizard dialog box, which gives you the option of linking your data to an existing Word document or to an entirely new document.

1. Selecting Link Your Data To An Existing Microsoft Word Document displays the standard File

2. Open dialog box from which you can select your document. Access opens the selected document with the Mail Merge toolbar active.

3. Selecting Create A New Document And Then Link The Data To It opens a new document with the Mail Merge toolbar active. You can use the Mail Merge toolbar or the task pane to create and print a merged document or labels.

If you’re using Word 2000, Access opens a new Word document with the Mail Merge toolbar displayed. Select the Mail Merge Helper tool from the toolbar to create and print the merge document or labels.

Working with dates in Access can be confusing. Fortunately, Access offers the Date/Time data type, which greatly simplifies the job of handling dates. In addition, you'll find a number of functions that manipulate date values and components. But even with all the built-in help, solutions are seldom intuitive. Here are a few tips that help you solve some of the most common date problems.

Date data entry shortcuts

You don't always have to enter a literal date value. Access offers a couple of keyboard shortcuts, which are quicker and help eliminate typos. To enter the current date, press [Ctrl] ; (semicolon). If you want to copy a date from the previous record, press [Ctrl] ' (apostrophe). To enter the current time, press [Ctrl] : (colon).

Eliminating date data entry—sometimes

Sometimes a date is the current date, and sometimes it isn't. When a date is usually the current date, you can eliminate entering any value at all. Simply set the field's Default Value property to Now() or Date(). When you enter a new record, the property will enter the system's current date (and time). You don't even need to press [Ctrl] ;.

Now() versus Date()

The Now() function returns the system's current date and time. It's a package deal--you always get both. If all you need is the current date and not the time, use the Date() function instead. On the other hand, if all you want is the current time, use the Time() function. Don't use Now() unless you really mean to store both the date and time.

Why does it matter? A value that includes both a date and time component usually responds differently to expressions. For instance, you might expect the following expression to find dates that fall after December 12, 2006:

WHERE field >#12/12/2006#

However, the resulting recordset will include December 12, 2006, entries when the entry includes a time value. Conversely, Access won't return December 12, 2006, dates if the entry stores just a date value.

Auditing changes by adding an edit date

If you have multiple users updating data, you might want to track who's making changes. You can do so by adding a simple event procedure in the following form to a data entry form's Dirty event:

Private Sub Form_Dirty(Cancel As Integer)

editfield = Now()

End Sub

where editfield represents the field that stores the last edit date.

Access fires the form's Dirty event when the contents of the form changes. This happens even if the user re-enters the same entry. If the user catches the mistake, he or she can press [Esc] to cancel the change. However, the user must then press it a second time to delete the newly inserted edit date before leaving the record. Otherwise, Access will save the edit date.

Calculating Julian dates

A Julian date identifies a date by its numeric rank since January 1 (relative to the current year). This rank tells us how many days fall between a date and January 1 of the same year. For instance, January 1 of any year is 1, February 1 of any year is 32, and so on. Leap years complicate things because dates following February 28 in a leap year aren't the same as those in a non-leap year. To calculate a Julian date, use the following expression:

JulianDate = date – DateSerial(Year(date), 1, 1) + 1

where date is the date or Date/Time field for which you're calculating a Julian value. This expression works for leap and non-leap years.

Date delimiters

When including numeric values in expressions, you don't need to identify the value in any way. However, you must identify dates. The correct character to use when including a literal date value is the pound character (#). For instance, the following expression will not return the number of days between two dates:

TotalDays: 3/3/2007 – 2/1/2007

Instead, it returns -4.9825610363727E-04. That's because Access evaluates the dates mathematically: 3 divided by 3 divided by 2007, subtract 2, and so on. The returned value isn't wrong; it just doesn't reflect your intentions.

Be sure to identify date values so Access can handle them properly in equations, as follows:

TotalDays: #3/3/2007# – #2/1/2007#

When you identify the values as dates using the # delimiter, Access returns the value 30--the number of days between February 1, 2007, and March 3, 2007.

Using comparison operators to span a date range

Finding all the records with a specific date is easy--simply enter the date in question. But finding a subset of dates that fall between two dates requires the following conditional operators: <, <=, >, and >=. For instance, the SQL expression for locating all date values earlier than a specific date is:

WHERE datefield < #date#

If you're using the QBE gird, you'd enter the simpler expression into datefield's Criteria cell:

< #date#

Perhaps you need a smaller subset, such as all the dates before 2006 but after 2002. In that case, you'd specify both dates as follows:

WHERE datefield > #12/31/2002# AND datefield < #1/1/2006#

Alternatively, use the simpler expression via the QBE grid:

> #12/31/2002# AND datefield < #1/1/2006#

Both expressions return only those dates that fall in 2003, 2004, and 2005.

Using Format() to display dates

Most of the time, you'll want to display dates in a specific format without storing that format as part of the date. The Format() function allows you to display individual and combination date components without affecting the stored date value. To display a single component, use the appropriate format code. For instance, the following expression would return only the day of the month as an integer (1 through 31):

Format(datefield, "d")

If you want a leading zero, use dd instead of d. The table below contains more individual component formats

When using Format(), remember that the resulting value is a string. You'll use Format() to display dates, but don't use it when you must use the results in mathematical equations.

Sorting by date components

Sorting by dates is simple. You just set the date field's Sort option or add an ORDER BY clause to the SQL statement. However, Access will use the entire date entry. If you're trying to sort by a single date component--the day of the month, the month, or the year--use the Day(), Month(), or Year() functions. For instance, if you wanted to find all the January dates, you'd use the SQL

WHERE clause in the form:

WHERE Month(datefield) = 1

To enter the equivalent expression in the QBE grid, you'd enter the following expression as datefield's criteria:

Month(datefield) = 1

Returning the first or last day of the month

None of the Access date functions returns the first or the last day of a specific or relative month. To do both, you'll need custom expressions. To return the first and last day of the current and relative months, use the following expressions:

Current month

FirstDay = DateSerial(Year(date), Month(date), 1)

LastDay = DateSerial(Year(date), Month(date) + 1, 0)

Previous month

FirstDay = DateSerial(Year(date), Month(date) - 1, 1)

LastDay = DateSerial(Year(date), Month(date), 0)

Next month

FirstDay = DateSerial(Year(date), Month(date) + 1, 1)

LastDay = DateSerial(Year(date), Month(date) + 2, 0)

If you require additional information or assistance with this item, please give us a call.