Recieve unusual error in query. PLease Help!

T

troy

Background: I have 5 tables in my Update query. 4 source tables and one
target table. I run my query and recieve an error that states "ODBC update on
linked table 'Dev_tblProduct' Failed" also "Cannot UPDATE tblProduct because
tblProductCOMPANY exists".

I do not have a table in my query that is names ProductCompany. However I do
have a table of that name which is a one to many in my table relationships.

Could this be the cause of the error? ProductCompany has nothing to do with
this query except for it being related in the table relationship window. I
have added my SQL below incase you see something I am missing. I really need
to get this fixed. 2 days on this is long enough! Thanking you all in advance.


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) Not Like
[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;
 
D

Duane Hookom

I would expect that the least you could do is format the SQL a little to at
least put some paragraph breaks here and there to make helping you easier.
You have a couple issues that stick out without much investigation.
1) you have quotes around table and field names:
(DEV_dbo_TblProduct.ProductId)="[dbo_tblProduct].[ProductID]") AND
....
And (DEV_dbo_TblProduct.EAndOFlag)<>"[dbo_tblProductVersion].[EAndOFlag]")
....
(DEV_dbo_TblProduct.Countersignatures)<>"[dbo_tblProductVersion].[Countersignatures]")
.... more
2) you are using "Not Like" in a couple places with no wildcards. Why not
use "<>"

If you fix the above issues, can you change the query to a select query to
determine if the records are even updateable?
--
Duane Hookom
MS Access MVP


troy said:
Background: I have 5 tables in my Update query. 4 source tables and one
target table. I run my query and recieve an error that states "ODBC update
on
linked table 'Dev_tblProduct' Failed" also "Cannot UPDATE tblProduct
because
tblProductCOMPANY exists".

I do not have a table in my query that is names ProductCompany. However I
do
have a table of that name which is a one to many in my table
relationships.

Could this be the cause of the error? ProductCompany has nothing to do
with
this query except for it being related in the table relationship window. I
have added my SQL below incase you see something I am missing. I really
need
to get this fixed. 2 days on this is long enough! Thanking you all in
advance.


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) Not Like
[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;
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Solution. Need Help 6
Error Msg During Update Query. PLEASE HELP! 0
Update query question 1
Need help with Query results 2
Why does this happen? Please help! 0
Does a filter query? 2nd Post Please Help! 3

Top