INF: Database Structure Questions & Answers
  
PSS ID Number: Q104392
Article last modified on 02-07-1995
 
1.00 1.10
 
WINDOWS
 

---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access versions 1.0 and 1.1
---------------------------------------------------------------------
 
======================================================================
  Microsoft Product Support Services Application Note (Text File)
            WX0635: DATABASE STRUCTURE QUESTIONS & ANSWERS
======================================================================
                                                   Revision Date: 9/93
                                                      No Disk Included
 
The following information applies to Microsoft Access, versions
1.0, and 1.1.
 
-----------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY     |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an Application |
| Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER      |
| EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED      |
| WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR       |
| PURPOSE. The user assumes the entire risk as to the accuracy and    |
| the use of this Application Note. This Application Note may be      |
| copied and distributed subject to the following conditions:  1) All |
| text must be copied without modification and all pages must be      |
| included;  2) If software is included, all files on the disk(s)     |
| must be copied without modification (the MS-DOS  utility         |
| diskcopy is appropriate for this purpose);  3) All components of    |
| this Application Note must be distributed together;  and  4) This   |
| Application Note may not be distributed for profit.                 |
|                                                                     |
| Copyright (C) 1992-1993 Microsoft Corporation.  All Rights Reserved |
| Microsoft, FoxPro, Microsoft Access, and MS-DOS are registered      |
| trademarks                                                          |
| and Windows is a trademark of Microsoft Corporation.                |
| dBASE III PLUS, dBASE IV, and Paradox are registered trademarks of  |
| Borland International, Inc.                                         |
| 1-2-3 and Lotus are registered trademarks of Lotus Development      |
| Corporation.                                                        |
| Btrieve is a registered trademark of Novell, Inc.                   |
| Oracle is a registered trademark of Oracle Corporation.             |
| Sybase is a registered trademark of Sybase, Inc.                    |
-----------------------------------------------------------------------
 
1.  Q. What foreign data formats does Microsoft Access support?
 
    A. Microsoft Access can link to Btrieve, dBASE III PLUS,
       dBASE IV, FoxPro versions 2.x, and Paradox versions
       3.x data, including indexes.
 
       Microsoft Access can import data from Btrieve, dBASE III PLUS,
       dBASE IV, FoxPro versions 2.x, Lotus 1-2-3 versions 2.x
       and 3.x, Lotus 1-2-3/W, Microsoft Excel versions 2.x and later
       for Windows, Paradox 3.x, and fixed-length and delimited ASCII
       text.
 
       Microsoft Access can connect to Microsoft SQL Server, Sybase
       SQL Server, and Oracle RDBMS Server using the Open Database
       Connectivity (ODBC) specification. These ODBC drivers are the
       only drivers currently certified for Microsoft Access version
       1.1.
 
2.  Q. What are primary and foreign keys in a relational database?
 
    A. Primary keys uniquely identify a specific row in a table. They
       are usually composed of a single field or column whose data is
       unique to each row of information (for example, a customer
       identification number, employee social security number, or
       order identification number). Primary keys also can be composed
       of more than one field. For example, the fields Company
       Identification and Contact Name could be combined to form a
       primary key for a table that contains information about
       companies that have more than one contact.
 
       Foreign keys identify rows in one table that match the primary
       key of another table. For example, if the field Employee ID is
       the primary key in the Employee table, in the Orders table,
       Employee ID is the foreign key. The relationship between
       primary and foreign keys enables you to access related data in
       multiple tables.
 
3.  Q. Which FieldSize setting should I select for the Number data
       type in order to use a Counter field of another table as the
       foreign key in the current table?
 
    A. To use a Counter field of another table as the foreign key in
       the current table, select the Long Integer setting.
 
