473,426 Members | 1,614 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > microsoft access / vba > questions > non updateable view access 2000

Join Bytes to post your question to a community of 473,426 software developers and data experts.

Non updateable view access 2000

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.vuCIRSFILTER
AS
SELECT SoldTo, CIRSID, FLNumber, CustomerIDNumber, CustomerName,
CustomerAddress, City, State, ZIP, ContactName, ContactPhone,
ContactCell,
OnSiteContact, ContactEmail, OnSitePhone,
OnSiteCellPage, OnSiteEmail, SalesRep, Division, PM,
InstallUpgradeDate, Notes, RFASID, RFASIDVers,
RFATrans, PBXSystemType, PBXVersion,
PBXDialUpModem, PBXPassword, PBXLoginID, VoiceMailSystemType,
VoiceMailVersion,
VoiceMailDialUpModem, VoiceMailLoginID,
VoicemailPassword, CMSSystemType, CMSVersion, CMSDialUpModem,
CMSLoginID, CMSPassword,
MAPD, MAPDDialup, MAPDLogin, MAPDPassword,
CTIInformation, CSUDSUSystemType, CSUDSUVersion, CSUDSUDialUpModem,
CSUDSULoginID,
CSUDSUPassword, AdditionalEquipment,
PWMContract, SupportNotes, OE, CREATED_BY_USER, CREATED_ON_DATE,
MODIFIED_ON_DATE,
MODIFIED_BY_USER
FROM dbo.tblCIRSMain
WHERE (CREATED_BY_USER = 'company\auser')

