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
