Data from Excel to Access

[Guest]

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?

 

[K Dales]

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

 

[Jamie Collins]

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?

--