Thanks
CREATE TABLE [dbo].[tblCIRSMain] (
[SoldTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CIRSID] [int] IDENTITY (1, 1) NOT NULL ,
[FLNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CustomerIDNumber] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CustomerAddress] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactPhone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactCell] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactEmail] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteContact] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSitePhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteCellPage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SalesRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Division] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InstallUpgradeDate] [datetime] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASIDVers] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RFATrans] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PBXSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[VoiceMailSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailVersion] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailLoginID] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoicemailPassword] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CMSSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPD] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAPDDialup] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPDLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MAPDPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CTIInformation] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSULoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AdditionalEquipment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PWMContract] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SupportNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[CREATED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATED_ON_DATE] [datetime] NOT NULL ,
[MODIFIED_ON_DATE] [datetime] NULL ,
[MODIFIED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_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_CIRSRowCreateDate] DEFAULT (getdate()) FOR
[CREATED_ON_DATE],
CONSTRAINT [DF_tblCIRSMain_CIRSRowModUser] DEFAULT ('') FOR
[MODIFIED_BY_USER],
CONSTRAINT [aaaaatblCIRSMain_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 [VoiceMailLoginID] ON
[dbo].[tblCIRSMain]([VoiceMailLoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [ix_ILNumber] ON [dbo].[tblCIRSMain]([FLNumber]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
Nov 13 '05 #1
2 1658
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 (abefuzzleduser2)
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.vuCIRSFILTER
AS
SELECT SoldTo, CIRSID, FLNumber, CustomerIDNumber, CustomerName,
CustomerAddress, City, State, ZIP, ContactName, ContactPhone,
ContactCell,
OnSiteContact, ContactEmail, OnSitePhone,
OnSiteCellPage, OnSiteEmail, SalesRep, Division, PM,
InstallUpgradeDate, Notes, RFASID, RFASIDVers,
RFATrans, PBXSystemType, PBXVersion,
PBXDialUpModem, PBXPassword, PBXLoginID, VoiceMailSystemType,
VoiceMailVersion,
VoiceMailDialUpModem, VoiceMailLoginID,
VoicemailPassword, CMSSystemType, CMSVersion, CMSDialUpModem,
CMSLoginID, CMSPassword,
MAPD, MAPDDialup, MAPDLogin, MAPDPassword,
CTIInformation, CSUDSUSystemType, CSUDSUVersion, CSUDSUDialUpModem,
CSUDSULoginID,
CSUDSUPassword, AdditionalEquipment,
PWMContract, SupportNotes, OE, CREATED_BY_USER, CREATED_ON_DATE,
MODIFIED_ON_DATE,
MODIFIED_BY_USER
FROM dbo.tblCIRSMain
WHERE (CREATED_BY_USER = 'company\auser')

Thanks
CREATE TABLE [dbo].[tblCIRSMain] (
[SoldTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CIRSID] [int] IDENTITY (1, 1) NOT NULL ,
[FLNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CustomerIDNumber] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CustomerAddress] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactPhone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactCell] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactEmail] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteContact] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSitePhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteCellPage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SalesRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Division] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InstallUpgradeDate] [datetime] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASIDVers] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RFATrans] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PBXSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[VoiceMailSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailVersion] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailLoginID] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoicemailPassword] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CMSSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPD] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAPDDialup] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPDLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MAPDPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CTIInformation] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSULoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AdditionalEquipment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PWMContract] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SupportNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[CREATED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATED_ON_DATE] [datetime] NOT NULL ,
[MODIFIED_ON_DATE] [datetime] NULL ,
[MODIFIED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_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_CIRSRowCreateDate] DEFAULT (getdate()) FOR
[CREATED_ON_DATE],
CONSTRAINT [DF_tblCIRSMain_CIRSRowModUser] DEFAULT ('') FOR
[MODIFIED_BY_USER],
CONSTRAINT [aaaaatblCIRSMain_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 [VoiceMailLoginID] ON
[dbo].[tblCIRSMain]([VoiceMailLoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

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


Nov 13 '05 #2
Steve,

Checking view -> design view shows no Key displayed.

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

CREATE INDEX Pkview_rs_tblCIRSMAIN
ON view_rs_tblCIRSMAIN (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 (abefuzzleduser2)
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.vuCIRSFILTER
AS
SELECT SoldTo, CIRSID, FLNumber, CustomerIDNumber, CustomerName,
CustomerAddress, City, State, ZIP, ContactName, ContactPhone,
ContactCell,
OnSiteContact, ContactEmail, OnSitePhone,
OnSiteCellPage, OnSiteEmail, SalesRep, Division, PM,
InstallUpgradeDate, Notes, RFASID, RFASIDVers,
RFATrans, PBXSystemType, PBXVersion,
PBXDialUpModem, PBXPassword, PBXLoginID, VoiceMailSystemType,
VoiceMailVersion,
VoiceMailDialUpModem, VoiceMailLoginID,
VoicemailPassword, CMSSystemType, CMSVersion, CMSDialUpModem,
CMSLoginID, CMSPassword,
MAPD, MAPDDialup, MAPDLogin, MAPDPassword,
CTIInformation, CSUDSUSystemType, CSUDSUVersion, CSUDSUDialUpModem,
CSUDSULoginID,
CSUDSUPassword, AdditionalEquipment,
PWMContract, SupportNotes, OE, CREATED_BY_USER, CREATED_ON_DATE,
MODIFIED_ON_DATE,
MODIFIED_BY_USER
FROM dbo.tblCIRSMain
WHERE (CREATED_BY_USER = 'company\auser')

Thanks
CREATE TABLE [dbo].[tblCIRSMain] (
[SoldTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CIRSID] [int] IDENTITY (1, 1) NOT NULL ,
[FLNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CustomerIDNumber] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CustomerAddress] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactPhone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactCell] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ContactEmail] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteContact] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSitePhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteCellPage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OnSiteEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SalesRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Division] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InstallUpgradeDate] [datetime] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFASIDVers] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RFATrans] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PBXSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PBXPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[VoiceMailSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailVersion] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoiceMailLoginID] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[VoicemailPassword] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CMSSystemType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSDialUpModem] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSLoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CMSPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPD] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAPDDialup] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MAPDLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MAPDPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CTIInformation] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUSystemType] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSUVersion] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUDialUpModem] [nvarchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CSUDSULoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CSUDSUPassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AdditionalEquipment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PWMContract] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SupportNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[CREATED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATED_ON_DATE] [datetime] NOT NULL ,
[MODIFIED_ON_DATE] [datetime] NULL ,
[MODIFIED_BY_USER] [nvarchar] (50) COLLATE
SQL_Latin1_General_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_CIRSRowCreateDate] DEFAULT (getdate()) FOR
[CREATED_ON_DATE],
CONSTRAINT [DF_tblCIRSMain_CIRSRowModUser] DEFAULT ('') FOR
[MODIFIED_BY_USER],
CONSTRAINT [aaaaatblCIRSMain_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 [VoiceMailLoginID] ON
[dbo].[tblCIRSMain]([VoiceMailLoginID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

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

Nov 13 '05 #3
New Post

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
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...
Microsoft Access / VBA
3
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...
Microsoft Access / VBA
4
"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 =...
ASP.NET
17
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...
ASP.NET
2
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...
Visual Basic .NET
11
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...
DB2 Database
11
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...
ASP.NET
4
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)....
Microsoft SQL Server
1
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...
Microsoft Access / VBA
0
marktang
What is ONU?
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
General
0
Oralloy
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,...
C / C++
0
jinu1996
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...
Online Marketing
1
The easy way to turn off automatic updates for Windows 10/11
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
Windows Server
0
tracyyun
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...
General
0
agi2029
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,...
Career Advice
0
isladogs
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...
Microsoft Access / VBA
0
Trying to create a lan-to-lan vpn between two differents networks
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
Networking - Hardware / Configuration
0
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!