Posting this info to hopefully save someone the hours I spent resolving the problem!
The latest SP that brought us the "unsafe expressions" errors also disabled Access' ability to update a linked Excel workbook via query.
I had 2 excel sheets linked as tables, one the master and the 2nd containing updates. I used a mismatch query as input to an update query which appended new records to the Excel master. Quit working thanks to the unnanounced "feature."
Wrote a macro to query and put the mismatches into a temp table and then do a transferspreadsheet to the Excel master. This puts the data into a new sheet (except when you've already run it before, then it puts it into the sheet you created last time). (If you use the name of an existing sheet, Excel will rename it.)
Of course, when an application pastes to Excel everything comes in as text, so the "number formatted as text" cols have to be fixed prior to pasting the updates into the master sheet. If you don't fix them, the next time the mismatch query runs if mismatches the old records plus the new ones! Thanks a lot, Microsoft!
Frank
The latest SP that brought us the "unsafe expressions" errors also disabled Access' ability to update a linked Excel workbook via query.
I had 2 excel sheets linked as tables, one the master and the 2nd containing updates. I used a mismatch query as input to an update query which appended new records to the Excel master. Quit working thanks to the unnanounced "feature."
Wrote a macro to query and put the mismatches into a temp table and then do a transferspreadsheet to the Excel master. This puts the data into a new sheet (except when you've already run it before, then it puts it into the sheet you created last time). (If you use the name of an existing sheet, Excel will rename it.)
Of course, when an application pastes to Excel everything comes in as text, so the "number formatted as text" cols have to be fixed prior to pasting the updates into the master sheet. If you don't fix them, the next time the mismatch query runs if mismatches the old records plus the new ones! Thanks a lot, Microsoft!
Frank
