"The database engine could not lock the table XXX because it's already open by another person or process".
Does any macro step perform an open action on any form that is bound to a table you're trying to update or append to? Does it open an updatable query, and in another step you're trying to update/append to the table the query has a lock on? Point I'm trying to make is that since you've declared no one else has the db open, the macro process (or a prior process) has opened a form or query and that is causing the sql to fail. If so, a step may be required to close the object prior to the sql execution. Kinda guessing since I very seldom use macros.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.