import data from a csv file into an access 2000 database

[fhu130]

I have an access 2000 database with 200+ records with approx 60 fields. I
want to import data from a csv file into the first 54 or so fields. But i
only want to import the data in if either the information within the fields
have changed or the record would be a new one.

The background is.
1. Our supplier has an online database.
2. We have our own database of customers that is almost identicle with only
a few extra fields.
3. We have to go online and update the suppliers database, but we obviously
dont want to enter the information twice.
4. So we want our database to be synchronised with the suppliers online
database.
5. We need it to update the records we already have in the database and also
create new records. without altering the few extra fields that ours contains.
6. The suppliers online database creates a csv file that we can use.
7. Each record does have a unique reference number to tie everything together.

Please help and im sorry if it seems like i am rambling but this is a major
headache for me. I am sure the answer is quite simple.

Many Thanks

fhu130

 

[Beth Melton]

You can do this using a few queries.

Once you import the new table use an Update query to update all
non-key fields for the records you already have in the database. This
is just a matter or adding both tables to a query and joining them by
the reference field. Then add all fields from your supplier table and
update each non-key field with that of the your import table. The
Update To cell for each non-key field would be something like:
[ImportTableName]![Address].

Then use an Append query with an outer join to find all records in
your imported table without a match in your suppliers table to append
the new records. If you don't know how to create an outer join and use
the Null operator to find unmatched records then the "Find unmatched
query wizard" can help you out. Then it's just a matter of changing
the Query type to an Append query.

If you need specific instructions then you may want to post follow-up
questions to one of the Access newsgroups since that's where the
Access experts hang out.

--
Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/