Following are the most frequently asked questions in Section 2 (Tables/DB Design) of the MSAccess forum. If you don't find the answer to your question here, please feel free to post it in Section 2 and address it to "All."

1. Q. How should I design my database?

   A. The best place to start is with Chapter 2, "Designing a Database,"
      in the Access 2.0 "User's Guide," and you may also want to take a 
      look at article number Q100139 ("INF: Database Normalization 
      Basics") in the Microsoft Knowledge Base forum (GO MSKB). In 
      addition, reading some books on database design is highly 
      recommended. (See "Recommended Books on Database Design" on page 
      39 of the Access 2.0 "User's Guide.")

2. Q. I've separated my application into two databases as the 
      documentation recommends. When I distribute my application, how am 
      I going to get Access to attach to the tables?   

   A. There are several examples of how to do this in the forum 
      libraries, such as NWLOCL.ZIP in library 7 and REATTC.ZIP in 
      library 2. (For more examples, you can do a keyword search on 
      "attach" in the forum libraries.)

      There are also a couple of Access Basic functions available on 
      pages 35-37 of the "Advanced Topics" manual that comes with the 
      Access Developer's Toolkit (ADT). If you don't have the ADT, you 
      can use the example in SOLUTION.MDB in your Sampapps subdirectory. 
      Note: The code in SOLUTION.MDB depends upon the presence of the 
      Access wizards, so this option should only be used in the full 
      version of Access.

3. Q. I deleted my table from the relationships window, but Access still 
      thinks that the relationship exists. What's going on?

   A. If the table/relationship appears again when you choose Show All 
      from the Relationships menu, then you have only removed the table, 
      not the relationship. Deleting a table from the relationships 
      window only removes that table from the *layout* of the window. To 
      delete the relationship, you must select the join line with your 
      mouse and press the delete key.

4. Q. Access says that it can't delete my table because it is 
      participating in a relationship, but I can't find the relationship 
      in the relationships window. Help!

   A. Assuming that the relationship has been deleted correctly (see #2 
      above), you may have a "phantom" relationship. Article number 
      Q119481 in the Microsoft Knowledge Base forum (GO MSKB) describes 
      the cause of this problem and provides an Access Basic function to 
      delete the phantom relationships.

5. Q. The right fill character (!) in my input mask doesn't really fill 
      from right to left.

   A. This is a known problem with the right fill character, and it is 
      discussed in article number Q114880 in the Microsoft Knowledge 
      Base forum (GO MSKB). The article provides an example of how to 
      set up a phone number field so it accepts either a 7-digit phone 
      number or 10-digit phone number.

6. Q. How can I get my counter field to start at a number other than 1?

   A. You can use the technique described in the on-line Help. (Choose 
      Search from the Help menu, search on "Counter," and go to the 
      topic titled "Changing the Starting Value of a Counter Field 
      (Common Question).") This is also covered in article number Q94821 
      in the Microsoft Knowledge Base forum (GO MSKB).

7. Q. My counter field is missing some numbers. How can I renumber them?

   A. The counter field doesn't automatically reuse a number when you 
      delete a record. You may also have some numbers out of sequence if 
      you have started to add a record and then canceled it. You can 
      resequence your counter field with the following steps, but keep 
      these notes in mind: 
     
      -Counters should not be used as "record numbers." In fact, a 
      record number doesn't have much meaning to a Relational Database 
      Management System (RDBMS) like Access. Unique primary key 
      values distinguish one record from another, and the best 
      primary keys are made up of naturally-occurring unique values, 
      such as a Social Security Number or an Employee ID, rather than a 
      counter or a "record number."

      -Don't resequence these numbers if this is the primary key of a 
      parent table that has related child records unless you have
      selected Cascade Updates in the relationship.

      Method 1
      1. Delete any relationships involving this counter field.
      2. Make a backup copy of your table.
      3. Open the table in design view.
      4. Remove the primary key index (if the counter field is part of 
         the primary key).
      5. Delete the counter field.
      6. Add a new counter field.
      7. Reset your primary key if applicable.
      8. Recreate relationships as necessary.

      If you are using the steps above on a large table in Access 1.x, 
      you may run into memory errors. (This is due to the 4M transaction 
      limit in 1.x. In 2.0, you are limited by the amount of available 
      memory.) If this is the case, use Method 2 below.

      Method 2
      1. Delete any relationships involving this table.
      2. Make a copy of your table's structure. (Highlight the table in 
         the database window, select Copy from the Edit menu, select 
         Paste from the Edit menu, type in a new table name, and choose 
         Structure Only.)
      3. Create and run an append query that appends the records from 
         your old table to the new one. (Include all of the fields in 
         the query grid *except* the counter field.
      4. Recreate relationships as necessary.