Access Stored Procedure Automation Error

Thanks for the info, but I use this approach in all of my Excel documents that refer to Access DBs and I have never had this issue before. As far as I understand it that LockType specifies what the Locktype in the DB is, not excel, as the code is passed to the Access DB engine.
I have other queries that are run in the same way, in the same document, that use the same 'open_record_set' procedure as above with great success. I also have some that write back in other multi user environments and they don't work unless adLockOptimistic (3) is selected.

I tried changing it to 1 at any rate, just confirm or deny my theory, and I does not work sadly.

Right, I've solved it!

The issue was in my SQL String:

SELECT A2.*
FROM (SELECT A.[Item IDSizeColourFitWarehouseStock Status], A.[Item ID], A.Size, Switch((A.Size='8' Or A.Size='6') AND Left(A.Size,1)<>'0', 'Missing leading 0', true, null) AS [Leading 0 Issue] FROM SiMBA_Plan_Data AS A)  AS A2
WHERE A2.[Leading 0 Issue] <> null
ORDER BY A2.Size;

The above runs fine in Access, but it doesn't when using the ADO libraries in VBA Excel.
However the below does:

SELECT A2.*
FROM (SELECT A.[Item IDSizeColourFitWarehouseStock Status], A.[Item ID], A.[Size], Switch((A.[Size]='8' Or A.[Size]='6') AND Left(A.[Size],1)<>'0', 'Missing leading 0', true, null) AS [Leading 0 Issue] FROM SiMBA_Plan_Data AS A)  AS A2
WHERE A2.[Leading 0 Issue] <> null
ORDER BY A2.[Size];

As you can see the only difference is I've put [] square brackets around my 'Size' field. So this would suggest that 'Size' is a reserved word in the ADO libraries and must be wrapped in [] square brackets when refering to a field name, yet it is not a reserved word in Access and therefore doesn't require the square brackets. This is why it worked in Access, but not in Excel.

Thanks for the help