Note: My Access tables are Linked to SQL Server 2008 except for a few local temp tables. Local table naming convention is [~ImportTable]
Some of the threads on this were old.
Each Wall Street trading day, I receive an Excel worksheet with thirty thousand rows of future trading data for a commodity (puts and calls). The data layout (headers, title information...) is the same, however the number of records may change.

To automate this (i.e. let some other person click a button) the following programming steps are used:

1. Create a local Access table that is a clone of the production data table (in my case, a linked table to SQL Server 2008) i.e ~ImportOptions and ImportOptions
2. Use the code to delete all data in ~ImportOptions
3. Import all data from Excel into the local table
4. Use code to delete all header or random data in the ~ImportOptions table
5. Use SQL update query to convert any data in ~ImportOptions table
6. Append ~ImportOptions table into Production ImportOptions table

By staging each Excel sheet in a temp table, all data Quality Assurance or conversions can be done with SQL statements before appending the data.
Please Login or Register  to view this content.