MS Access Linked Table Error

Using Access (MS Office 365 Pro Plus) I created a linked table to Oracle with no issues. When I double click on the table name, I get the error "ODBC--call failed".  I click "OK", and the table displays. It contains the correct column names, and the correct number of records. However, every value in every column is #Name?.

The really odd thing is that if I click on the filter button in each column heading, I see a list of all the correct values for that column! If I select one, or a few, I get the error "ODBC--call failed", and then a display of the correct number of rows that should be there, but again, all the values are just #Name?.

Any idea what causes this, and how I can fix it?
bassman592Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
The cause is most likely that the table uses some data type that the ODBC driver doesn't recognize such as BigInt.  The dropdowns work because they select only the column in question.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bassman592Author Commented:
Yes, thank you - it is a couple of timestamp columns that MS Access can't handle.
PatHartmanCommented:
I haven't run into issues with timestamps but nothing surprises me.  I haven't used Oracle in quite a while so maybe it has a different data type than SQL Server does.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
What driver are you using?
bassman592Author Commented:
Ok, so I did a little digging and found out that...    The Oracle Timestamp data type contains a precision that is not ODBC compliant. So if you try to use the "regular" Microsoft Oracle ODBC driver, and the table or view you are connecting to contains a column is an Oracle timestamp, you will get the results I describe in my question. Even if you have installed, and use, the Oracle ODBC driver you will get the same result. Unless.... the Oracle ODBC driver configurator contains several tabs of optional features. One of them is called something like "Bind timestamp as date", and that is NOT checked by default. If you check it however, it translates the Oracle timestamp as an ODBC compliant date data type, which still contains time (if present, of course). At any rate, after I configured a DSN in this way, I was able to link to my "problem" table just fine. I guess the former timestamp columns have lost some precision (I don't know that for a fact), but that's fine for my purposes.

Thank you all.
PatHartmanCommented:
Thanks for the complete answer.  The SQL Server ODBC driver is not configurable the way the DB2 and Oracle drivers are so I forget about that possibility since I use SQL Server almost exclusively these days.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.