CODEWITHSUNDEEP
sqlvba
user4588747
user4588747

Reputation:

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.

Upvotes: 1

Views: 1643

Answers (2)

Parfait
Parfait

Reputation: 107652

It would be helpful if you post your query so we see its syntax.

But several issues to explore:

  1. CurrentDB.Execute is only for action queries (append, update, make table). For Select queries, use linked tables or recordsets.

  2. Link your external tables into the current database so they can be available as any other local table. Access can connect to other Access files, Excel sources, or any ODBC/OLEDB compliant database (Oracle, SQL Server, MySQL, etc.)

  3. Use ADO or DAO recordsets to pull in your external data and then connect the queries locally as needed in VBA.

Upvotes: 0

Sobigen
Sobigen

Reputation: 2169

I had this issue due to Access having a more functions available from Access than I could use through a SQL statement from Excel. So I would check any methods you're using in your query and see which ones don't work outside Access.

Upvotes: 0

Related Questions