Modifying Table through Access in different Schema on SQL 2005

D

Dave_TakaraCo

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top