RE: Importing Data

It is usally easier to "fix" most errors from within Access by using a
separate "Import table" from which you can then move into your permanent
table.

Below is a basic method that involves a few extra steps, but is easier if
you don't want to write a lot of code.

If this is an Access Project (.adp), you can write a stored procedure to do
this, but I am assuming this is an Access database (.mdb).

Before you begin, if your Excel spread*** has a header row, make sure the
names are not duplicated and, ideally, don't contain spaces (FirstName is
better than First Name).

1. Import sread*** using File--Get External Data wizard. Select "new
table" and let access assign a primary key.

2. If you may recieve an import error, but don't worry about it at this
time. Just import the spread***. If you don't receive an error message,
skip steps 3-4.

3. Open the new import table and delete all the records.

4. Switch to design view and change all fields to text.

5. Open the error log from the first import and note the column(s) that had
data issues. You will need to fix this later.

5. Import again, but this time tell Access to use your newly added Import
table.

6. Most likely, you will not receive an import error message, but if you do
open the Error table and note the column(s) that have errors. Compare this
to the first Error list. It should be less. THESE WILL HAVE TO BE FIXED IN
EXCEL or you can ignore them and add them into the table later, your choice.

7. Create an select query based upon your Import table.

8. Using your first Error table, review the fields that successfully came
into the Import table, but not the first time. There will be data that does
not "fit" the type of data that Access believes it should contain (i.e.
letters in a date field). Using your select query fix any that you want to
fix. Note that the rows numbers in the Error table which will help you find
the errors.

9. Create a copy of the Import table. This will become your permanent
table, so name it accordingly.

10. Option 1 - Open in design view and (one at a time) change the field
type to the type of data you want (ie. field named Date from text to
date/time). Click save. If you fixed all the datat in that row, it will
successfully save. Otherwise you need to review the data and fix the errors.
Continue on, one field at a time until all your data types are the correct
ones.

Option 2 - Delete the data in the new (permanent) table. Open the table in
design view and change all the data types to the correct one. Create an
Update query and select one row at a time to update from your Import table to
your permanent table. If you encounter an error review that field in the
Import table and fix the data.


Let me know if this helps.

Randy


"Mike" wrote:

> Hello,
>
> I am trying to import data into an existing database. The data comes from
> an excel file. Specifically, what I am trying to do is use the "order
> entry" template and I want to import my own products (about 6,000) into the
> products table. I have tried all kinds of sencerios, I've tried adding
> fields to both the excel spead*** as well as the table. I continue to get
> an error, not allowing me to import. On the form, when I type in the drop
> down, I want to use a UPC number and auto fill the rest. First challenge
> though, is the import. Any help would be appreciated. (I use Access 2002).
>
> thanks for any help you can give me.
>
>
> Mike
>
>
> ps. fields I need to import, if this is important, are:
>
> Stock Number
> UPC Number
> Title
> Publisher
> Unit Price
>
>
>
.