Reporting against an Access database

[dougp]

Using CA 11.1.7IF8

From the perspective of the business user, in the evolution of one's database system experience, what comes right after Excel and right before SQL Server?
Microsoft Access.

My place of work has many Access database files that are used all the time.  There is no IT support for these.  If something goes wrong, the user is stuck.  They still insist on using Access to develop business-critical systems.

I recently got a request to incorporate into a report data that lives in Microsoft Access.

I know how to upload an Excel file into Cognos.  I don't see a way to upload an Access file.

I see in Cognos Administration | Configuration | Data Source Connections, I can add a data source connection that uses JDBC or ODBC.  What I don't see are any examples of how to do this.  I suppose something like https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/standard-security/ may be a good place to start if creating an ODBC data source.

If I go to Manage | Data server connections | Add data server, in the Select a type list, I don't see JDBC or ODBC or Access.

What is your preferred way to use data from Microsoft Access in a Cognos report or dashboard?

[MFGF]

Using CA 11.1.7IF8

From the perspective of the business user, in the evolution of one's database system experience, what comes right after Excel and right before SQL Server?
Microsoft Access.

My place of work has many Access database files that are used all the time.  There is no IT support for these.  If something goes wrong, the user is stuck.  They still insist on using Access to develop business-critical systems.

I recently got a request to incorporate into a report data that lives in Microsoft Access.

I know how to upload an Excel file into Cognos.  I don't see a way to upload an Access file.

I see in Cognos Administration | Configuration | Data Source Connections, I can add a data source connection that uses JDBC or ODBC.  What I don't see are any examples of how to do this.  I suppose something like https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/standard-security/ may be a good place to start if creating an ODBC data source.

If I go to Manage | Data server connections | Add data server, in the Select a type list, I don't see JDBC or ODBC or Access.

What is your preferred way to use data from Microsoft Access in a Cognos report or dashboard?

Hi Doug,

Sadly there's no option to directly ingest an Access database like you would with an Excel file or csv file. The only connection option I can suggest is via ODBC, and you'll need firstly to add in the ODBC driver and connection on the Cognos server (making sure you use the 32-bit ODBC Administration console, not the 64-bit one), then set up an ODBC data source connection in Cognos using the old Administration Console. Your next challenge would then be how to model the metadata. I know for sure Framework Manager works, but I have a nasty suspicion a data module would only work with a DQM data source. One to experiment with, there.

There isn't a "generic" JDBC connection you can use in Cognos for connecting to Access, as far as I'm aware. Data Server connections are DQM-only, so there are no options to set up an ODBC connection via the Data Servers UI.

This sounds like a lot of messing around to me. Do you have alternatives, like dumping the Access data out into CSV or Excel formats and ingesting these?

Cheers!

MF.

[dougp]

dumping the Access data out into CSV or Excel formats and ingesting these

Makes sense since the whole database was developed outside IT's direction.  However, I would like to avoid to many manual steps in the reporting process.

use the 32-bit ODBC Administration console, not the 64-bit one

Did that, but I assumed that means I need the 32-bit driver also, but...

Data Server connections are DQM-only

When I create a DQM model and try to use the Metadata Wizard against this data source, I get

Code Select Expand

BMT-IMP-0016 The provider type OD is not supported in this mode.
But when I change it to CQM and try again...

Code Select Expand

QE-DEF-0285 The logon failed.
QE-DEF-0323 The DSN(ODBC)/ServiceName is invalid. Either the DSN is missing or the host is inaccessible.

But the ODBC data source tested fine in the ODBC Data Sources app.

It seems reasonable that a reporting tool can be used to connect to any data source, anywhere.  But I also realize that Access is way outside the right way to do things.

[MFGF]

Makes sense since the whole database was developed outside IT's direction.  However, I would like to avoid to many manual steps in the reporting process.
Did that, but I assumed that means I need the 32-bit driver also, but...
When I create a DQM model and try to use the Metadata Wizard against this data source, I get

Code Select Expand

BMT-IMP-0016 The provider type OD is not supported in this mode.
But when I change it to CQM and try again...

Code Select Expand

QE-DEF-0285 The logon failed.
QE-DEF-0323 The DSN(ODBC)/ServiceName is invalid. Either the DSN is missing or the host is inaccessible.

But the ODBC data source tested fine in the ODBC Data Sources app.

It seems reasonable that a reporting tool can be used to connect to any data source, anywhere.  But I also realize that Access is way outside the right way to do things.

It has been a long time since I connected Cognos to an Access database, but it shouldn't be difficult. When setting up the data source in the CA Admin console, the ODBC Data Source entry should be the name of the ODBC DSN you set up in ODBCAD32, and you'd normally have "No authentication" selected for Access.

Quick sanity-check. Is the instance of FM you're using on a different machine from the CA server instance? You'll need the ODBC driver and the ODBC DSN to be on both machines, as FM uses an embedded CQE instance to connect locally while modelling.

Cheers!

MF.

[dougp]

I agree.  It shouldn't be this hard.

Of course it's on a different machine.  FM is a client and is only available to those with Cognos Analytics Administrator licenses.  The only reason I can think of to install FM on a server is to make it available to multiple random users, unless it's to segregate Cognos system requirements from reality...

I can't install the 32-bit driver on my workstation because I have 64-bit MS Office apps installed.  (Thanks, Microsoft.)

So, I could get different results between the client and server because the data sources could be configured differently.  Sounds kinda hokey.

Apparently, I'm not getting even that far.  Testing the data source connection in Cognos Administration produces...

Code Select Expand

QE-DEF-0285 The logon failed.
...whether I use "No authentication" or "An external namespace" (in case Windows authentication is being employed in the Access file).

My ODBC connection string is blank because I figure the ODBC data source is handling that.  Is that correct?
It fails the same way if I enter a connection string.

[MFGF]

So, I could get different results between the client and server because the data sources could be configured differently.  Sounds kinda hokey.

Yep - this has always been a quirk for CQE. FM connects locally, so needs a local odbc driver and DSN/database native client/etc. It's easy to get these out of step with the ones configured on the CA server.

Apparently, I'm not getting even that far.  Testing the data source connection in Cognos Administration produces...

Code Select Expand

QE-DEF-0285 The logon failed.
...whether I use "No authentication" or "An external namespace" (in case Windows authentication is being employed in the Access file).

I never had to use authentication with Access databases, so it was always the "No authentication" option for me.

My ODBC connection string is blank because I figure the ODBC data source is handling that.  Is that correct?
It fails the same way if I enter a connection string.

All you should need is the name of the ODBC DSN you set up in the ODBC Administration panel. Nothing else.

It looks to be complaining about authentication, though (the logon failed). Are you having to set up authentication in the ODBC Administration config for the DSN, or does it test successfully in there with no authentication?

I'll dig around in my archives to see if I have any ancient access databases from back in my days as a Cognos instructor. We used them in some of the training courses back in the day. If I can find one, I'll see if I can get it connected...

Cheers!

MF.

[MFGF]

One other thought. You'll need to set up the ODBC DSN as a System DSN not a User DSN. Is this what you did?

Cheers!

MF.

[dougp]

Are you having to set up authentication in the ODBC Administration config for the DSN

No.  But I don't know how the driver works.  It may mean that it's using Windows authentication.  But since I created the DSN while logged on as the account that runs Cognos, and it tested fine while logged on as that user, I would expect Cognos to be able to access it that way.  Cognos Administration says otherwise.

You'll need to set up the ODBC DSN as a System DSN not a User DSN

That's what I did.

[MFGF]

Hi Doug,

My archive dig was successful - I found a couple of very simple Access databases. They have no authentication. I did the following:

- Launch odbcad32.exe with Admin privileges
- Go to the System DSN tab
- Press the Add button to add a new System Data Source
- Choose "Microsoft Access Driver (*.mdb)" as the driver and click Finish
- Enter a name for the data source - whatever you like. You'll need this name later on in CA.
- Use the Select button and browse to and choose your Access database file then click OK
- Click OK to the ODBC Microsoft Access Setup dialog
- Click OK to the ODBC Data Source Administrator (32-bit) dialog

- In Cognos Analytics, choose Manage > Administration console
- Go to the Configuration tab
- Use the "New Data Source" button to add a new data source
- Give your data source a name and click Next
- From the Type: dropdown, select ODBC and click Next
- In the "ODBC data source:" field, enter the name you chose when setting up the ODBC DSN above
- Select "No authentication"
- Click "Test the connection..." then click Test on the next screen

This comes back with an "ODBC / Compatible    Succeeded" message for me.

If you're unsure if your Access database has embedded authentication that's getting in the way, I can send you one of my database files to try. The one I'm testing with is a whopping 240k in size :)

Cheers!

MF.

[dougp]

Thanks for the help

*.mdb?  How old is this doc?

I have some additional wrinkles:
My Cognos server doesn't house any data.  The Access files (*.accdb) will be in folders somewhere on the network.  I was able to create a junction pointing to a folder (mklink /D local-folder-name \\servername\sharename\foldername\[\tt]) that contains the database, then use that in the ODBC data source.

I just discovered that I could not have tested this successfully in the ODBC app.  There's no Test button.

This may be a permissions issue.  I can't see how I have permission to get to the file.  So I can't tell if my Cognos service account has permission.  Pushing the questions back to the user.

[MFGF]

Thanks for the help

*.mdb?  How old is this doc?

I have some additional wrinkles:
My Cognos server doesn't house any data.  The Access files (*.accdb) will be in folders somewhere on the network.  I was able to create a junction pointing to a folder (mklink /D local-folder-name \\servername\sharename\foldername\[\tt]) that contains the database, then use that in the ODBC data source.

I just discovered that I could not have tested this successfully in the ODBC app.  There's no Test button.

This may be a permissions issue.  I can't see how I have permission to get to the file.  So I can't tell if my Cognos service account has permission.  Pushing the questions back to the user.

It's very old indeed! :)

Let me know if you want a copy of the file (for your own testing purposes).

Cheers!

MF.