Non updateable view access 2000 - Microsoft Access / VBA

abefuzzleduser2

I am using sql 2000 (mdb) with linked tables on SQL 2000. I need to
use views to limit users records, instead of direct table access. I
setup a single talbe view in sql and tested update and inserting the
view using query analyzer. But when I add that view as a "linked"
table in access (windows db security dsn-less). Access does not allow
me to edit the data from view. Table has Pk have tried indexing view
but we use several ntext fields which does not work for clustered
index

Have tried creating view "with schemabinding" and "with viewmetadata"

Can not get this to work. Is it the lack of index on view?

REATE VIEW dbo.vuCIRSFILTE R
AS
SELECT SoldTo, CIRSID, FLNumber, CustomerIDNumbe r, CustomerName,
CustomerAddress , City, State, ZIP, ContactName, ContactPhone,
ContactCell,
OnSiteContact, ContactEmail, OnSitePhone,
OnSiteCellPage, OnSiteEmail, SalesRep, Division, PM,
InstallUpgradeD ate, Notes, RFASID, RFASIDVers,
RFATrans, PBXSystemType, PBXVersion,
PBXDialUpModem, PBXPassword, PBXLoginID, VoiceMailSystem Type,
VoiceMailVersio n,
VoiceMailDialUp Modem, VoiceMailLoginI D,
VoicemailPasswo rd, CMSSystemType, CMSVersion, CMSDialUpModem,
CMSLoginID, CMSPassword,
MAPD, MAPDDialup, MAPDLogin, MAPDPassword,
CTIInformation, CSUDSUSystemTyp e, CSUDSUVersion, CSUDSUDialUpMod em,
CSUDSULoginID,
CSUDSUPassword, AdditionalEquip ment,
PWMContract, SupportNotes, OE, CREATED_BY_USER , CREATED_ON_DATE ,
MODIFIED_ON_DAT E,
MODIFIED_BY_USE R
FROM dbo.tblCIRSMain
WHERE (CREATED_BY_USE R = 'company\auser' )

