Cognos - MS Access Data Source

[CognosPolzovatel]

Does anyone know if it's possible to have a data source in cognos (framework manager & cognos connection) be associated with a Microsoft Access database (versus an Oracle DB or SQL for example). If so, could you please direct me to documentation on how to get this piece properly working? I have Cognos 8.2.

[MFGF]

Hi,

You'd need to use an ODBC connection to MS Access. Set up a System DSN using the Windows ODBC Admin tool, then in Cognos 8 define a data source as type "ODBC Windows Only" to use the ODBC connection.

Regards,

MF.

[CognosPolzovatel]

Does this require the access database to be local on the cognos server? Can this work if the access database is on a remote server, which doesn't contain the cognos installation.

[CognosPolzovatel]

Where can I find more information on this? Perhaps some documentation exists out there of how to do this in Cognos?

[MFGF]

Hi,

The ODBC connection needs to be defined on the Cognos 8 server.  The database file (.mdb) does not need to be on the same server, as long as the ODBC connection can see it somewhere on the network - typically on a shared drive from another server (which does not need to have Cognos 8 installed).

Regards,

MF.

[CognosPolzovatel]

Cool - thanks. I shall go ahead and experiment then, with setting up a MS Access data source in Cognos.

[CognosPolzovatel]

Hello MFGF:

"You'd need to use an ODBC connection to MS Access. Set up a System DSN using the Windows ODBC Admin tool, then in Cognos 8 define a data source as type "ODBC Windows Only" to use the ODBC connection."

I finally got a chance to go back and test this. I setup a System SDN on the Cognos box. I am now attempting (via Cognos 8.2) to create a new data source (via the Cognos web interface). Unfortunately, I do not see the "ODBC Windows Only" type that you are referring to. The only ODBC options that I have include:
ODBC
Composite (ODBC)
Microsoft SQL Server (ODBC)
Teradata (ODBC)

Once one of the above is chosen, I am prompted to provide the connection string (user id and password), etc. What would I need to do here? What if I do not have a username/password set for the access DB?

Thanks for your help.

[CognosPolzovatel]

Particularly, what would I enter in the ODBC connect string (if I choose to create an ODBC data source) in Cognos. 

[ykud]

Use ODBC and enter System DSN name for MS Access you've set up.

[CognosPolzovatel]

Okay, I was doing that yesterday:
ODBC data source: Test
ODBC string: SystemDSNName

No signon.

Test connection:
Failed
QE-DEF-0285 The logon failed.
Unable to connect to at least one database during a multi-database attach to 1 database(s) in: testDataSourceConnection
etc...

[ykud]

SystemDSNName is defined on cognos server?
Try entering login\password for signon.
Try changing windows user cognos service is using, set Logon As property on service to some local admin user.

[CognosPolzovatel]

"SystemDSNName is defined on cognos server?"
Yes, in reality I have a different name, set on both the Cognos server and when defining the ODBC string. I just used SystemDSNName as an example string name (programmer).

"Try entering login\password for signon.
Try changing windows user cognos service is using, set Logon As property on service to some local admin user."
Okay, I do not have a username/password set on the actual Access file, but shall try providing the credentials to local admin user, as you suggest. For some reason I think the issue, however, exists else where. We'll see.

[CognosPolzovatel]

Nope: that didn't do it either.

[CognosPolzovatel]

Moving the file from the mapped drive to reside locally on the Cognos server solved the problem. The connection test succeeds now.

[ykud]

Use network shares and unc paths (\\servername\share\file)

If map drive -- it's invisible to Cognos, unless cognos service runs under the same account.

[CognosPolzovatel]

I am a user (joined to a specific Domain) that connects to the Cognos server and has admin rights. And so, I tried two things:

1. I created a mapped drive on the Cognos server to another drive (a directory where the MDB resides) that I have read rights (and that I also have access with the same Domain credentials).
Via the Cognos server, I am able to go into this mapped drive and traverse for the MDB file. I have read rights on it. However, when I setup the ODBC System DSN and then the data source in Cognos, I get a failed error.

2. I tried to share this folder out to self (with the same user credentials/domain as I logon to the Cognos server). I then setup the ODBC System DSN by searching the network and providing a direct address/path to the MDB file. Again, I have read rights to the folder. However, the data source connectio in Cognos fails.


At the moment, the data source connection works only when the MDB file locally exists in Cognos. However, I would like to be able to keep these files in their current (remote) location.