4.  Q. How do you change the starting value of a Counter column to a
       number other than 1?
 
    A. To set the starting value of a Counter column to a number other
       than 1, use the following four steps:
 
       1. Create an additional single-column temporary table. Give the
          Number column the same name as the Counter column in the
          original table.
 
       2. Create a new row in the temporary table by inserting a value
          in the Number column. This value should be 1 less than the
          desired starting value for the original table.
 
       3. Create an append query to append the single row from the
          temporary table to the original table.
 
       4. Delete the temporary table. You can then delete the dummy
          row from the original table.
 
        NOTE: Do not compact the database before you add the first row
        to the original table. If you do this, the counter value
        resets to begin at 1.
 
       For additional information and an example of how to set the
       initial value of a counter, please obtain Technical Note
       #94821. You can order this technical note by selecting the
       FastTips Technical Library option from the FastTips Main Menu.
 
5.  Q. Is data in tables stored in a sorted order? How can I view my
       data in sorted order?
 
    A. Data in tables is not stored in sorted order. Data is stored in
       the order in which it was entered. To view data in sorted
       order, create a query, or a form based on a query, that uses
       the Sort field on the query grid. By default, if the table
       includes a primary key, the Datasheet view of the table will
       display the data sorted by the primary key. To view the data
       sorted by something other than the primary key alone, use a
       query, as previously described.
 
6.  Q. How can I delete the primary key from a table?
 
    A. You can delete the primary key for a table by opening the Table
       Properties dialog box and deleting the entry for the Primary
       Key field. Please note that the primary key cannot be deleted
       from a table that is the primary table in a relationship.
 
7.  Q. How can I create computed columns in tables?
 
    A. You can create computed columns or expressions with queries.
       Within the query, create a column that is defined as an
       expression. In general, it is helpful to think of queries as
       virtual tables; you can use a query wherever you can use a
       table. If you're familiar with SQL terminology, this is very
       similar to creating a view. Unlike most implementations of
       views, however, Microsoft Access views can be updated even if
       they involve joins from different data sources, such as
       Paradox, Btrieve, or separate Microsoft Access data sources.
 
8.  Q. Two of my tables have Counter data type columns in common. Why
       can't I define a relationship between the two tables based on
       these columns?
 
    A. You can define only one-to-one relationships between Counter
       data type columns. If the linking column on the "one" side is a
       counter, define the linking column on the "many" side as a long
       integer.
 
       For more information on table relationships, see the "Microsoft
       Access User's Guide," Chapter 1, "Designing a Database," pages
       13-18.
 
9.  Q. How many databases can I open at a time in Microsoft Access?
 
    A. Through the user interface menus in Microsoft Access, you can
       open only one database at a time; therefore, you can view only
       one database window in each session of Microsoft Access.
       Through Access Basic, however, you can use the OpenDatabase()
       function to open several databases at once. The number of
       databases you can have open simultaneously through Access Basic
       is limited only by your computer's memory.
 
       Also, by choosing Attach Table from the File menu, you can
       attach to as many databases as your computer's memory allows.
 
10. Q. Can Microsoft Access version 1.0 use databases created in
       version 1.1?
 
    A. Microsoft Access 1.0 cannot read from or write to databases
       created in Microsoft Access 1.1. However, Microsoft Access 1.1
       can read from and write to databases created in version 1.0 and
       still allow version 1.0 to read from and write to the same
       database without conversion.
 
       If you want to upgrade your version 1.0 database to version 1.1
       format, use the following four steps:
 
       1. From the version 1.1 File menu, choose Compact Database. The
          Database To Compact From dialog box is displayed.
 
       2. In the File Name box, select the database that you want to
          convert. In the List Files Of Type box, select Databases
          (*.mdb).
 
       3. Choose OK. The Database To Compact To dialog box is
          displayed.
 
       4. In the List Files Of Type box, select Access V1.1 (*.MDB).
 
           NOTE: If you want to convert your version 1.1 database back
           to version 1.0 format, select Access V1.0 (*.MDB) instead.
 
Additional reference words: 1.00 1.10
KBCategory: kbusage kbfasttip kbappnote
KBSubcategory: FstQa
=============================================================================
Copyright Microsoft Corporation 1995.
