So I have tried this repeatedly and I managed to get it working, and not working. Although there may be many factors involved I wanted to share this factor. The code that I use (using the List name based on Isaacs answer above) works sometimes, and doesn't work other times. I do not change the code but when it doesn't work I get the same error as described above.

Is it possible that Microsoft lists give an access problem if accessed via ADODB in Excel VBA if other syncing is happening to that list in Sharepoint?