import data from excel 2003 to a table in an existing database

[Johnli]

i made a copy of Products table as ProductsChange table in an existing
database. no relationship created yet. i will use ProductsChange table to
update records to Products table regularly. i exported Products table and
saved as an excel spreadsheet called ProductschangeTemplate. i created an
Macro to import data from ProductsChangeTemplate to ProductsChange table.
tried several times and i always get the "key violation error". if i change
primary key field of ProductsChange table to No Index(primary key got to
taken off first), it works but left duplicated records in ProductsChange
table every time i import the same productID.

to void duplication, i delete records in the ProductsChange table before
importing.

there must be some settings i did not do properly. please help how i can
avoid deleting records first but go to my Macro to import directly and don't
have to worry duplicated records.

 

[Ken Snell MVP]

i made a copy of Products table as ProductsChange table in an existing
database. no relationship created yet. i will use ProductsChange table to
update records to Products table regularly. i exported Products table and
saved as an excel spreadsheet called ProductschangeTemplate. i created an
Macro to import data from ProductsChangeTemplate to ProductsChange table.
tried several times and i always get the "key violation error". if i
change
primary key field of ProductsChange table to No Index(primary key got to
taken off first), it works but left duplicated records in ProductsChange
table every time i import the same productID.

to void duplication, i delete records in the ProductsChange table before
importing.

there must be some settings i did not do properly. please help how i can
avoid deleting records first but go to my Macro to import directly and
don't
have to worry duplicated records.

 

[Ken Snell MVP]

The best approach is to use an interim table to receive the contents of your
spreadsheet's data. Then the append query to copy the data to the permanent
table should include a left join to the permanent table on the primary key
field and only select records from the interim table where the permanent
table's primary key field value is NULL.

For example:

INSERT INTO PermanentTable
SELECT * FROM InterimTable
LEFT JOIN PermanentTable
ON InterimTable.PrimaryKey =
PermanentTable.PrimaryKey
WHERE PermanentTable.PrimaryKey IS NULL;