July 6, 2011 at 9:03 am
Hello,
Sorry in advance if this seems more like an Access issue than a SQL server issue. I've posted
in Access forums, but no luck there.
A user has a role on SQL Server 2008 to SELECT from tables (no update, insert, or delete).
She would like to work with the data through an Access 2007 front end.
She opens Access, creates a blank database, clicks External Data, clicks ODBC database, creates an ODBC connection using
SQL Server Native Client 10.0. She goes through the wizard and at the end, clicks Test Connection and
gets a Success message. Her SQL Server role is assigned to her windows login. She signs on to SQL Server with
windows authentication, not a SQL Server ID. In Access, it's the same: the DSN (SQL Server Native Client 10.0)
was set up with Integrated Windows authentication.
She then tries to link to one of the SQL Server tables and gets "The
Microsoft Access database engine could not find the object <name>. Make sure the object exists
and that you spell its name and the path name correctly. (Error 3011)."
I have SELECT, UPDATE, INSERT, and DELETE access to the SQL Server table. I do the exact same thing and it works.
She tried the same thing in Access with all the SQL Server ODBC drivers: SQL Native Client, SQL Server,
and SQL Server Native Client 10.0. No luck. She is able to import perfectly from Excel using her read-only login,
so I'm thinking there's a bug in the Access Drivers. Or, there's an undocumented reason why Access 2007 won't let
me link or copy read-only tables from SQL Server 2008.
This is not specific to the user, by the way. We are all running Windows 7 Enterprise
on our laptops with Access 2007. Any of us who are given read-only permissions on the
SQL Server 2008 tables can duplicate the problem and any of us with full read/write (select,insert,update,delete)
permissions can import w/o the error message above. And as I mentioned, Excel's import works like a charm.
My problem is that I want to maintain some control over the data on the back end (SQL Server 2008 - I want
to keep the SQL Server roles if possible), but the users want to use Access (not Excel) as a front end.
Thanks,
Seth
July 7, 2011 at 7:30 am
I think this is the issue which led immediately to its resolution. Here is the definition of a typical table on my SQL Server box:
[font="Courier New"]CREATE TABLE Request_Activity
(
XI_master_id bigint NOT NULL,
request_date datetime NULL,
last_user_to_modify last_user_to_modify_type,
last_modification_time last_modification_time_type
)[/font]
The last two columns are user-defined types. A user or role on SQL Server must have explicit permission on user-defined types in order to see them. Once I granted permission on the user-defined types, the problem disappeared. Nothing to do with DSNs or connectivity. Not an Access issue particularly, except for the generally misleading error message.
July 10, 2011 at 10:09 am
What permissions does the Account that was used to create the DSN?
When you created the DSN did you select the default database as the on with the table that you are trying to link? What happened when you tried to test the connection?
If you provide her with your version of the Access Database can she open the table?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply