Adding only new values to a sheet from a recordset

[Simebaby]

Hi All
I currently have a worksheet containing data grabbed from a SQL database.
When the data is refreshed, I need to add any news values in the recordset to the sheet, but leave any existing ones in place.
I can construct a unique id from 3 columns of the data for comparison.
Currently I'm looping through the recordset, and any rows that aren't already in the sheet are added.
That works but I suspect it's *very* inefficient - is there a tried and tested way to do this?
I'm sure there's something obvious that' I'm missing.
TIA
Si

 

[Micron]

I think you've reached the point where you definitely are using the wrong app. Just my opion - Excel isn't a database even though so many people seem to want to use it like a database. Unless you can just write your data into an expandable Excel table and use the tools "Remove Duplicates" option then I think you're right. The bigger the list gets, the more this will get bogged down as you loop through it.

One other thing that comes to mind is if you can link your sheet to the sql data, you could create an unmatched query that will find records in the sql data that are not in the sheet, then you could use that as the basis of an update query that you'd have to export to your sheet. However, I think you could only "link" the sheet & sql data by using Access as the go-between app. Note that in Access, linked sheets are static and AFAIK cannot be updated since long ago.

You could also move the Excel part of the process to Access, but Access has a significant learning curve and you will struggle if you design tables like spreadsheets, which is what just about every Excel aficionado does.

 

[Simebaby]

I think you've reached the point where you definitely are using the wrong app. Just my opion - Excel isn't a database even though so many people seem to want to use it like a database. Unless you can just write your data into an expandable Excel table and use the tools "Remove Duplicates" option then I think you're right. The bigger the list gets, the more this will get bogged down as you loop through it.

One other thing that comes to mind is if you can link your sheet to the sql data, you could create an unmatched query that will find records in the sql data that are not in the sheet, then you could use that as the basis of an update query that you'd have to export to your sheet. However, I think you could only "link" the sheet & sql data by using Access as the go-between app. Note that in Access, linked sheets are static and AFAIK cannot be updated since long ago.

You could also move the Excel part of the process to Access, but Access has a significant learning curve and you will struggle if you design tables like spreadsheets, which is what just about every Excel aficionado does.

Agreed. As the data is ordered by date and time, I've gone with finding the most recent date / time, and selecting any data *after* that. Not perfect but will most likely suffice for what I need.
The data is already held in SQL so bringing that into access would be just crazy duplication I would think. The problem is the SQL database only holds the last three months of data and I want to keep any historical entries. I might look at using Access as a longer term thing though.

 

[Micron]

The data is already held in SQL so bringing that into access would be just crazy duplication I would think.

You'd link to it, not import it - unless you wanted to keep records beyond the 3 month window that they are in sql db