odbc error when using query from excel

[tziktzal]

Hello everyone, this is the first time ever I turn to the internet for help directly, but I am really out of my depth here and I hope I've come to the right place.

At work, we use Access linked to an Oracle SQL database. For some reports, we call to SQL queries in Access from Excel through the 'Import data - from microsoft Access' functionality. This works perfectly fine for just about everything, but I get an odbc-connection failed error when querying any data from a specific table only. The only extra information that the error in Excel shows is the name of the *.mdb file. The result of the query in Access itself is perfectly fine, no errors.

Any help would be greatly appreciated.

 

[Pat Hartman]

Welcome. We'll help if we can. If you are referencing the Oracle table directly from Excel, perhaps Excel is using a different ODBC driver than Access is using. You shouldn't be able to reference an Oracle table linked to the .mdb from Excel. You shouldn't even be able to see the linked tables. You should only be able to see the tables physically located in the .mdb when you look at the .mdb from Excel.

Can you give us a little more clarification on what is happening where?

 

[tziktzal]

Hi and thank you. Maybe I am misunderstanding something, but I don't think I am directly referencing the Oracle db from Excel. I am importing the data from Access which is linked to the db (I have refreshed all the entries in the linked tables list to make sure the issue is not just there).
In Excel it's Data-Get Data-From Database-From Microsoft Access, then I select the query.

Again, it does work for all the other queries which access data from all kinds of tables from that db, just this one table causes the error.
And when querying directly in Access it does work as well.

Thanks for the hint with the driver versions though, I will check that.

 

[Pat Hartman]

Are you going to give us the error message?