This is an interesting discussion. We are not alone with need to import data
At first look at the front Excel. Is the user permitted to key in any garbage to
be imported to the database? To be short I still keep to the tab textfiles but I also
do it in un other way.
I design budget/prognoses system(s) with Excel as front and SQL-server as back.
All coding (shuffling data to and from Excel is VBA remote OLE DB ADO in Excel.
Very short the user fetch an Excel template from an intrant server and populate
one or more sheets with data by choise ie cost center. The user then see actual
figures and existing budget/prognoses. All cells showing values are locked!
Certain cells on certain sheets are mark with a different colour and open for just numbers
to be inserted in a certain range. The workbook/sheets recalculate then the user key in
values (so it is a bit of simulation). Also comments can be entered or references to other
documents /stored in the database. When the user is satisfied he/she press a button
and the values (only in the open cells) are inserted in the database by a traditional insert
statement. (Can be update also).
Best luck with your Excel/database adventure.