Excel Worksheet to Access Table

[Harry]

I need to write VBA code that will import an Excel worksheet into a table.
The original Access data is to be deleted, and replaced with the excel data.

I can do it OK manually, by deleting all records, and then copy the excel
data, and paste append into access, but how is this done in code?
I can also use the get external data etc etc, but I want code that will do
either this or the paste append.

TIA

 

[Douglas J. Steele]

Run a Delete query to delete the original data, then use TransferSpreadsheet
to bring in the new data.

 

[Michael Hopwood]

You can link your excel data into Access as a table, then you can either use
the data real-time or run a SQL query that will populate your table without
using code, to use a linked table:

1. In the database TABLES view right-click and select "Link tables..."
2. Select Excel files in the Dialog box
3. Follow the steps in the wizard

 

[Harry]

Thanks guys.

I'll try both methods (TransferSpreadsheet & link to Excel).
I currently link all tables to the "Front End" with the forms.
Didn't know you could link to Excel.