Liren Large Software Subsidy 6/06.iso/a/a560/8.ddi/PSSKB.TX$/PSSKB.bin

Text File | 1993-01-03 | 22.1 KB | 590 lines

----------------------------------------------------------------------
                 ANSWERS TO COMMONLY ASKED QUESTIONS
----------------------------------------------------------------------

TABLE OF CONTENTS

1   What other data formats does Microsoft Access support?
2   How many databases can I open at once?
3   What are primary and foreign keys in a relational database?
4   How can I change the starting value of a Counter field?
5   How can I create calculated fields in tables?
6   Is data stored in a sorted order in a table? How can I view
    my data in sorted order?
7   Can I join tables from different databases in one query?
8   How can I check for Null fields using a query?
9   What is the difference between DISTINCT and DISTINCTROW?
10  How do I invoke my Access Basic code from within a form?
11  Do form rules override table rules?
12  When do I use the ! operator and when do I use the . (dot) operator?
13  How can I prevent a field from being included in the tab order?
14  Why doesn't the header I created show in my form on the screen?
15  When are validation rules on a form evaluated?
16  How can I ensure that a field on a form is not left blank?
17  How can I refer to a control on a subform?
18  How can I change the behavior of the cursor when I tab into a field?
19  How can I convert a form to a report?
20  How can I sort the data in a report on a field from another table?
21  When I print my report, every other page is blank. What causes this?
22  How can I undo changes to the current field or record?
23  How can I analyze the structure of my database?


Question 1

What other data formats does Microsoft Access support?

Answer 1

Microsoft Access can link to dBASE III+, dBASE IV, Paradox version
3.x, and Btrieve data (all including corresponding indexes).

Microsoft Access can import data from FoxPro version 2.x, dBASE III+,
dBASE IV, Paradox version 3.x, Lotus 1-2-3 version 2.x, Lotus 1-2-3
version 3.x, Lotus 1-2-3/W, Microsoft Excel version 2.x and later,
and Btrieve.  It can also import fixed-length and delimited ASCII text.

Microsoft Access also supports the Open Database Connectivity (ODBC)
specification for connectivity to data on database servers.  The
Microsoft SQL Server ODBC driver is the only driver certified for
version 1.0 and is included in your Microsoft Access package.

For more information on data formats supported by Microsoft
Access, see Chapter 4, "Importing, Exporting, and Attaching," in
the Microsoft Access User's Guide.
---------------------------------------------------------------------

Question 2

How many databases can I open at once?

Answer 2

In Microsoft Access, you can open only one database at a time through
the user interface.  You can, however, use the Attach Table command
on the File menu to attach to as many databases as memory allows.
You can also use the Access Basic OpenDatabase function to open several
databases at once (limited only by memory). For more information on
attaching, see Chapter 4, "Importing, Exporting, and Attaching," in
the Microsoft Access User's Guide. For more information on the
OpenDatabase function, search Help for "OpenDatabase."
---------------------------------------------------------------------

Question 3

What are primary and foreign keys in a relational database?

Answer 3

A primary key uniquely identifies each record in a table and is
usually a single field containing data that is unique for each record;
for example, an employee ID number or an order ID number.  A primary
key can also include more than one field.  For example, in a table
that contains information about companies that have more than one
contact, the primary key might include both the Company ID and Contact
Name fields.  Together, the two fields uniquely identify the record.

A foreign key identifies a field or fields in one table that match
the primary key in another table.  For example, the Employee ID
field in the Employees table is the primary key, and the Employee
ID field in the Orders table is a foreign key.

For more information on primary and foreign keys, see Chapter 1,
"Designing a Database," or Chapter 2, "Table Basics," in the
Microsoft Access User's Guide.
---------------------------------------------------------------------

Question 4

How can I change the starting value of a Counter field to a number
other than 1?

Answer 4

One way is to create a new, temporary table with just one field,
a Number field, that has the same name as the Counter field in
the original table. Enter a value in the temporary table.  This
value should be 1 less than the desired starting value for the
Counter field in the original table.  Create an append query to
append the record from the temporary table to the original table.
After you append the record, delete the temporary table, and then
delete the dummy record from the original table.

NOTE:  Don't compact the database before adding the first record
to the original table; if you do, the Counter value will be reset
to start at 1.
---------------------------------------------------------------------

Question 5

