Access 97 SR 2 Front End
SQL Server 7.00.1063 Back End
When I attempt to relink the tables in my Access app. I am suddenly
getting this weird error message = "ODBC--Call Failed", followed by
"[Microsoft][ODBC SQL Server Driver]Restricted Data Type Attribute
Violation(#0)". This problem did not manifest last time I tried a few
weeks ago, and so far as I know there have been no software changes
such as Service Packs etc.
The error occurs when attempting to link a view. Here is the script
for the tables and the view siipl_DoesNotWork:
==================================
CREATE TABLE [dbo].[tblBOMEntries] (
[fldID] [int] IDENTITY (1, 1) NOT NULL ,
[fldCMMPartID] [int] NULL ,
[fldSIPartNo] [varchar] (20) NULL ,
[fldDescription] [varchar] (50) NULL ,
[fldQtyAssy] [int] NULL ,
[fldItemNo] [int] NULL ,
[fldOnIPL] [bit] NOT NULL ,
[fldRefDesig] [text] NULL ,
[fldParentID] [int] NULL ,
[fldBOMLevel] [int] NULL ,
[sys_ts] [timestamp] NULL ,
[fldObsDate] [varchar] (15) NULL ,
[fldEffDate] [varchar] (15) NULL ,
[fldItemRevNo] [int] NULL ,
[fldModNo] [varchar] (10) NULL ,
[fldNewBOMFlag] [smallint] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblCMMIndex] (
[fldCMMPartID] [int] IDENTITY (1, 1) NOT NULL ,
[fldMainDescription] [varchar] (50) NULL ,
[fldSIPartNo] [varchar] (50) NULL ,
[fldBOMRevision] [varchar] (5) NULL ,
[fldBOMAttached] [bit] NOT NULL ,
[fldBOMDate] [datetime] NULL ,
[fldCMMID] [int] NULL ,
[sys_ts] [timestamp] NULL ,
[fldLink] [int] NULL ,
[fldNewBOMRevision] [varchar] (5) NULL ,
[fldNewBOMAttached] [bit] NOT NULL ,
[fldNewBOMDate] [datetime] NULL ,
[fldSiteID] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
setuser N'siipl'
GO
CREATE VIEW siipl.DoesNotWork
AS
SELECT dbo.tblBOMEntries.*,
dbo.tblCMMIndex.fldCMMPartID AS Expr1
FROM dbo.tblCMMIndex INNER JOIN
dbo.tblBOMEntries ON
dbo.tblCMMIndex.fldCMMPartID = dbo.tblBOMEntries.fldCMMPartID
================================
However, if I create a new view based on exactly the same SQL as
DoesNotWork I don't get the same error message:
CREATE VIEW dbo.DoesWork
AS
SELECT dbo.tblBOMEntries.*, dbo.tblCMMIndex.fldCMMPartID AS Expr1
FROM dbo.tblCMMIndex INNER JOIN
dbo.tblBOMEntries ON
dbo.tblCMMIndex.fldCMMPartID = dbo.tblBOMEntries.fldCMMPartID
The only difference that I can deduce between siipl.DoesNotWork and
dbo.DoesWork is the ownership. Can that be the problem? If so, why?
TIA
Edward