Categories: sql, vba

MS Access: Query works fine, but fails when VBA calls it

2 answers

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.

All answers to this question, which has the identifier 28634015

The best answer:

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.

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.

Last questions

how do i remove the switch on my home screen?
how to edit the JS date and time to update atuomatically?
How to utilize data stored in a multidimensional array
Powermockito not mocking URL constructor in URI.toURL() method
Android Bluetooth LE Scanner only scans when phone's Location is turned on in some devices
docker wordpress container can't connect to mysql container
How can I declare a number in java that is more than 64-bits? [duplicate]
Optaplanner solutionClass entityCollectionProperty should never return null error when simple JSON object passed to controller
Anylogic, get the time a pedestrain is in a queue
How do I fix this syntax issue with my .flex file?
Optimizing query in PHP
How to find the highest number of a column and print two columns of that row in R?
Ideas on “Error: Type is referenced as an interface from”?
JCIFS SmbFile.exists() and SmbFile.isDirectory() return false when it exists and I can listFiles()
PHP total order
Laravel booking system design
neural net - undefined column selected
How to indicate y axis does not start from 0 in ggplot?
Fragments in backStack
Spinner how to change the data