How can I create calculated fields in tables?

Answer 5

In general, it helps to think of queries as tables.  You can use
a query whenever you can use a table.  To add a calculated field
to a query, open the query in Design view and enter an expression
in the Field row in the QBE Grid. If you're familiar with Microsoft
SQL Server terminology, using a query in this way is very similar
to creating a VIEW.  Unlike most implementations of views, however,
Microsoft Access views are generally updatable even if they involve
joins from disparate data sources. To learn more about when queries
are updatable, see Chapter 6, "Designing Select Queries," in the
Microsoft Access User's Guide.
---------------------------------------------------------------------

Question 6

Is data stored in a sorted order in a table?  How can I view my
data in sorted order?

Answer 6

No.  Data is stored in the order in which it is entered.  To
view data in sorted order, create a query, add the field you
want to sort on to the QBE grid, and then select Ascending or
Descending in the Sort row for that field. For more information,
see Chapter 5, "Query Basics," in the Microsoft Access User's Guide.
---------------------------------------------------------------------

Question 7

Can I join tables from different databases in one query?

Answer 7

Yes.  You can use the Attach Table command on the File menu to
attach to tables in separate databases, and then create joins
in the Design view of the Query window. For more information
on attaching, see Chapter 4, "Importing, Exporting, and Attaching,"
in the Microsoft Access User's Guide. For information on creating
joins in the Query window, see Chapter 5, "Query Basics."
---------------------------------------------------------------------

Question 8

How can I check for Null fields using a query?

Answer 8

Place the expression "Is Null" in the Criteria row of the QBE grid.
For more information, search Help for "IsNull."
---------------------------------------------------------------------

Question 9

What is the difference between DISTINCT and DISTINCTROW?

Answer 9

DISTINCT is a reserved word in ANSI SQL (as well as Microsoft
Access SQL) and causes a query to return unique data, as opposed
to unique records.  For example, if 10 customers are named Jones,
a query based on the SQL statement "SELECT DISTINCT Name FROM
Customers" returns only one record with Jones in the Name field.
In Microsoft Access queries, you specify DISTINCT by selecting the
Unique Values Only check box in the Query Properties dialog box.

In contrast, DISTINCTROW is unique to Microsoft Access.  It causes
a query to return unique records, not unique values.  For example,
if 10 customers are named Jones, a query based on the SQL statement
"SELECT DISTINCTROW Name FROM Customers" returns all 10 records with
Jones in the Name field.

The major reason for adding the DISTINCTROW reserved word to Microsoft
Access SQL is to support updatable semi-joins, such as one-to-many
joins in which the output fields all come from the table on the "one"
side.  DISTINCTROW is specified by default in Microsoft Access
queries and is ignored in queries in which it has no effect. You
should not delete the DISTINCTROW reserved word from the SQL dialog
box.
---------------------------------------------------------------------

Question 10

How do I invoke my Access Basic code from within a form?

Answer 10

To call a function from a property, open the form in Design view
and type the name of the function in the property sheet as follows:

 =<function_name>()

The equal sign (=) and parentheses are required. You can call an
Access Basic function from form or control properties such as
AfterUpdate and DefaultValue.  You cannot call Sub procedures
from properties.  For more information, search Help for
"properties: calling macros and functions" or see Chapter 7,
"Coding with Forms," in Microsoft Access Basic: An Introduction
to Programming.
---------------------------------------------------------------------

Question 11

Do form rules override table rules?

Answer 11

Yes.  Note, however, that when you create a control on a form
by dragging a field from the field list, the control inherits
the field's ValidationRule property setting, which is defined
in the table's Design view.

For information on setting field properties, see Chapter 3,
"Changing and Customizing Tables," in the Microsoft Access
User's Guide. For information on setting control properties,
see Chapter 9, "Designing Forms."

---------------------------------------------------------------------

Question 12

When is it appropriate to use the ! (exclamation point) operator
versus the .(dot) operator when identifying objects and properties
in an expression such as Forms!Form1.Visible?

Answer 12

A good rule of thumb is to use the ! operator to the left of the
name of anything you create (such as a form or a control on the
form). Use the .(dot) operator to the left of the name of anything
defined by Microsoft Access (such as a property).

For more information on expression syntax, see Appendix C, "Expressions,"
in the Microsoft Access User's Guide.
---------------------------------------------------------------------

