T
troy
I have been workng on this for 1 1/2 weeks for which I have brought TO our IT
department for help also. No solution as of yet so I thought the experts
would know the problem? So here is the challange.
I am producing an update query with 4 source tbls (see names below)
1. Product (linked to Dev Target tbl)
2. Document (Linked to Obligation tbl)
3. ProductVersion (linked to Product tbl)
4. Obligation Type (linked to Product tbl)
One Target table (see name below)
1. DevProduct (linked to Product table)
I put in fields from the above tables (not the target of course) and when I
run the query I receive the following error msg..
ODBC-update on linked table DevProduct failed.
[Microsoft][ODBC SQLServer driver][SQL Server] Cannot UPDATE TblProduct
because tblProductCompany exist".
The strange thing about this is I do not have a ProductCompany table in my
query set. However I do have it related in my table front-end relations. This
is a database I ended up with that is a total mess! I was able to contact our
IT dept and they stated that the tables do have a trigger attached to them.
The trigger has the same error msg etc as what is stated when I run the query.
Could the probelm be on the back-end and not the front? If so how to fix it
and where to begin. Between us 3 we have 32 years experience and hoping one
of you could find this solution and do us a great deed for the day.
THANK YOU ALL VERY MUCH FOIR YOUR TIME!
PS I have pasted the SQL below if that would help?
UPDATE dbo_tblSaaDocument RIGHT JOIN (dbo_tblObligationType INNER JOIN
(dbo_TblProductVersion INNER JOIN (DEV_dbo_TblProduct RIGHT JOIN
dbo_TblProduct ON DEV_dbo_TblProduct.ProductId = dbo_TblProduct.ProductId) ON
dbo_TblProductVersion.ProductId = dbo_TblProduct.ProductId) ON
dbo_tblObligationType.ObligationTypeID = dbo_TblProduct.ObligationTypeID) ON
dbo_tblSaaDocument.SaaDocumentID = dbo_tblObligationType.SaaDocumentID SET
DEV_dbo_TblProduct.ProductId = [dbo_tblProduct]![ProductID],
DEV_dbo_TblProduct.EAndOFlag = [dbo_TblProductVersion].[EAndOFlag],
DEV_dbo_TblProduct.Countersignatures =
[dbo_TblProductVersion].[Countersignatures], DEV_dbo_TblProduct.Attachments =
[dbo_TblProductVersion].[Attachments], DEV_dbo_TblProduct.StateAbbr =
[dbo_TblProduct]![StateAbbr], DEV_dbo_TblProduct.BondAmtMin =
[dbo_TblProductVersion]![BondAmtMin], DEV_dbo_TblProduct.ProductLine =
[dbo_tblObligationType]![ProductLine], DEV_dbo_TblProduct.BondAmtStd =
[dbo_TblProductVersion]![BondAmtStd], DEV_dbo_TblProduct.BondAmtMax =
[dbo_TblProductVersion]![BondAmtMax], DEV_dbo_TblProduct.BondType =
[dbo_tblObligationType]![BondType], DEV_dbo_TblProduct.BondTermMonths =
[dbo_TblProductVersion]![BondTermMonths], DEV_dbo_TblProduct.CancelDays =
[dbo_TblProductVersion]![CancelDays], DEV_dbo_TblProduct.ObligationTypeID =
[dbo_TblProduct]![ObligationTypeID], DEV_dbo_TblProduct.CategoryId =
[dbo_tblObligationType]![CategoryID], DEV_dbo_TblProduct.BondExpirationDate =
[dbo_TblProductVersion]![BondExpirationDate],
DEV_dbo_TblProduct.AvailableOnline = [dbo_TblProduct]![Available Online],
DEV_dbo_TblProduct.MinCreditScore = [dbo_TblProductVersion]![MinCreditScore],
DEV_dbo_TblProduct.BondEffectiveDate =
[dbo_TblProductVersion]![BondEffectiveDate], DEV_dbo_TblProduct.ClassCode =
[dbo_tblSaaDocument]![ClassCode], DEV_dbo_TblProduct.RiskType =
[dbo_TblProductVersion]![RiskType], DEV_dbo_TblProduct.RateType =
[dbo_TblProductVersion]![RateType], DEV_dbo_TblProduct.RenewalMethod =
[dbo_TblProductVersion]![RenewalMethod], DEV_dbo_TblProduct.RateCode =
[dbo_TblProductVersion]![RateCode],
DEV_dbo_TblProduct.BondObligationDescription =
[dbo_TblProduct]![BondObligationDescription], DEV_dbo_TblProduct.ObligeeId =
[dbo_TblProduct]![ObligeeID], DEV_dbo_TblProduct.SpecialInstructions =
[dbo_TblProductVersion]![SpecialInstructions], DEV_dbo_TblProduct.Description
= [dbo_tblProductVersion].[Description], DEV_dbo_TblProduct.FormId =
[dbo_tblProductVersion].[NewBusinessFormId]
WHERE (((DEV_dbo_TblProduct.ProductId) Is Null And
(DEV_dbo_TblProduct.ProductId)="[dbo_tblProduct].[ProductID]") AND
((DEV_dbo_TblProduct.EAndOFlag)=IIf(Nz([dbo_tblProductversion]![EAndOFlag],0)=-1,"Y","N")
And (DEV_dbo_TblProduct.EAndOFlag)<>"[dbo_tblProductVersion].[EAndOFlag]")
AND
((DEV_dbo_TblProduct.Countersignatures)=IIf(Nz([dbo_tblProductversion]![Countersignatures],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Countersignatures)<>"[dbo_tblProductVersion].[Countersignatures]")
AND
((DEV_dbo_TblProduct.Attachments)=IIf(Nz([dbo_tblProductversion]![Attachments],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Attachments)<>"[dbo_tblProductVersion].[Attachments]")
AND ((DEV_dbo_TblProduct.StateAbbr)<>[dbo_tblProduct].[StateAbbr]) AND
((DEV_dbo_TblProduct.BondAmtMin)<>[dbo_TblProductVersion].[BondAmtMin]) AND
((DEV_dbo_TblProduct.ProductLine) Not Like
[dbo_tblObligationType].[ProductLine]) AND
((DEV_dbo_TblProduct.BondAmtStd)<>[dbo_TblProductVersion].[BondAmtStd]) AND
((DEV_dbo_TblProduct.BondAmtMax)<>[dbo_TblProductVersion].[BondAmtmax]) AND
((DEV_dbo_TblProduct.BondType) Not Like [dbo_tblObligationType].[BondType])
AND
((DEV_dbo_TblProduct.FormId)<>[dbo_TblProductVersion].[NewBusinessFormId]))
OR
(((DEV_dbo_TblProduct.BondTermMonths)<>[dbo_TblProductVersion].[BondTermMonths])
AND
((DEV_dbo_TblProduct.Description)<>[dbo_TblProductVersion].[Description])) OR
(((DEV_dbo_TblProduct.CancelDays)<>[dbo_TblProductVersion].[CancelDays]) AND
((DEV_dbo_TblProduct.ObligationTypeID)<>[dbo_TblProduct].[ObligationTypeID])
AND ((DEV_dbo_TblProduct.ObligeeId)<>[dbo_TblProduct].[ObligeeId])) OR
(((DEV_dbo_TblProduct.CategoryId)<>[dbo_tblObligationType].[CategoryID]) AND
((DEV_dbo_TblProduct.BondExpirationDate)<>[dbo_TblProductVersion].[BondExpirationDate])
AND ((DEV_dbo_TblProduct.AvailableOnline)<>[dbo_TblProduct].[Available
Online]) AND
((DEV_dbo_TblProduct.MinCreditScore)<>[dbo_TblProductVersion].[MinCreditScore]))
OR
(((DEV_dbo_TblProduct.BondEffectiveDate)<>[dbo_TblProductVersion].[BondEffectiveDate])
AND ((DEV_dbo_TblProduct.ClassCode)<>[dbo_tblSaaDocument].[ClassCode]) AND
((DEV_dbo_TblProduct.RiskType)<>[dbo_TblProductVersion].[RiskType])) OR
(((DEV_dbo_TblProduct.RateType)<>[dbo_TblProductVersion].[RateType]) AND
((DEV_dbo_TblProduct.RenewalMethod)<>[dbo_TblProductVersion].[RenewalMethod])
AND ((DEV_dbo_TblProduct.RateCode)<>[dbo_TblProductVersion].[RateCode]) AND
((DEV_dbo_TblProduct.BondObligationDescription)<>[dbo_TblProduct].[BondObligationDescription]))
OR
(((DEV_dbo_TblProduct.SpecialInstructions)<>[dbo_TblProductVersion].[SpecialInstructions]))
WITH OWNERACCESS OPTION;
department for help also. No solution as of yet so I thought the experts
would know the problem? So here is the challange.
I am producing an update query with 4 source tbls (see names below)
1. Product (linked to Dev Target tbl)
2. Document (Linked to Obligation tbl)
3. ProductVersion (linked to Product tbl)
4. Obligation Type (linked to Product tbl)
One Target table (see name below)
1. DevProduct (linked to Product table)
I put in fields from the above tables (not the target of course) and when I
run the query I receive the following error msg..
ODBC-update on linked table DevProduct failed.
[Microsoft][ODBC SQLServer driver][SQL Server] Cannot UPDATE TblProduct
because tblProductCompany exist".
The strange thing about this is I do not have a ProductCompany table in my
query set. However I do have it related in my table front-end relations. This
is a database I ended up with that is a total mess! I was able to contact our
IT dept and they stated that the tables do have a trigger attached to them.
The trigger has the same error msg etc as what is stated when I run the query.
Could the probelm be on the back-end and not the front? If so how to fix it
and where to begin. Between us 3 we have 32 years experience and hoping one
of you could find this solution and do us a great deed for the day.
THANK YOU ALL VERY MUCH FOIR YOUR TIME!
PS I have pasted the SQL below if that would help?
UPDATE dbo_tblSaaDocument RIGHT JOIN (dbo_tblObligationType INNER JOIN
(dbo_TblProductVersion INNER JOIN (DEV_dbo_TblProduct RIGHT JOIN
dbo_TblProduct ON DEV_dbo_TblProduct.ProductId = dbo_TblProduct.ProductId) ON
dbo_TblProductVersion.ProductId = dbo_TblProduct.ProductId) ON
dbo_tblObligationType.ObligationTypeID = dbo_TblProduct.ObligationTypeID) ON
dbo_tblSaaDocument.SaaDocumentID = dbo_tblObligationType.SaaDocumentID SET
DEV_dbo_TblProduct.ProductId = [dbo_tblProduct]![ProductID],
DEV_dbo_TblProduct.EAndOFlag = [dbo_TblProductVersion].[EAndOFlag],
DEV_dbo_TblProduct.Countersignatures =
[dbo_TblProductVersion].[Countersignatures], DEV_dbo_TblProduct.Attachments =
[dbo_TblProductVersion].[Attachments], DEV_dbo_TblProduct.StateAbbr =
[dbo_TblProduct]![StateAbbr], DEV_dbo_TblProduct.BondAmtMin =
[dbo_TblProductVersion]![BondAmtMin], DEV_dbo_TblProduct.ProductLine =
[dbo_tblObligationType]![ProductLine], DEV_dbo_TblProduct.BondAmtStd =
[dbo_TblProductVersion]![BondAmtStd], DEV_dbo_TblProduct.BondAmtMax =
[dbo_TblProductVersion]![BondAmtMax], DEV_dbo_TblProduct.BondType =
[dbo_tblObligationType]![BondType], DEV_dbo_TblProduct.BondTermMonths =
[dbo_TblProductVersion]![BondTermMonths], DEV_dbo_TblProduct.CancelDays =
[dbo_TblProductVersion]![CancelDays], DEV_dbo_TblProduct.ObligationTypeID =
[dbo_TblProduct]![ObligationTypeID], DEV_dbo_TblProduct.CategoryId =
[dbo_tblObligationType]![CategoryID], DEV_dbo_TblProduct.BondExpirationDate =
[dbo_TblProductVersion]![BondExpirationDate],
DEV_dbo_TblProduct.AvailableOnline = [dbo_TblProduct]![Available Online],
DEV_dbo_TblProduct.MinCreditScore = [dbo_TblProductVersion]![MinCreditScore],
DEV_dbo_TblProduct.BondEffectiveDate =
[dbo_TblProductVersion]![BondEffectiveDate], DEV_dbo_TblProduct.ClassCode =
[dbo_tblSaaDocument]![ClassCode], DEV_dbo_TblProduct.RiskType =
[dbo_TblProductVersion]![RiskType], DEV_dbo_TblProduct.RateType =
[dbo_TblProductVersion]![RateType], DEV_dbo_TblProduct.RenewalMethod =
[dbo_TblProductVersion]![RenewalMethod], DEV_dbo_TblProduct.RateCode =
[dbo_TblProductVersion]![RateCode],
DEV_dbo_TblProduct.BondObligationDescription =
[dbo_TblProduct]![BondObligationDescription], DEV_dbo_TblProduct.ObligeeId =
[dbo_TblProduct]![ObligeeID], DEV_dbo_TblProduct.SpecialInstructions =
[dbo_TblProductVersion]![SpecialInstructions], DEV_dbo_TblProduct.Description
= [dbo_tblProductVersion].[Description], DEV_dbo_TblProduct.FormId =
[dbo_tblProductVersion].[NewBusinessFormId]
WHERE (((DEV_dbo_TblProduct.ProductId) Is Null And
(DEV_dbo_TblProduct.ProductId)="[dbo_tblProduct].[ProductID]") AND
((DEV_dbo_TblProduct.EAndOFlag)=IIf(Nz([dbo_tblProductversion]![EAndOFlag],0)=-1,"Y","N")
And (DEV_dbo_TblProduct.EAndOFlag)<>"[dbo_tblProductVersion].[EAndOFlag]")
AND
((DEV_dbo_TblProduct.Countersignatures)=IIf(Nz([dbo_tblProductversion]![Countersignatures],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Countersignatures)<>"[dbo_tblProductVersion].[Countersignatures]")
AND
((DEV_dbo_TblProduct.Attachments)=IIf(Nz([dbo_tblProductversion]![Attachments],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Attachments)<>"[dbo_tblProductVersion].[Attachments]")
AND ((DEV_dbo_TblProduct.StateAbbr)<>[dbo_tblProduct].[StateAbbr]) AND
((DEV_dbo_TblProduct.BondAmtMin)<>[dbo_TblProductVersion].[BondAmtMin]) AND
((DEV_dbo_TblProduct.ProductLine) Not Like
[dbo_tblObligationType].[ProductLine]) AND
((DEV_dbo_TblProduct.BondAmtStd)<>[dbo_TblProductVersion].[BondAmtStd]) AND
((DEV_dbo_TblProduct.BondAmtMax)<>[dbo_TblProductVersion].[BondAmtmax]) AND
((DEV_dbo_TblProduct.BondType) Not Like [dbo_tblObligationType].[BondType])
AND
((DEV_dbo_TblProduct.FormId)<>[dbo_TblProductVersion].[NewBusinessFormId]))
OR
(((DEV_dbo_TblProduct.BondTermMonths)<>[dbo_TblProductVersion].[BondTermMonths])
AND
((DEV_dbo_TblProduct.Description)<>[dbo_TblProductVersion].[Description])) OR
(((DEV_dbo_TblProduct.CancelDays)<>[dbo_TblProductVersion].[CancelDays]) AND
((DEV_dbo_TblProduct.ObligationTypeID)<>[dbo_TblProduct].[ObligationTypeID])
AND ((DEV_dbo_TblProduct.ObligeeId)<>[dbo_TblProduct].[ObligeeId])) OR
(((DEV_dbo_TblProduct.CategoryId)<>[dbo_tblObligationType].[CategoryID]) AND
((DEV_dbo_TblProduct.BondExpirationDate)<>[dbo_TblProductVersion].[BondExpirationDate])
AND ((DEV_dbo_TblProduct.AvailableOnline)<>[dbo_TblProduct].[Available
Online]) AND
((DEV_dbo_TblProduct.MinCreditScore)<>[dbo_TblProductVersion].[MinCreditScore]))
OR
(((DEV_dbo_TblProduct.BondEffectiveDate)<>[dbo_TblProductVersion].[BondEffectiveDate])
AND ((DEV_dbo_TblProduct.ClassCode)<>[dbo_tblSaaDocument].[ClassCode]) AND
((DEV_dbo_TblProduct.RiskType)<>[dbo_TblProductVersion].[RiskType])) OR
(((DEV_dbo_TblProduct.RateType)<>[dbo_TblProductVersion].[RateType]) AND
((DEV_dbo_TblProduct.RenewalMethod)<>[dbo_TblProductVersion].[RenewalMethod])
AND ((DEV_dbo_TblProduct.RateCode)<>[dbo_TblProductVersion].[RateCode]) AND
((DEV_dbo_TblProduct.BondObligationDescription)<>[dbo_TblProduct].[BondObligationDescription]))
OR
(((DEV_dbo_TblProduct.SpecialInstructions)<>[dbo_TblProductVersion].[SpecialInstructions]))
WITH OWNERACCESS OPTION;