Trouble create Data Source in MS Reporting Services

Hi,
I am trying to create a data source in MS Reporting Services that uses an ODBC connection to an Access database.  When I test the connection, I receive the error 42000 ODBC Microsoft Access Driver. You do not have the necessary permissions to use the '(unknown)' object.  Is there some permissions that I need to check on the Access database?  I am able to set up test connections on some other Access databases on the same server with no problem.
keicy01Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim P.Connect With a Mentor Commented:
You pretty much need to go to the source tables and not go through an intermediate  access database.
0
 
Jim P.Commented:
Does the SQL Server Services userid have permission to that folder?
Is it on the local server or on a remote server?
Are you using UNC or mapped drives?
0
 
keicy01Author Commented:
I believe that the SQL Server Services userid does have permission.  I can establish a Data Source that connects to another Access database located in the same folder without any error.  I am trying to connect to a remote server.  And I am currently using mapped drives.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Jim P.Commented:
The SQL Services don't recognize mapped drives.

If you are logged on when you create the ODBC call then the system recognizes your mapped drives.

But when MSSQLSERVER or SQLSERVERAGENT services tries to access the R: drive that you mapped it isn't there.

Try changing it to \\MyRemoteSrvr\MyShare\MyAccDB.mdb in the ODBC call and see how it works.
0
 
keicy01Author Commented:
When I set up my ODBC Connection in Control Panel, I do not see how I can use the UNC path instead of my mapped drive.  The ODBC Setup just pulls in my mapped drives and i can't change it to the UNC path.  In order to set up the data source in SQL Reporting Services, I would need the ODBC Connection to use the UNC path right?  There isn't a way to change the path within SQL Reporting Services when I am creating my data source??
0
 
Jim P.Commented:
Go to a windows explorer window. Go to Tools --> Folder Options --> View tab and make sure it is set to "Display full path in the address bar." is on.

Then explore to the the path and copy copy the UNC path from there. Or just type it in to the Database name window. Hit enter and it should show you the files there. Select it and you should be good.
0
 
keicy01Author Commented:
i will give this a try. thanks for all your help.
0
 
keicy01Author Commented:
I have confirmed that the "Display full path in the address bar" is on. But I am able to use the UNC path in the Data Source Wizard, it automatically defaults back to my mapped drive for the database.  Plus, I am able to create a Data Source connecting to another database in the same folder using the mapped drive, so I am unsure as to why I can connect to one and not the other.  Could there be a permissions issue on the Access database itself that is not allowing me to connect?
0
 
Jim P.Commented:
If you're using the Access Driver, below is what the images should look like.

If I'm on the wrong path, please let me know.
01-Select-Driver.jpg
03-Select-Path.jpg
04-Appearance.jpg
0
 
keicy01Author Commented:
Okay, I was able to set up the new data source using the UNC path, Thanks...

However, when I try to create a shared data source using the newly created data source, i still get the error [42000][Microsoft][ODBC Microsoft access Driver] You do not have permissions to view the '(unknown)' object.  Have your system administrator or the person who created tis object establish the appropriate permissions for you.  

In the Connection properties window of the Data Source wizard, I am specifying the the login and password for the database.
0
 
Jim P.Commented:
You have User Level Security on the database?

Does the other database have it?

Access security is a headache. I generally don't use it.  I'm not sure how to work around it, off-hand. Let me put a call out for some other experts to look at the question.
0
 
keicy01Author Commented:
Yes, the databases both have user level security.  I compared the two and they appeared to have the same settings, but I may have missed something.  I will take another look in the meantime.

Thanks!
0
 
Jim P.Commented:
While editing the registry is not for novices, another possibility to confirm that they are essentially the same is to go into the registry (regedit) and look at the contents of the keys directly. It will be under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MyODBCCall.

You are making a System DSN for the new one.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
If you're using a secured database, then you'd need to select "Database" in the last screen, then select the Database option and set your values there ... you'd need to include the full path to the workgroup file and (I believe) you can also enter the username and password, if needed.
0
 
keicy01Author Commented:
LSMConsulting,
I did the above recommended steps, and now I am receiving error HY000 Operation not supported on linked tables.  The Access database that I am trying to connect to does have linked tables.  Will that be a issue with creating my Reporting Services Data source?
0
 
keicy01Author Commented:
Thanks!
This creates another dilema for me because the database that I am trying to connect to has a number of linked tables to different databases.  I truely do appreciate all of your help.
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
All Courses

From novice to tech pro — start learning today.