Access ODBC connections

[Steve Rowe]

Hi,
There is always one thing you don't test during an upgrade or an assumption you make that turns out to be wrong....Mine was that a 64-bit ODBC driver would be easy to get hold of...


So I can't get my 9.0 64 bit TM1 server to talk to access.
Some questions then
1. I can't find anywhere that links to a 64 bit ODBC driver for Access, so does anyone have an idea where one might be. I can find several places that explicitly say there is no such thing but they are 2 or 3 year old posts now.
2. Is there a way to persuade a 64 bit TM1 server to use 332 bit ODBC drivers, again I assume not.
3. I found this link which is to a security patch for a 64-bit MDAC pack which I'm hoping might contain an Access ODBC connection link, but....I can find no link or reference to the 64 bit MDAC on the M$ sight.

Any help or pointers I'd be very grateful...

Cheers,

[rollo19]

You might be using: C:\Windows\System32\odbcad32.exe to configure your ODBC connection from Administrative tools
You could try running: C:\WINDOWS\SysWOW64\odbcad32.exe
http://www.archivum.info/microsoft.publ ... 01995.html

[Steve Rowe]

Thanks Rollo, that's the different ODBC consoles for 32 and 64 on a 64 bit box. Once you have 64 bit TM1 running though you can only use 64 bit ODBC drivers though (on the system32 path). I think so anyway.....

[mattgoff]

You're right Steve, 64-bit TM1 has to use the 64-bit Data Sources control panel. I did a quick search, and it appears you're SOL. Looks like it's *shudder* AXNet time....

Matt

[Michel Zijlema]

Hi Steve,

I don't think there will be 64-bit ODBC drivers for Access available, so you need to use the 32-bit driver over the ODBC Gateway.
To make this work you first need to setup the (system!) DSN through the odbcad32.exe console (the one in the WoW64 directory).
Second you need the ODBC Gateway installed on your server and you need to add the ELFODBC parameters in your tm1s.cfg file.
When all has been set OK you should be able to connect to the Access database from your 64-bit TM1 Server instance.

Michel

[Mike Cowie]

You're right Steve, 64-bit TM1 has to use the 64-bit Data Sources control panel. I did a quick search, and it appears you're SOL. Looks like it's *shudder* AXNet time....

Unless Steve decides to run TM1 9.4, in which case it can never be AXNET time again. From the 9.4 release notes:

Axnet is no longer supported in TM1. If you are running the x64 version of the TM1 server and
previously use Axnet to access 32-bit applications, you must install and use the appropriate 64-bit
drivers for your applications.

We got stuck with this situation once already (an ODBC driver for GL data whose vendor hadn't yet developed a 64-bit version) and certainly Access fits into the same category. Apparently this was killed because of the need for Unicode support and the Axnet solution's apparent inability to support that fully. I can't say I'll miss it terribly, but it did help in those few cases where we didn't have 64-bit drivers or couldn't when nobody in IT could get the 64-bit Oracle drivers working.

Some replies from support to the "What the heck do we do now?" question:

If you are using a 64-bit TM1 9.4 Server for Windows x64, and if your are to sue [sic] an ODBC datasource, for your TI processes, you must use a 64-bit ODBC Driver.

If the database vendor does not offer a 64-bit ODBC Driver for Windows x64, maybe some Third Party vendor like DataDirect or EasySoft is offering such a 64-bit ODBC Driver.

If there is no 64-bit ODBC Driver, if there is only a 32-bit ODBC Driver, you cannot connect to that database if you are using 64-bit TM1 9.4 Server for Windows x64.

By IBM Cognos, there are only 2 solutions:
- Do not upgrade to 64-bit TM1 9.4 Server for Windows x64. but instead stay with 64-bit TM1 9.1 Server for Windows x64 supporting the axnet ODBC gateway.
- If possible use the 32-bit TM1 9.4 Server for Windows x86 instead of the 64-bit TM1 9.4 Server for Windows x64.

There may be other solutions, like a 32-bit ODBC to 64-bit ODBC Bridge connecting a 32-bit ODBC Driver with the 64-bit OODBC Driver Manager of Windows x64, but only the database vendor or Third Party vendors specialised on Database Connectivity software like DataDirect or EasySoft can answer such questions.

Someone else from IBM Cognos suggested in a separate conversation trying an intermediate 32-bit TM1 Server to get the 32-bit ODBC data effectively into a TM1 "staging" area. Then, you could get it onto a 64-bit TM1 Server through replication or some other means, like exporting it to a file. That could work, too, though it isn't ideal (especially if you're using replication to accomplish the data movement).

In our case we were able to live with a 32-bit version in production since the TM1 application was quite small, but at some point we may run into this again.

Sorry to be the bearer of bad news...

Regards,
Mike

[Steve Rowe]

Thanks all, just waiting to restart the server as I have added the Elf... settings to my config file and I should to be go.
Assuming everything works like it is supposed to of course!

I actually found some help on the IBM site very easily too

[Bob Stuecheli]

This thread was about 10 days too late for me. I just went through about 12 days working with Iboglix support trying to get the 64 bit ODBC drivers for the Oracle 10g database to work with the 64 bit version of 9.0 SP3. Of course that still isn't working. However by accident I did stumble across the right sequence of installing the drivers, the 32 bit ODBC Gateway (axnet), the 64 bit server, and then the EFLODBC parameters.

But I don't like having the client password sitting in the cfg file unencrypted. I am not overly concerned because we have only 4 people that can access the LAN directory where the cfg file is located, but I still don't like it. Does anyone know a way around this?

Bob

[mattgoff]

This thread was about 10 days too late for me. I just went through about 12 days working with Iboglix support trying to get the 64 bit ODBC drivers for the Oracle 10g database to work with the 64 bit version of 9.0 SP3. Of course that still isn't working. However by accident I did stumble across the right sequence of installing the drivers, the 32 bit ODBC Gateway (axnet), the 64 bit server, and then the EFLODBC parameters.

But I don't like having the client password sitting in the cfg file unencrypted. I am not overly concerned because we have only 4 people that can access the LAN directory where the cfg file is located, but I still don't like it. Does anyone know a way around this?

Bob

What wasn't working with the 64-bit drivers? We're running Oracle 10.2.0.2 64-bit drivers OK....

Matt

[Bob Stuecheli]

Our database is 10.2.0.3 and the DBS's couldn't find 64 bit drivers for that release. We have 10.2.0.1 64 bit drivers and a patch to 10.2.0.4 but neither would work for us. We could set up a DSN and test the connection to the database just fine through the ODBC Database Administrator, but when setting up a TI process using ODBC, we could click on the Preview button and it would bring back the 10 rows of data, but when clicking on it a second time the server would crash.

In our experience, TM1 seems to be very picky about the database and driver versions. Running a local server on my PC with the 32 bit 10.2.0.1 drivers would not connect to the database in TM1 while it was fine in SQL Plus, but the 10.2.0.3 drivers did work on my PC. It could be something unique to our environment - who knows.

[KevinB]

I installed TM1 9.1.4 64bit Server on Windows 2003 x64 Server with Oracle 9i.

I did install the Oracle Instantclient 10.02.00.02 for the ODBC driver.

I finally got the TI process to connect to Oracle but running a process takes a ridiculous amount of time! The Oracle query would fetch about 800 rows and the TI process took 5 minutes to complete.

Does this have to do with the Axnet service? How do I know if the Axnet service is really working. Is there a way to tell?

Please help!

~Kevin Beckley
Wilsonville, OR