wrote ...

My objective of this project is to have my co-workers,
who are not familiar with Access, to input data in Excel.
Meanwhile I will either establish a link or a macro to
transfer data to Access

such Macro <snipped could only transfer the
whole Excel database into Access in addition to of what I
already (i.e.: duplicate) have instead of updating/
replacing it.

You haven't provided DDL schema (CREATE TABLE) or sample data (INSERT
INTO) so I assume you want a general answer. So, you want to create an
outer join (e.g. RIGHT JOIN) between you main Jet table and the linked
Excel table using the key (your index?) column and test for unequal
data columns (for amended rows) and for null values in the key column
in the Jet table (for new rows). To additionally test for deleted
rows, reverse the join (e.g. LEFT JOIN this time) and test for null
values in the key column in the linked Excel table.

I have a generous nature on Fridays, so here's a quick demo (with DDL)
which, for simplicity's sake, uses one key column and one data column
(assumes a connection to the Jet db e.g. execute each statement in a
query object in the MS Access UI).

Create the Jet table:

CREATE TABLE MorrisJet
(
MyKeyCol INTEGER NOT NULL,
MyDataCol INTEGER NOT NULL
)
;

ALTER TABLE MorrisJet
ADD CONSTRAINT pk__morris
PRIMARY KEY (MyKeyCol)
;

Create the Excel table:

CREATE TABLE
[Excel 8.0;HDR=Yes;Database=C:\Morris.xls;].MorrisXL
(
MyKeyCol INTEGER ,
MyDataCol INTEGER
)
;

Populate the Jet table:

INSERT INTO MorrisJet
(MyKeyCol, MyDataCol) VALUES (1,1)
;

INSERT INTO MorrisJet
(MyKeyCol, MyDataCol) VALUES (2,2)
;

INSERT INTO MorrisJet
(MyKeyCol, MyDataCol) VALUES (3,3)
;

Now, let's create the Excel data in an 'edited' state:

First, a row intact:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\Morris.xls;].MorrisXL
(MyKeyCol, MyDataCol) VALUES (1,1)
;

Then an amended row:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\Morris.xls;].MorrisXL
(MyKeyCol, MyDataCol) VALUES (2,999)
;

Let's say the MyKeyCol=3 row has been deleted in Excel so we simply
won't insert it in the first place.

Finally, a new row:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\Morris.xls;].MorrisXL
(MyKeyCol, MyDataCol) VALUES (4,4)
;

Now, in no particular order, modify the database using the amended
Excel data.

First, INSERT the new rows:

INSERT INTO MorrisJet
(MyKeyCol, MyDataCol)
SELECT
T2.MyKeyCol, T2.MyDataCol
FROM
MorrisJet T1
RIGHT JOIN
[Excel 8.0;HDR=Yes;Database=C:\Morris;].MorrisXL T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T1.MyKeyCol IS NULL
;

Next, UPDATE the amended rows:

UPDATE
MorrisJet T1
INNER JOIN
[Excel 8.0;HDR=Yes;Database=C:\Morris;].MorrisXL T2
ON T2.MyKeyCol=T1.MyKeyCol
SET
T1.MyDataCol=T2.MyDataCol
WHERE
T1.MyDataCol<T2.MyDataCol
;

Finally, DELETE the removed rows:

DELETE FROM
MorrisJet T3
WHERE EXISTS
(
SELECT
*
FROM MorrisJet T1
LEFT JOIN
[Excel 8.0;HDR=Yes;Database=C:\Morris;].MorrisXL T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T2.MyKeyCol IS NULL
AND T3.MyKeyCol=T1.MyKeyCol
)
;

Jamie.

PS Ping Dick: one for the blog?

--