Dynamic Imports to Access

[Ergo1291]

Hi fellow XLers,

I have a question about importing excel data into a database (Access) via VBA:

Current Situation:

- 1 Excel Sheet containing:
- 98 Columns (A-CT) with Row 1 being the header ¦ includes a Unique ID (UID)
- 6000 Rows (changing all the time)

- 1 Access Table containing:
- 98 Fields (same names as column headers (Row 1) in Excel) ¦ UID is the Primary Key
- 11389 records

What I need to do:

I want to check if an entry exists in the DB, and if so update a number of fields according to the entries in the Excel file.
If it doesnt exist i want to write the entire row into the corresponding fields.

My Problem/Question:

I am new to Access, but i managed to write specific cells from Excel into their corresponding fields in Access by using such a code:

IN EXCEL Module:

With rs
            ' create a new record
            .AddNew 

            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldName3") = Range("C" & r).Value
            ' ......
            .Fields("FieldNameN") = Range("CL" & r).Value
            
            ' stores the new record
           .Update 
End With
r = r + 1 ' next row

as you might see, i would have to write 98 lines of code to add all the columns. I am sure there is a much cleaner solution to this problem, but I have no idea where to start.

Any help is very welcome!!!

 

[Ergo1291]

Also, I dont know if it would be easier to use SQL queries, but I am completely lost there.