Thanks for the replies. The OPENROWSET method wouldn't work because Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied on the SQL Server I'm trying to access. I can't use DTS because I only have read access to this particular server.
This is what I ended-up doing (from within Access):
INSERT INTO tblFiscalSales
IN [ODBC;DSN=Epicor Test DB;UID=xxxx;PWD=xxxx;DATABASE=enatworld]
This works, but I'm doing it from a CurrentProject.Connection so it's JET doing the work, not the server. I suspect there would be a slight performance gain if I did it the other way round and got the server to INSERT INTO Access but it's not an option without changing settings on the server.