Question 13

When I lock a field, I usually don't want the insertion point to
move to that field.  Is there a way to prevent the field from
being included in the tab order?

Answer 13

Two control properties affect whether you can change the value in
a control and whether the control can receive the focus: Locked
and Enabled.  By default, the Locked property is set to No, and
for all controls except unbound object frames, the Enabled property
is set to Yes.

The following table shows all combinations of the different
property settings.

LOCKED           ENABLED           BEHAVIOR

No               Yes               The control can receive the
                                   focus. Data is displayed
                                   normally and can be copied
                                   and changed.

No               No                The control is dimmed and cannot
                                   receive the focus.  However, the
                                   field still appears in the form's
                                   Design view when you choose Tab
                                   Order from the Edit menu.

Yes              No                The control isn't dimmed but you
                                   can't tab into the field.

Yes              Yes               You can tab into the control
                                   but you can't change the value in
                                   the field.

For more information, search Help for "Locked" and "Enabled."
---------------------------------------------------------------------

Question 14

Why doesn't the header I created show in my form on the screen?

Answer 14

A form has two types of headers: form headers and page headers.
Microsoft Access displays form headers both on screen and when
the form is printed, but it displays page headers only when the
form is printed.  The header you created was most likely a page
header.
---------------------------------------------------------------------

Question 15

When are validation rules on a form evaluated?

Answer 15

Microsoft Access validation rules operate at the field level.
A validation rule is evaluated only when data is entered or edited
in a field and the focus moves to another field or record.  If the
field is left unchanged, Microsoft Access doesn't evaluate the
validation rule.  Microsoft Access also validates a field on a form
when you switch views or close the form.

If you want to do record-level validation, you can attach a macro to
the form's BeforeUpdate property. For more information, see Chapter 22,
"Using Macros with Forms," in the Microsoft Access User's Guide.
---------------------------------------------------------------------

Question 16

How can I ensure that a field on a form is not left blank?

Answer 16

There are several ways to ensure that a field on a form isn't left
blank or unchanged.  The most effective way is to create a macro
that uses the IsNull function to check for a value in the field.
For more information, search Help for "macros: validating data"
and go to the "Validating Data Using Macros" topic.
---------------------------------------------------------------------

Question 17

How do I refer to a control on a subform?

Answer 17

When you refer to a subform on a main form, you are actually
referring to the subform control.  To refer to the subform itself,
use the Form property of the subform control:

Forms![main form]![subform].Form![control]

For a nested subform, use the following syntax:

Forms![main form]![subform].Form![nested subform].Form![control]

For more information, search Help for "subforms: referring to
controls on."
---------------------------------------------------------------------

Question 18

How can I change the behavior of the insertion point when I tab into a
field?

Answer 18

By default, if you use the arrow keys to move to the next field,
the entire field is selected.  To have the insertion point move to
the next character instead of the next field, choose Options from
the View menu, select Keyboard, and change the setting for Arrow
Key Behavior from Next Field to Next Character.

For more information, search Help for "Options command."
---------------------------------------------------------------------

Question 19

How can I convert a form to a report?

Answer 19

In Design view, choose Save As Report from the File menu.
---------------------------------------------------------------------

Question 20

How can I sort the data in a report on a field from a table other
than the one on which the report is based?

Answer 20

Create a query that includes the field from the other table, sort
on the field, and then base the report on the query.  If you have
already created the report, verify that the query includes all the
fields used in the report, and then change the report's ControlSource
property to the name of the query.

For more information on creating queries, see Chapter 5,
"Query Basics," and Chapter 6, "Designing Select Queries," in the
Microsoft Access User's Guide. For more information on basing
your report on a query, see Chapter 17, "Report Basics," and
Chapter 18, "Designing Reports."
---------------------------------------------------------------------

Question 21

When I print my report, every other page is blank. What causes this
problem?

Answer 21

