VBA to append Access tables with data from Excel arrray

[nicktaylorgen]

Hi All,

I receive an Excel document on a daily basis via email. There is a sheet in the document which contains an array of data I want to insert into access tables. I only want to append the tables with new data, since the file contains historical data as well as new data.

If I create a table in the Excel document it will make it easier to write VBA that automatically updates the Access tables, however, I'm afraid adding a table to the document will slow it down even more. The file is already about 35 megabytes. Will a table slow down the file? If so, is there something out there on how to insert arrays or named ranges into a database? I understand SQL, but the VBA piece is a challenge.

Thank You!
Nick

 

[welshgasman]

What is in the data that identifies new records? Date perhaps.?

I have a similar scenario whereby a start date signifies the new data.

I link to the sheet from Access and a form asks for Start Date and that then calls a query to append the data.

No tables needed.

 

[nicktaylorgen]

I figured out how to append the database with all records. It wasn't as difficult as I thought. I will clear the staging table, then append all records, run several queries or VBA to clean up the data, and then run queries to append the tables based on unique "Loan Numbers". I like your approach to this, but I've already gotten much of the code completed.