Carol, importing data from Word to Access works, but you
must be precise about the way you format it. Generally,
the easiest way to do this is to follow this basic
process:
1. Create a separate document with just the information
that you want to import. The contents of this doc should
be nothing more than what you want to be in a single
table in Access. If you've got information in Word that
should go into multiple tables in Access, create multiple
docs in Word for importing.
2. In each Word doc, format the info into a single table,
however large it needs to be. The columns in the Word
table should match the columns you want in Access. Make
the first row of the table be column names. From your
example, I would do something like this:
Code Name Description
101.102 Heart Attack Heart Attack relates to
Cardiovascular, etc...
....
3. Convert the table into text. First, select the entire
table; you can do that from the Table menu by choosing
Select > Table. Then, from the Table menu select Convert
Table to Text... You'll get a dialog that
says "separate text using..." Use either tabs or commas.
(I prefer tabs myself, because I'm likely to have commas
within the contents of table cells.)
4. Save the doc as a plain .txt file, then close it. Word
will ask you what kind of encoding to use; either Windows
or DOS is fine.
5. Switch to Access. Access can now import the table from
this doc, either into a new table it creates or an
existing table you've already set up. I usually let it
create the table for me, because it simply saves work: if
I've done my table correctly in Word, Access will create
a proper table from it.
6. In Access, from the File menu, select Get External
Data > Import... This opens a dialog where you can find
and open the text file you just saved from Word. (If you
can't find the file at first, go to the bottom of the
dialog box and choose Text files from the "Files of
type..." list.) Open your text file and Access starts the
Text Import Wizard.
7. Follow the steps in the Text Import Wizard. You'll
want to choose "Delimited" for your text format.
8. In the next step, choose the Tab as the delimiter (if
you chose tabs to separate your text in Word) and check
the "First row contains field names" box. Also, you'll
note here that there's a "Text qualifier" field. By
default, it's "none", but if Access has trouble
interpreting your text properly, you can go back to Word,
place single or double quotes around each of your text
cells, and re-save the doc; then go through this wizard
again and choose the proper qualifier, which will tell
Access how to handle your large text blocks.
9. In the next step, you can choose to import into an
existing table or let Access create a new one. Let Access
create a new table for you. Name it what you like, or
Access will use the name of your text file as the name
for the new table.
10. In the next step, Access lets you set lots of details
about the way it interprets each of your columns of data.
You can explore these, but the first time through just
accept whatever Access wants to do; it's generally good
enough for starters. If you don't like the results, you
can always repeat the process and tweak these settings as
you like.
11. In the next step, Access asks you to select or let it
create a primary key. Do the latter. If you don't
understand keys, read up on them; they're an essential
element of good database design, and letting Access
create them for you is a good practice, unless you
already have placed a column in your source doc that you
know you want to use as a key.
12. In the next step, name your new table and click
Finish. Access imports the data into what should be a
good table. If you like, Access can now run another
wizard that analyzes the table it just created to offer
you advice on how to optimize it. Go ahead and run the
wizard if you want; but unless you understand what it
tells you, don't let it do anything. You'll be better off
reading its advice, studying the concepts, and then
reconsidering your original table design.
-Matt Lusher
Microsoft
This posting is provided "AS IS" with no warranties, and
confers no rights.
