ODBC Link Problem

[KernelK]

I have an ODBC connection using the Microsoft ODBC for Oracle driver. I am attempting to link a set of 6 tables in my access database so that I can query the Oracle information.

When I set up the links to tables, I get no errors. The problem happens when I try to open (view) or query four of the tables. I get the error "ODBC--call failed." on 3 of the tables and all of the cells have the error "#Name?". On the fourth table I get the error "ODBC--call failed. [Microsoft][ODBC Driver Manager] SQL data type out of range (#0)" and all of the cells show "#Name?".

The other two tables I can view and query just fine. The strange part of all of this is that if instead of linking to those four tables, I Import the tables, everything works just fine. The imported table structures appear to be the exact same as the linked tables. So I know the ODBC connection works, but won't link properly.

I am trying to avoid having to import the tables because I do not want to have to re-Import the tables daily as the data in them changes frequently. I would prefer to link directly to the source.

Any help is much appreciated.

 

[Banana]

Some information is needed:

1) What driver are you using? Version?

2) Are you using a DSN? What is the connection string? (you can X out the username, password, and database name if desired)

3) What data types does the problematic tables contain? If you go to linked table's design view, see what data types is used within Access and compare with Oracle's structure and verify it is mapped correctly.

4) Can you connect using exact parameters in code? (e.g. using an ADO connection?)

HTH.

 

[KernelK]

How can you say it is impossible? Apparently it is possible, 1. Because I can IMPORT the data, 2. Because two of the tables link perfectly fine.

If it is impossible, please elaborate on why...

Also, update. I forgot that the table i get the "...out of range..." error, I can't directly import that one either. What I can do is run a make-table query from the linked table (one that won't let me look at it or run Select queries on) using just the fields i need for my purposes (excluded 2 fields a memo and an double). The make-table query executes without a hitch and creates my table minus the two fields.

 

[Banana]

Kernel,

your 2nd post make it clear that there is an issue with data type mapping issue as I know that Oracle has certain data type that are just plain not supported in Access. Not a showstopper, as you showed.

BTW, it may be easier to create a View in Oracle with the fields you need and link against that instead of the table itself. I don't know if Oracle also allow you to redefine the datatype in a View, but if it can be done, you could also use that to alter the datatype so Access can read it.

Furthermore, in case of MySQL ODBC driver, there is an option for converting BIGINT into a INT for those (e.g. Access) that doesn't support BIGINT. Inspect your ODBC driver to see what options are available and use those to make mapping more accurate.

HTH.

 

[KernelK]

Answers to Banana:

1. Microsoft ODBC for Oracle 2.575.1117.00

2. I am using a System DSN and as such I am unsure of the connection string. When I mouse over the linked table in Access I get: "ODBC;DSN=EWFM;UID=ewfmodbc;SERVER=EWFM;;TABLE=TCSDBOWNER.EMP"

3. Number-Decimal, Text on the access side, those are the only two types being used. I think the one table that was giving me the "...data out of range..." error may be because of a Memo field in the table that Access is trying to map as text. I am not sure of the Oracle side as the only access I have is the ODBC access. Is there a way to force the link to map a different data type? Maybe I could just play with them until it works....

4. I haven't tried to use a connection through code as that isn't the solution I'm looking for. I need to have the tables linked to the current data at all times. But I can try an ADO connection for trouble-shooting purposes.

 

[Banana]

Pretty sure Decimal shouldn't be mapped to Text and may have contributed to problems.

If I were you, I'd go and find documentations or Oracle forum and figure out how to map the data type correctly. Oh, I almost forgot- Microsoft did discuss about the data mapping for Oracle in their whitepaper. If you go to support.microsoft.com and search for "Jet ODBC connectivity whitepaper", you can download it for free and read the whitepaper which explains how everything works with a section for Oracle's mapping and issues.

I only mention code because you usually get more information about problems with it if it's done through code. I also agree that it's just not worth it for production use, but for troubleshooting, it's quite indispensable.

HTH.

 

[georgedwilkinson]

Hey let's try to keep it civil. This is a professional forum, not an MMORPG.

 

[Banana]

It's impossible, you can't do that ****

Instead of saying it's impossible, explain why you think it is impossible, and you could try to be open to the possibility that you're wrong.

 

[georgedwilkinson]

The **** don't work like that, it's impossible

Please watch your language!!! This is a professional site.

And tell why it won't work or site your source. Nobody knows what you're talking about.

 

[georgedwilkinson]

Do yourself a favor and don't click on the link. It is a porn site.

 

[KernelK]

Banana, thank you for all your helpful suggestions. After reading through some oracle forums, I think it may be a simple driver issue. I'm waiting for the oracle 10g drivers to d/l right now and i'm going to see if that may clear all this up.

seabass341, don't you have anything better to do then troll through technical forums espousing views that are clearly baseless? And why would I take the opinion of someone who didn't even know what the dlookup function was as of three days ago? http://www.access-programmers.co.uk/forums/showthread.php?t=155661

 

[Banana]

Do yourself a favor and don't click on the link. It is a porn site.

Thanks. The incident has been reported.

KernelK, keep us posted; would love to see the resolution.

 

[georgedwilkinson]

seabass341, don't you have anything better to do then troll through technical forums espousing views that are clearly baseless? And why would I take the opinion of someone who didn't even know what the dlookup function was as of three days ago? http://www.access-programmers.co.uk/forums/showthread.php?t=155661

I think his evil roommate must have gotten ahold of his account.

 

[KernelK]

Well, it certainly seems to be a driver issue, as the Oracle 10g drivers work beautifully. Thanks for the help guys!

 

[Banana]

Glad it worked out! You can bask now that you've officially one-up'ed seabass-something!