I have been going nuts looking for a straightforward way to do this, but most answers I have come across go off on a tangent instead of directly addressing the question.
Here is a simplified SQL snippet I am trying to run through VBA
SELECT * INTO ##Customers FROM Addresses
SELECT * FROM ##Customers a LEFT JOIN PostageRate b ON a.ZipCode=b.ZipCode
And then I need to put all this data into an Excel sheet.
So far I am able to execute:
SELECT * FROM Addresses
This is the functional equivalent of the first query without the temporary table. I land up with a RecordSet which I then put into a sheet using CopyFromRecordSet, but I don't know how to turn this RecordSet into a table so I can query it for the second query where I do the LEFT JOIN and then put the resulting RecordSet on the sheet.
Naturally the queries I am trying to run are more complex than this one, but this illustrates what I am trying to achieve.
Apologies if I have missed a post that does answer this, but I have yet to come across it.
Thanks!
EDIT: I have looked into putting the data from the first query onto a sheet and treating the sheet as a table for the second query. The problem is as follows:
The sheet is local and the second table is on a database. According to this question, the database would need to have access to the Excel sheet. It does not. Any more insights about how to achieve temporary tables through VBA would be greatly appreciated. Thanks all!