Modifying Table through Access in different Schema on SQL 2005

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance



Using Access 2007 ADP
Connect to SQL 2005 db

In SQL 2005 SSMS I created a new schema named SALES for example,
then used ALTER SCHEMA <SchemaName> TRANSFER dbo.<TableName>; to transfer
the schema to the newly created one.

Am doing this to simplify user permissions on tables e.g. I can assign a
Sales login to the Sales schema and I'm done.

I can modify the table in SMSS fine, but if I try and modify the table (e.g.
add a new column) in Access I get the following error when I attempt to save
the table:

'tblSALESCurrency (Sales)' table
- Unable to modify table.
ADO error: Object is invalid. Extended properties are not permitted on
'Sales.tblSALESCurrency.df', or the object does not exist.

I am aware of the problem when creating extended properties in SMSS like a
description when creating a new column, then deleting that column before a
save, and then trying to save, but this problem doesn't seem to apply to
access.

Just wondering how to use different schema's in access, how to fix this
issue, and if anyone else is using schemas and how they're doing it.... :)

TIA
dave
.



Relevant Pages

  • Re: Differences between SSMS Express & SSMS 2005 other editions
    ... the version of SSMS I have displays "Properties" when a selected ... I'm using Microsoft Press's "SQL Server 2005 ... table object in Object View should display a "Modify" choice according ... If you don't understand what it takes to change a table schema ...
    (microsoft.public.sqlserver.tools)
  • Re: Query Help
    ... "attribute splitting"; it means that you have taken an attribute (in ... with monthly sales report folders. ... Of course, the schema will ... Get some help from an experienced data modeler and throw out what you ...
    (microsoft.public.sqlserver.programming)
  • Re: User Defined Function: Convert String value of Table to Table Object
    ... What are the LOGICAL differences in this year's sales and last year's ... Why are they **totally different** entities in the schema? ... we keep separate PHYSICAL files on magnetic tapes based ...
    (microsoft.public.sqlserver.programming)
  • Re: basic questions
    ... Better is for each user to have a default schema that makes sense for each ... the AdventureWorks database creates a Sales schema. ... good overview that defines the basics. ... I've always found SQL security a bit ...
    (microsoft.public.sqlserver.security)
  • Re: schemas
    ... "A schema is a collection of database objects". ... Clearly, this isn't true in SQL Server, ... stored procedures that relate to sales go in the Sales schema etc). ...
    (microsoft.public.sqlserver.server)