MS Access: Query works fine, but fails when VBA calls it
I may have a workaround as a temporary fix, but this is going to cause problems if I can't get it fixed for good.
I have a query in MS Access that works just fine when it's run on its own, but when I run it using the CurrentDb.Execute command, it gives the error "Run-time error '3146': ODBC--call failed."
I also checked if it fails if it's run from the Immediate window, and it does fail then, too.
The query calls from three different tables plus one local table that was designed by the VBA script. The VBA script works if one particular table is removed.
I know this is very generic, but why would the query work everywhere except if it's called from VBA?
UPDATE: The original question was poorly worded, and I apologize for that. It was written on-the-fly and I was frazzled by the problem I was having.
Here, I think, is the problem in a nutshell: If VBA calls a query that pulls from too many tables (3 or more) on my company's server, it gives the "call failed" error. If the query is just double-clicked, it runs fine.
Now that I'm thinking about it, could it be a server-side issue?
The workaround that I've put in place is multiple queries that create local tables, then running the original query pointing to the local tables instead of the server tables. It works, but is a very time-consuming process, both in writing the code and running the code. For this specific project that I'm doing right now, it'll work, but it would really be good to be able to get this working properly.