Avatar of patrickmiller
Flag for United States of America asked on

How do I setup a table in SQL 2008 to be read only for ODBC connection in Access 2007

I have a single table that I do not want the users to be able to update.  Only to view.   They currently view in with the use of an Access 2007 database and an ODBC connection.     I am not a SQL admin and I don't have one on Staff but I do know how to get around the SMSS.    The user is MTCNArchive and they are currently setup on the SQL Server under user mapping with db_datareader.   I setup the ODBC connection called MTCN and tested the connection and it worked fine.   In my attempt to link table with MTCN in Access and using MTCNArchive user login and password I get an error that says "The Microsoft Office Access Database engine could not find the object ''dbo_RECV'.   Make sure the object exists and that you spell its name and the path name correctly.      If I use an ODBC connection and use the sa login and password it works fine but the user can change the data, which is not fine.   Suggestions.
Microsoft SQL Server 2008Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

You could create a view.  The view will be not-updateable unless you specify a unique identifier when you link it.  It's not the same as what you are looking for but I'm not a DBA either.

Also, keep in mind that if any table in a join is not updateable, the whole query will be not updateable so you won't be able to join to this view in any query you need to be updateable.

This is an sql user configuration issue.  Something is not set to give enough access, likely to the specific database dbo_RECV.  If you attach images of your security settings, I'm sure one of us can find the issue.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

For safety sake, you should also check "db_denydatawriter", but maybe I'm just paranoid :-)

IF dbo_RECV is a table, then it likely should be referenced as dbo.RECV
In most cases you can skip the dbo. portion entirely as that is the default table set.