asked on

Problem creating data sources in MS Reporting Services

Hi,
I have a question about creating data sources using ODBC connections in MS Reporting Services. The database I need to connect to is the front-end Access database. The front-end db has a number to link tables to the back-end db and also other linked dbases. When I attempt to create my data source to the front end I receive error:

ERROR[42000][Microsoft][ODBC Microsoft Access Driver] You do not have necessary permissions to use the '(unknown)' object. Have your system administrator or the person who created this object establish the appropriate permissions for you.

I can establish a data source connection for the back end database with no problem. Is there an issue with creating ODBC Data sources to front-end Access databases in Reporting Services?

ASKER CERTIFIED SOLUTION

Vadim Rapp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Yurich

Of course it's possible. You can connect to any ODBC database from MS Reporting Services.

You may have this situation because your instance has been set in Data Sources using some credentials and now you can't connect to it as you don't provide them for your data source.

To check if your MS RS is working fine with MS Access databases, create a very simple basic database in MS Access yourself (one table, a few rows of data), no passwords, no anything. And create connection to it from MS RS. It should be just working fine. If it does, you can try to look in your data source if you have any specific settings for accessing your database, or see your DB Administrator or System Administrator, they may have information necessary for you.

Another thing, MS RS works best with MS SQL, so what you can try doing is to export data from MS Access to MS SQL, the way to do it will be different for MS SQL 2000 and MS SQL 2005, but it's not very hard to do. Then, if your MS Access databse is still active (it's getting updated etc.), you can do daily updates and keep your data up to date, or if it's static, you can just use your new MS SQL database.

Good luck,
Yurich

Jeffrey Coachman

keicy01,

Also,

Open the front end db and see if you can "C.R.U.D." the records:
Create
Read
Update
Delete

At least this migt tell you where the problem is.

JeffCoachman

SOLUTION

dmitryz6

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

When you think about it, it should not be possible. If I own the back end and give someone with a front end access to my table(s), it should not be possible for the front end owner to open up the linked tables to a third party - without the back end owner's knowledge. If you want access to my tables, you have to get it from me, not another party with whom I have allowed a link or links.

Nosterdamus

keicy01,

Just a shot in the dark...

I recall seeing an Error 42000 that was related to using a reserved key-word(s) as field name(s). Check if there is any possibility that one (of more) of your fields is using either a RS or an Access reserved word(s). If you have any doubts, just change the field name(s) to something else, e.g. :"ThisCantBeReservedWord"...

HTH

Nosterdamus

keicy01

ASKER

Thanks for all the suggestions. I will give these a try and get back to you shortly.

Yurich

I'm still interested

GRayL

>I will give these a try and get back to you shortly.< - and??

SOLUTION

Vadim Rapp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

I know, but the asker made the statement - http:#a20814803