Is there any limit of records?

[isladogs]

OK here's a summary of how I do this - its been in place for well over 10 years and runs perfectly 'unattended' except for rare issue where the network connection is lost

As I said before...

Basically you need a unique field in the Excel table so you aren't relying on an autonumber PK field.

For example with my schools databases, I run this process over night every night. I use unique pupil number (UPN) as the reference field.

I extract 30 CSV files remotely from the schools management system database and import each of these to temporary 'buffer' tables so I can modify where necessary.
I then run procedures to do the append/update/delete on each file.

This is done to update staff, student, timetable and other whole school data remotely. This will take a while depending on the size of the school

For a large school of 1500 + students the individual CSV files can be very large. For example the attendance mark files are split into year groups to make them more manageable - each CSV file is over 10MB and approx 150,000 rows

Attached zip file containing the following items:
1. How SDA Link works - that's the utility used to update the main database (SDA).
2. How to use SDALink with Task Scheduler
3. Two random example procedures as text files:
- AppendStudentExclusions - imports the CSV file, saves to 'buffer table' ImportedStudentExclusions and does some data manipulation
- UpdateTimetable - transfers the data already saved to the ImportedTimetable buffer table and saves it to the final table
In most procedures I use SQL code or recordsets but for simplicity I've chosen two procedures that use queries so its easier to understand. Obviously there's lots of items specific to my purposes that you can ignore in
these examples
4. Example of log file produced when each procedure runs - this is emailed automatically when the process is complete

Hopefully the idea will be clear enough in conjunction with my earlier comments.

To reiterate you MUST have a unique field on each record of your Excel spreadsheet so you can determine whether:
a) the record has already been imported (in which case update it)
b) it's a new records (in which case use an append unmatched query)
c) an existing record is not in the latest spreadsheet (in which case delete it or archive it)

Hopefully you can adapt to your needs