The width of your report plus the left and right margins should not
exceed the width of the page (usually 8.5").
----------------------------------------------------------------------

Question 22

I've made a change to a record that violates a validation rule or
referential integrity. How can I restore the change so the error
message box doesn't re-appear?

Answer 22

Press the Esc key, or choose Undo Current Record from the Edit menu.
---------------------------------------------------------------------

Question 23

How can I analyze the structure of my database?

Answer 23

Microsoft Access Product Support Services has developed a
tool called the Database Analyzer, which enables you to print
structural information about the tables, forms, and other
objects in your databases. This information can be useful in
keeping track of fields, controls, and properties in your
database objects and in diagnosing problems with a database.
When you call Microsoft Product Support Services, they may ask
you to load the Analyzer and run an analysis of any database
objects that are causing problems.

If you chose the Complete Installation option when you installed
Microsoft Access, or if you installed the sample applications as
part of a custom installation, the Database Analyzer was copied
onto your hard disk. Follow the steps in the following procedure
to load the Analyzer into memory and use it.

To load the Database Analyzer:

1 Using Microsoft Windows Notepad or another text editor, open
  MSACCESS.INI (in your WINDOWS directory), and add the following
  line under [Libraries]:

    analyzer.mda=

2 Save the file, and restart Microsoft Access.

3 Open the database you want to analyze.

4 Click the Macro button in the Database window, and then
  choose the New button.

5 In the first row, choose RunCode as the action.

6 Type StartAnalyzer() as the Function Name argument
  for this action.

7 Close the Macro window, saving the macro and naming it
  "Analyzer."

Note: For other databases, copy the Analyzer macro onto the
Clipboard, open the database, and paste the macro to the list
of macros in the Database window.

Once you load the Database Analyzer, you can use it to analyze a
single object such as a table, multiple objects (including
different types of objects at one time), or an entire database.

To analyze a table:

1 Open the database.

2 Run the Analyzer macro.

3 In the Database Analyzer, click the Table button.

  The list of tables in the database appears in the Items
  Available box.

4 Double-click one of the tables in the list.

  The table moves to the Items Selected box.

  You can also use the right arrow button to move a selected
  object from the Items Available box to the Items Selected
  box.  Use the left arrow button to remove an object from the
  Items Selected box.

5 Choose the Analyze button.

  In the Select An Output Database dialog box, select the
  database into which you want to place the tables created by
  the analyzer. You can place them in the database you are
  analyzing or in a separate database set up for analyzer tables.

6 Select the database, and then choose OK.

  Microsoft Access creates a table listing the table fields and
  important properties for each field.

7 Close the Database Analyzer, click the Table button in the
  Database window (or, if you selected a different database, open
  that database), and then open the table named @TableDetails.

With the Database Analyzer, you can:

* Analyze any or all of your tables. (Each table structure is
  appended to the bottom of the @TableDetails table.)

* Analyze all the other objects in your database, creating the
  tables listed below. You can select and analyze multiple
  objects at one time. For example, you can analyze all your
  tables and queries at the same time.

* Select the properties you want to include in the analysis.
  To select properties, choose the Properties button, and move
  the properties from the Available Properties box to the
  Selected Properties box.

* Print the tables, or create reports produced by the analysis.

The table below lists the tables you can create with the
Database Analyzer. Microsoft Access lists these tables along
with other database tables in the Database window.

Object         Table Name         Contains
----------------------------------------------------------------
Table          @TableDetails      List of fields, data types,
                                  lengths, indexes

Query          @QuerySQL          Query's SQL statement
               @QueryDetails      List of fields, data types,
                                  lengths, and source tables

Form           @FormProperties    Form properties
               @FormControls      Controls and selected control
                                  properties

Report         @ReportProperties  Report properties
               @ReportControls    Controls and selected control
                                  properties

Macro          @MacroDetails      Macro actions and arguments

Module         @ModuleProcedures  Module procedures and parameters
               @ModuleVariables   Variables declared with a
                                  DimlanDim statement

The Database Analyzer creates temporary tables that increase its
size. To reduce it, compact it as you would a database. Before you
compact it, however, you must remove it from memory.

To compact the Database Analyzer:

1 Using Microsoft Windows Notepad or another text editor, open
  MSACCESS.INI (in your WINDOWS directory), and type a semicolon
  in front of the following line under [Libraries]:

        analyzer.mda=

2 Save the file, and restart Microsoft Access.

3 Compact the ANALYZER.MDA file.

4 Open the MSACCESS.INI file again, and remove the semicolon that
  you typed in earlier.

5 Restart Microsoft Access.

(Tip: Instead of compacting the Database Analyzer, you can copy
the original ANALYZER.MDA file into another directory, and then
copy that file over the file in the ACCESS directory whenever it
gets too large.)

-----------------------------------------------------------------