Thanks for the reply and I hope you are doing well today.
I am trying to think of why I decided to save a linked table object and use .Connect and .RefreshLink to change the source instead of using TransferSpreadsheet. But I can't remember the reason.
It could be that I was seeing different ways of checking if a table exists before attempting to delete it (like post #7 above) and decided to use .Connect and .RefreshLink to avoid that. But I don't need to check if the table exists, and I want Access to raise an error and stop running code if somehow the temporary linked table doesn't exist when the delete line executes. And if somehow the delete fails, and the next time the sub runs and TransferSpreadsheet creates "tempLinkedTable1", that's fine too because I'll get an error on the append query.
This is working fine so far with my tests today:
I am trying to think of why I decided to save a linked table object and use .Connect and .RefreshLink to change the source instead of using TransferSpreadsheet. But I can't remember the reason.
It could be that I was seeing different ways of checking if a table exists before attempting to delete it (like post #7 above) and decided to use .Connect and .RefreshLink to avoid that. But I don't need to check if the table exists, and I want Access to raise an error and stop running code if somehow the temporary linked table doesn't exist when the delete line executes. And if somehow the delete fails, and the next time the sub runs and TransferSpreadsheet creates "tempLinkedTable1", that's fine too because I'll get an error on the append query.
This is working fine so far with my tests today:
Code:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "tempLinkedTable", varFile, True
'run append query and other things here
Db.TableDefs.Delete "tempLinkedTable"
