Finally got it working. For those looking this up at a later date, the
main issues we

1. Length of the SQL Query - reduced this by using a table alias
(SELECT * from longtablename as F ... ORDER by F.product ...) and the
length came down to 158.
2. Quotation marks around the filename. Once I put these in, and took
out the bit where I replaced "~" with "~~", the query recognised the
file easily enough. I left the filename exactly as it is shown in
Explorer with only the file extension .dbf removed from the end.

Note: The change to vbCrLf didn't affect the pulling in but it does
look much tidier.

Thank you so much for your help joel, you've saved me a whole extra
day of wasting time on this. And helped tidy the code all at once!

It now pulls the file in within a few seconds (alternative methods are
to manually export from FoxPro which wouldn't suit my users who are
oblivious to FoxPro or to use a specific add-in designed for the
output which takes half an hour - not kidding - to pull in one little
file).