The reason you can't create the index is that Excel does
not have (natively) indexes for fields (columns) so there
is nothing for Access to use when it is a linked table
(the index is only good when it exists in the original
database, otherwise there is no way to do an indexed
lookup).

Your second method seems to be no different, really, than
importing the table instead of linking it - so why not
just use the Get External Data... Import data feature?
You could modify your procedure to do what you want (add
only new records) but why bother when Access can do all
the detail work for you?

Both import data and your ADO routine share the same issue
of returning all records - even existing ones, but there
is a way of dealing with this.

Proposed solution: Import into a temporary table, then
use an update query (if necessary) to update any records
(in the main Access table) that might have been changed in
Excel plus an append query to append any new records to
the Access table. If you have added the index to the
original Access table the update and append will both
preserve the index.

Hope this makes sense and helps you out...

K Dales

-----Original Message-----
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 which, at the end will be picked
up by Crystal Report to produce a report. I have made two
unsuccessful attempts.

----------------------------------------------------------

Attempt # 1)

I made a link between Access and Excel so that when
information changed in Excel will change in Access and
vice versa. Steps that I used was

-From Access, FILE, GET EXTERNAL DATA
-Select LINK TABLES.
-Choose Excel directory
-Choose file type EXCEL
-Press LINK
-Press NEXT
-Name the table name as the table name I had in Access
-Press NEXT
-Press FINISH

However, when I went to Access trying to change my data
definition from FIELD PROPERTIES, I realized that I was
not able to set any of my fields as Indexed field. As a
result, I was not able to work out links in Crystal
Report.

----------------------------------------------------------

Attempt # 2) which would be a much better solution if
work out.

I also tried to achieve such task by using Macro in Excel:

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an
Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As
Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset,
adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Nevertheless with such Macro I 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.

----------------------------------------------------------

Can anyone please give me a solution on either to
identify the INDEXED FIELD in Access while linked to
Excel or add a command in the Macro in order to avoid
duplication when the Macro is run?

.