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?

 

[Adrian Jansen]

If you are only using Excel as a data-entry medium, it might be as simple to
make an Access application which looks and acts like those parts of Excel.
An Access datasheet form already looks like an Excel spreadsheet. All you
have to do is supply a custom menu bar with the editing features you want on
it, and make it act as close as you like to the Excel one. Then you get the
full power of Access for the data handling, without going through all the
Excel-Access hoops.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Jamie Collins]

My objective of this project is to have my co-workers,
who are not familiar with Access, to input data in Excel.

See my reply to your same post in microsoft.public.excel.programming.

Jamie.

--