Modifying Table through Access in different Schema on SQL 2005
- From: Dave_TakaraCo <Dave_TakaraCo@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 21 Apr 2021 10:35:02 -0700
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
'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
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.... :)
- Prev by Date: 66
- Next by Date: Re: Using an unbound form for displaying and updating data
- Previous by thread: 66
- Next by thread: MSSQL Restore Error: The system database cannot be moved by RESTORE