Ok, sorry, I had assumed you were using an access project to run the queries(stored procs) on the server. It looks like you have linked the SQL server tables by ODBC and are using the Jet query engine against these tables.
OK, i'm no expert, but I think what might be happening is that your query is grabbing hold of thes tables hard and not letting go until it has finished running, causing you to be left with a lock on the server. This should be temporary, but Jet queries are fat client so the amount of resource on your users PC's will make a difference to performance. I think that the query could be running out of resource to run and leaving the lock on your SQL box. This would tie with the fact that it works for 1 month but not more. The more data you try to use the more resource it requires on the client.
OK, so what could you do:
1) You could upgrade the PC's of the users, but it would always be a losing battle. Might fix this query but not another. Worth doing if you can afford it but don't rely on it as a solution.
2)Distribute more of your processing to the server as views or stored procs.
3) Look at the possibility of breaking your query into several smaller queries that will complete and release the lock and another query to process these to the final solution.