Thanks
CREATE TABLE [dbo].[tblCIRSMain] (
[SoldTo] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[CIRSID] [int] IDENTITY (1, 1) NOT NULL ,
[FLNumber] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[CustomerIDNumbe r] [nvarchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CustomerAddress] [nvarchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (15) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ContactName] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ContactPhone] [nvarchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ContactCell] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ContactEmail] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSiteContact] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSitePhone] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSiteCellPage] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSiteEmail] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[SalesRep] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[Division] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[PM] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[InstallUpgradeD ate] [datetime] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[RFASID] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[RFASIDVers] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[RFATrans] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[PBXSystemType] [nvarchar] (25) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXVersion] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXLoginID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[VoiceMailSystem Type] [nvarchar] (25) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[VoiceMailVersio n] [nvarchar] (10) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[VoiceMailDialUp Modem] [nvarchar] (20) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[VoiceMailLoginI D] [nvarchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[VoicemailPasswo rd] [nvarchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CMSSystemType] [nvarchar] (25) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSVersion] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSLoginID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[MAPD] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[MAPDDialup] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[MAPDLogin] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[MAPDPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CTIInformation] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CSUDSUSystemTyp e] [nvarchar] (25) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CSUDSUVersion] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CSUDSUDialUpMod em] [nvarchar] (20) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CSUDSULoginID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CSUDSUPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[AdditionalEquip ment] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PWMContract] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[SupportNotes] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[OE] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[CREATED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[CREATED_ON_DATE] [datetime] NOT NULL ,
[MODIFIED_ON_DAT E] [datetime] NULL ,
[MODIFIED_BY_USE R] [nvarchar] (50) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [CIRS_SOLDTO] ON
[dbo].[tblCIRSMain]([SoldTo]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCIRSMain] ADD
CONSTRAINT [DF_tblCIRSMain_ (suser_sname())] DEFAULT (suser_sname())
FOR [CREATED_BY_USER],
CONSTRAINT [DF_tblCIRSMain_ CIRSRowCreateDa te] DEFAULT (getdate()) FOR
[CREATED_ON_DATE],
CONSTRAINT [DF_tblCIRSMain_ CIRSRowModUser] DEFAULT ('') FOR
[MODIFIED_BY_USE R],
CONSTRAINT [aaaaatblCIRSMai n_PK] PRIMARY KEY NONCLUSTERED
(
[SoldTo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CMSLoginID] ON [dbo].[tblCIRSMain]([CMSLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CSU/DSULoginID] ON
[dbo].[tblCIRSMain]([CSUDSULoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [PBXLoginID] ON [dbo].[tblCIRSMain]([PBXLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFASID] ON [dbo].[tblCIRSMain]([RFASID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [VoiceMailLoginI D] ON
[dbo].[tblCIRSMain]([VoiceMailLoginI D]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [ix_ILNumber] ON [dbo].[tblCIRSMain]([FLNumber]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

Nov 13 '05 #1

Subscribe Reply

1664

Steve Jorgensen

With an MDB, Access must be told which field or fields in the link comprise
the primary key. This can be done when you create the link through the menu,
and Access asks you to select the key fields, or on an existing link, by
executing an Access DDL query to "create" the "primary key" on the link. If
this has been done successfully, you should see the key symbol next to the
field or fields when you open the link in design view.

On 1 Sep 2004 11:56:22 -0700, ab************* @yahoo.com (abefuzzleduser 2)
wrote:

I am using sql 2000 (mdb) with linked tables on SQL 2000. I need to
use views to limit users records, instead of direct table access. I
setup a single talbe view in sql and tested update and inserting the
view using query analyzer. But when I add that view as a "linked"
table in access (windows db security dsn-less). Access does not allow
me to edit the data from view. Table has Pk have tried indexing view
but we use several ntext fields which does not work for clustered
index

Have tried creating view "with schemabinding" and "with viewmetadata"

Can not get this to work. Is it the lack of index on view?

REATE VIEW dbo.vuCIRSFILTE R
AS
SELECT SoldTo, CIRSID, FLNumber, CustomerIDNumbe r, CustomerName,
CustomerAddres s, City, State, ZIP, ContactName, ContactPhone,
ContactCell,
OnSiteContact, ContactEmail, OnSitePhone,
OnSiteCellPage , OnSiteEmail, SalesRep, Division, PM,
InstallUpgrade Date, Notes, RFASID, RFASIDVers,
RFATrans, PBXSystemType, PBXVersion,
PBXDialUpModem , PBXPassword, PBXLoginID, VoiceMailSystem Type,
VoiceMailVersi on,
VoiceMailDialUp Modem, VoiceMailLoginI D,
VoicemailPassw ord, CMSSystemType, CMSVersion, CMSDialUpModem,
CMSLoginID, CMSPassword,
MAPD, MAPDDialup, MAPDLogin, MAPDPassword,
CTIInformation , CSUDSUSystemTyp e, CSUDSUVersion, CSUDSUDialUpMod em,
CSUDSULoginI D,
CSUDSUPassword, AdditionalEquip ment,
PWMContract, SupportNotes, OE, CREATED_BY_USER , CREATED_ON_DATE ,
MODIFIED_ON_DA TE,
MODIFIED_BY_USE R
FROM dbo.tblCIRSMain
WHERE (CREATED_BY_USE R = 'company\auser' )

Thanks
CREATE TABLE [dbo].[tblCIRSMain] (
[SoldTo] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[CIRSID] [int] IDENTITY (1, 1) NOT NULL ,
[FLNumber] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[CustomerIDNumbe r] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CustomerAddress] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (15) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ContactName] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ContactPhone] [nvarchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ContactCell] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ContactEmail] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSiteContact] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSitePhone] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSiteCellPage] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSiteEmail] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[SalesRep] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[Division] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[PM] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[InstallUpgradeD ate] [datetime] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[RFASID] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[RFASIDVers] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[RFATrans] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[PBXSystemType] [nvarchar] (25) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXVersion] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXLoginID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[VoiceMailSystem Type] [nvarchar] (25) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[VoiceMailVersio n] [nvarchar] (10) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[VoiceMailDialUp Modem] [nvarchar] (20) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[VoiceMailLoginI D] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[VoicemailPasswo rd] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[CMSSystemType] [nvarchar] (25) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSVersion] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSLoginID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[MAPD] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[MAPDDialup] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[MAPDLogin] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[MAPDPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CTIInformation] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CSUDSUSystemTyp e] [nvarchar] (25) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[CSUDSUVersion] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CSUDSUDialUpMod em] [nvarchar] (20) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[CSUDSULoginID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CSUDSUPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[AdditionalEquip ment] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PWMContract] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[SupportNotes] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[OE] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[CREATED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NOT NULL ,
[CREATED_ON_DATE] [datetime] NOT NULL ,
[MODIFIED_ON_DAT E] [datetime] NULL ,
[MODIFIED_BY_USE R] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [CIRS_SOLDTO] ON
[dbo].[tblCIRSMain]([SoldTo]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCIRSMain] ADD
CONSTRAINT [DF_tblCIRSMain_ (suser_sname())] DEFAULT (suser_sname())
FOR [CREATED_BY_USER],
CONSTRAINT [DF_tblCIRSMain_ CIRSRowCreateDa te] DEFAULT (getdate()) FOR
[CREATED_ON_DATE],
CONSTRAINT [DF_tblCIRSMain_ CIRSRowModUser] DEFAULT ('') FOR
[MODIFIED_BY_USE R],
CONSTRAINT [aaaaatblCIRSMai n_PK] PRIMARY KEY NONCLUSTERED
(
[SoldTo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CMSLoginID] ON [dbo].[tblCIRSMain]([CMSLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CSU/DSULoginID] ON
[dbo].[tblCIRSMain]([CSUDSULoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [PBXLoginID] ON [dbo].[tblCIRSMain]([PBXLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFASID] ON [dbo].[tblCIRSMain]([RFASID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [VoiceMailLoginI D] ON
[dbo].[tblCIRSMain]([VoiceMailLoginI D]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [ix_ILNumber] ON [dbo].[tblCIRSMain]([FLNumber]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

Nov 13 '05 #2

abefuzzleduser2

Steve,

Checking view -> design view shows no Key displayed.

Open a new query, and try running this Access DDL ok

CREATE INDEX Pkview_rs_tblCI RSMAIN
ON view_rs_tblCIRS MAIN (SOLDTO)
WITH PRIMARY;

I was then able to update the view!

I am going to work on multi table views.

Thanks Steve!

Steve Jorgensen <no****@nospam. nospam> wrote in message news:<pj******* *************** **********@4ax. com>...

With an MDB, Access must be told which field or fields in the link comprise
the primary key. This can be done when you create the link through the menu,
and Access asks you to select the key fields, or on an existing link, by
executing an Access DDL query to "create" the "primary key" on the link. If
this has been done successfully, you should see the key symbol next to the
field or fields when you open the link in design view.

On 1 Sep 2004 11:56:22 -0700, ab************* @yahoo.com (abefuzzleduser 2)
wrote:
I am using sql 2000 (mdb) with linked tables on SQL 2000. I need to
use views to limit users records, instead of direct table access. I
setup a single talbe view in sql and tested update and inserting the
view using query analyzer. But when I add that view as a "linked"
table in access (windows db security dsn-less). Access does not allow
me to edit the data from view. Table has Pk have tried indexing view
but we use several ntext fields which does not work for clustered
index

Have tried creating view "with schemabinding" and "with viewmetadata"

Can not get this to work. Is it the lack of index on view?

REATE VIEW dbo.vuCIRSFILTE R
AS
SELECT SoldTo, CIRSID, FLNumber, CustomerIDNumbe r, CustomerName,
CustomerAddres s, City, State, ZIP, ContactName, ContactPhone,
ContactCell,
OnSiteContact, ContactEmail, OnSitePhone,
OnSiteCellPage , OnSiteEmail, SalesRep, Division, PM,
InstallUpgrade Date, Notes, RFASID, RFASIDVers,
RFATrans, PBXSystemType, PBXVersion,
PBXDialUpModem , PBXPassword, PBXLoginID, VoiceMailSystem Type,
VoiceMailVersi on,
VoiceMailDialUp Modem, VoiceMailLoginI D,
VoicemailPassw ord, CMSSystemType, CMSVersion, CMSDialUpModem,
CMSLoginID, CMSPassword,
MAPD, MAPDDialup, MAPDLogin, MAPDPassword,
CTIInformation , CSUDSUSystemTyp e, CSUDSUVersion, CSUDSUDialUpMod em,
CSUDSULoginI D,
CSUDSUPassword, AdditionalEquip ment,
PWMContract, SupportNotes, OE, CREATED_BY_USER , CREATED_ON_DATE ,
MODIFIED_ON_DA TE,
MODIFIED_BY_USE R
FROM dbo.tblCIRSMain
WHERE (CREATED_BY_USE R = 'company\auser' )

Thanks
CREATE TABLE [dbo].[tblCIRSMain] (
[SoldTo] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[CIRSID] [int] IDENTITY (1, 1) NOT NULL ,
[FLNumber] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[CustomerIDNumbe r] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CustomerAddress] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (15) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[ContactName] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ContactPhone] [nvarchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ContactCell] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[ContactEmail] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSiteContact] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSitePhone] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSiteCellPage] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[OnSiteEmail] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[SalesRep] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[Division] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[PM] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[InstallUpgradeD ate] [datetime] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[RFASID] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[RFASIDVers] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[RFATrans] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[PBXSystemType] [nvarchar] (25) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXVersion] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXLoginID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PBXPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[VoiceMailSystem Type] [nvarchar] (25) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[VoiceMailVersio n] [nvarchar] (10) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[VoiceMailDialUp Modem] [nvarchar] (20) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[VoiceMailLoginI D] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[VoicemailPasswo rd] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[CMSSystemType] [nvarchar] (25) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSVersion] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSLoginID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CMSPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[MAPD] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[MAPDDialup] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[MAPDLogin] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[MAPDPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CTIInformation] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[CSUDSUSystemTyp e] [nvarchar] (25) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[CSUDSUVersion] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CSUDSUDialUpMod em] [nvarchar] (20) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
[CSUDSULoginID] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[CSUDSUPassword] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[AdditionalEquip ment] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[PWMContract] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[SupportNotes] [ntext] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[OE] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[CREATED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NOT NULL ,
[CREATED_ON_DATE] [datetime] NOT NULL ,
[MODIFIED_ON_DAT E] [datetime] NULL ,
[MODIFIED_BY_USE R] [nvarchar] (50) COLLATE
SQL_Latin1_Gen eral_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [CIRS_SOLDTO] ON
[dbo].[tblCIRSMain]([SoldTo]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCIRSMain] ADD
CONSTRAINT [DF_tblCIRSMain_ (suser_sname())] DEFAULT (suser_sname())
FOR [CREATED_BY_USER],
CONSTRAINT [DF_tblCIRSMain_ CIRSRowCreateDa te] DEFAULT (getdate()) FOR
[CREATED_ON_DATE],
CONSTRAINT [DF_tblCIRSMain_ CIRSRowModUser] DEFAULT ('') FOR
[MODIFIED_BY_USE R],
CONSTRAINT [aaaaatblCIRSMai n_PK] PRIMARY KEY NONCLUSTERED
(
[SoldTo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CMSLoginID] ON [dbo].[tblCIRSMain]([CMSLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [CSU/DSULoginID] ON
[dbo].[tblCIRSMain]([CSUDSULoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [PBXLoginID] ON [dbo].[tblCIRSMain]([PBXLoginID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFASID] ON [dbo].[tblCIRSMain]([RFASID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [VoiceMailLoginI D] ON
[dbo].[tblCIRSMain]([VoiceMailLoginI D]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [ix_ILNumber] ON [dbo].[tblCIRSMain]([FLNumber]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

Nov 13 '05 #3

Similar topics

8725

Help wanted: 'Recordset not updateable'

by: ren | last post by:

Hello, When I open with Access 2000 a .DBF table and try to modify the content of a (random) record, I get the message 'This recordset is not updateable". And indeed, I can't change the content nor delete it. However, when opening the same .DBF tabel with Access 2000, though installed on another computer, I can change the content or...

Microsoft Access / VBA

17488

This Recordset is not Updateable Problem

by: jm | last post by:

I have a simple query. It has two tables. If I open the tables separately, I can add records. If I open them up together, the recordset is not updateable. As best I know, this has never been a problem it just happened all of a sudden. - The database has been repaired and converted several times to no avail. - All the permissions are...

Microsoft Access / VBA

6494

"Operation must use an updateable query" on ExecuteNonQuery()

by: MDW | last post by:

Hey all. I'm confused. I'm trying to add a single record into an Access 2000 database using ASP.Net. Here is the code: objConn = New OleDbConnection(strConnect) objConn.Open objCommand = New OleDbCommand("INSERT INTO LOGIN_MASTER (LOGIN_ID, PWD, F_NAME, L_NAME, TYPE_ID) VALUES ('" & strEmail & "','" & strPwd & "','" &

ASP.NET

1529

Must Use Updateable Query

by: Michael C# | last post by:

Question - I keep getting the above error when I try to run an INSERT query against an Access database. I'm running XP Professional and VS.NET 2003. I run SELECT queries from ASP.NET against it all day long, and when I run the INSERT query inside Access itself, it works fine. But for some reason when I run it from within ASP.NET, I get the...

ASP.NET

7503

Question: Operation Must Use an Updateable Query

by: SheryMich | last post by:

Hi - I am having a bit of a problem with the insert into a database. When I go to insert a record into an un-keyed, single table Access database, I get the aforementioned ''Operation Must Use an Updateable Query' error. Points: - The database is in my local temp directory, not inetpub - The directory has read/write permissions - The...

Visual Basic .NET

3103

QMF for Windows and Updateable Views

by: JC | last post by:

I created an updateable view, which joins two tables, in a DB2 UDB database. The view was made updateable by the creation of an "instead of" trigger. I'd like to use this view for updates via QMF for Windows or DB2 Table Editor. However, when I try to update from either of these tools, I receive an SQL0150 error. Is there anything I can do...

DB2 Database

3937

Operation must use an updateable query

by: Arpan | last post by:

I have always been working with SQL Server 2005 for ASP.NET apps but due to some reasons, had to revert back to MS-Access 2000. When I try to insert/update a MS-Access DB table (MDB), ASP.NET generates the following error: Operation must use an updateable query. pointing to a line that says

ASP.NET

4956

views not updateable

by: othellomy | last post by:

How do I make a view non updateable? I want to create a view so that I will not be able to update, insert or delete the view so that base table is not affected (except dropping the view itself). Thanks.

Microsoft SQL Server

1424

Time key is not updateable!! HELP!!

by: kia.martin | last post by:

I have a sql database connected with access, but when I try to run macros which basically just transfer information from tables for approval an error comes up with "time key is not updateable". I have found a quick fix in which I can delete the time key from the table and everything will run fine, but when end users try to change records it...

Microsoft Access / VBA

7416

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...

Windows Server

7676

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...

C / C++

7932

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...

Online Marketing

7776

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...

General

6001

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...

Career Advice

5347

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...

Microsoft Access / VBA

4965

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

C# / C Sharp

3456

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1905

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp