Problem using linked tables instead of ADP

Hi
I know I probably shouldn't be doing this but...
I have a table on an MSDE database that has a bigInt for the primary key. If
I try to link to this table from Access (XP) using an ODBC connection the
table links ok. The first time I opened the table it was ok. Since then the
primary key data type is interpretted as text and all the records (though
still there) have "#deleted" in each column of every row? I have tried
relinking the table and still get he same problem.
Any ideas whats going on?
Thanks in advance
Mark
Have you linked the table such that Access recognizes the primary key?
"Mark" wrote:

> Hi
> I know I probably shouldn't be doing this but...
> I have a table on an MSDE database that has a bigInt for the primary key. If
> I try to link to this table from Access (XP) using an ODBC connection the
> table links ok. The first time I opened the table it was ok. Since then the
> primary key data type is interpretted as text and all the records (though
> still there) have "#deleted" in each column of every row? I have tried
> relinking the table and still get he same problem.
> Any ideas whats going on?
> Thanks in advance
> Mark
>
>
|||Hi Monte

> Have you linked the table such that Access recognizes the primary key?
>
As far as I know, yes? I wasn't prompted to select a primary key and looking
at the table design from within access shows the primary key present but as
text instead of number.
Cheers
Mark
|||I have also seen this problem occur when I have "bit" type fields in the SQL
table, and don't have a default value (say zero) and allow nulls checked.
"Mark" wrote:

> Hi Monte
>
> As far as I know, yes? I wasn't prompted to select a primary key and looking
> at the table design from within access shows the primary key present but as
> text instead of number.
> Cheers
> Mark
>
>
|||"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:46D395BA-E150-4C96-9EDE-AF4776FBC18A@.microsoft.com...
>I have also seen this problem occur when I have "bit" type fields in the
>SQL
> table, and don't have a default value (say zero) and allow nulls checked.
Monte
Scary stuff hey :o{
I'm not going to worry about it too much as I was just playing to see the
difference in performance etc. Would still be interested to know why, may
have another look later...
Cheers
Mark

problem updating view with instead of trigger

Hi all,
I have created wv_details view that has an instead of update trigger. My
question is how can I update this view?
When I run the following query I get an error - "View 'wv_details' has
an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
statement."
UPDATE wv_details
SET REFERENCE=latest.REFERENCE,
[NOTES]=latest.NOTES,
[TITLE]=latest.TITLE,
[LINKACCT]=latest.LINKACCT,
[COUNTRY]=latest.COUNTRY,
[ZIP]=latest.ZIP,
[EXT]=latest.EXT,
[STATE]=latest.STATE,
[ADDRESS1]=latest.ADDRESS1,
[ADDRESS2]=latest.ADDRESS2,
[MERGECODES]=latest.MERGECODES,
[STATUS]=latest.STATUS,
[LASTUSER]=latest.LASTUSER
FROM wv_details latest
JOIN wv_details
ON latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
So I changed this query as shown below. It does not use a FROM clause
anymore. But I get a different error with this query - "The text, ntext, and
image data types are invalid in this subquery or aggregate expression.". The
NOTES field is a text column.
UPDATE wv_details
SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[NOTES]=(SELECT TOP 1 latest.NOTES
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[TITLE]=(SELECT TOP 1 latest.TITLE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[LINKACCT]=(SELECT TOP 1 latest.LINKACCT
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[COUNTRY]=(SELECT TOP 1 latest.COUNTRY
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ZIP]=(SELECT TOP 1 latest.ZIP
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[EXT]=(SELECT TOP 1 latest.EXT
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[STATE]=(SELECT TOP 1 latest.STATE
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[MERGECODES]=(SELECT TOP 1 latest.MERGECODES
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[STATUS]=(SELECT TOP 1 latest.STATUS
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
),
[LASTUSER]=(SELECT TOP 1 latest.LASTUSER
FROM wv_details latest
WHERE latest.accountno = wv_details.accountno
AND latest.detail = wv_details.detail
AND latest.dear = wv_details.dear
AND latest.recid <> wv_details.recid
AND NOT EXISTS(SELECT TOP 1 1
FROM wv_details old
WHERE latest.accountno = old.accountno
AND latest.detail = old.detail
AND latest.dear = old.dear
AND latest.recid <> old.recid
AND latest.lastdatetime < old.lastdatetime
)
)
So my question is how can I update this view?
Thanks...
-NikhilThere are quite a few issues:
1. You reference the view as if it's either an "inserted" or "deleted"
virtual table. It's not so.
2. You have an Instead Of trigger on the view, you should be updating the
base table within that trigger. You have full access the virtual tables
there.
3. You cannot do (update obj set col =(select top 1 lob_col from ...)).
You're are doing aggregation on the blob which is not allowed (by MS
design).
4. Even if (col=select top 1 lob) is allowed, this update is going to cost
you royally.
You have been posting for a while here. You know it would be easier if you
post ddl+sample data/code+expected output, it would be easier to help you.
http://groups.google.co.uk/groups?h...er+Nikhil+Patel
http://www.aspfaq.com/etiquette.asp?id=5006
-oj
"Nikhil Patel" <donotspam@.nospaml.com> wrote in message
news:eI6SpeYBFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created wv_details view that has an instead of update trigger.
> My question is how can I update this view?
> When I run the following query I get an error - "View 'wv_details' has
> an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM
> statement."
> UPDATE wv_details
> SET REFERENCE=latest.REFERENCE,
> [NOTES]=latest.NOTES,
> [TITLE]=latest.TITLE,
> [LINKACCT]=latest.LINKACCT,
> [COUNTRY]=latest.COUNTRY,
> [ZIP]=latest.ZIP,
> [EXT]=latest.EXT,
> [STATE]=latest.STATE,
> [ADDRESS1]=latest.ADDRESS1,
> [ADDRESS2]=latest.ADDRESS2,
> [MERGECODES]=latest.MERGECODES,
> [STATUS]=latest.STATUS,
> [LASTUSER]=latest.LASTUSER
> FROM wv_details latest
> JOIN wv_details
> ON latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> So I changed this query as shown below. It does not use a FROM clause
> anymore. But I get a different error with this query - "The text, ntext,
> and image data types are invalid in this subquery or aggregate
> expression.". The NOTES field is a text column.
> UPDATE wv_details
> SET REFERENCE=(SELECT TOP 1 latest.REFERENCE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [NOTES]=(SELECT TOP 1 latest.NOTES
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [TITLE]=(SELECT TOP 1 latest.TITLE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [LINKACCT]=(SELECT TOP 1 latest.LINKACCT
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [COUNTRY]=(SELECT TOP 1 latest.COUNTRY
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ZIP]=(SELECT TOP 1 latest.ZIP
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [EXT]=(SELECT TOP 1 latest.EXT
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [STATE]=(SELECT TOP 1 latest.STATE
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ADDRESS1]=(SELECT TOP 1 latest.ADDRESS1
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [ADDRESS2]=(SELECT TOP 1 latest.ADDRESS2
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [MERGECODES]=(SELECT TOP 1 latest.MERGECODES
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [STATUS]=(SELECT TOP 1 latest.STATUS
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> ),
> [LASTUSER]=(SELECT TOP 1 latest.LASTUSER
> FROM wv_details latest
> WHERE latest.accountno = wv_details.accountno
> AND latest.detail = wv_details.detail
> AND latest.dear = wv_details.dear
> AND latest.recid <> wv_details.recid
> AND NOT EXISTS(SELECT TOP 1 1
> FROM wv_details old
> WHERE latest.accountno = old.accountno
> AND latest.detail = old.detail
> AND latest.dear = old.dear
> AND latest.recid <> old.recid
> AND latest.lastdatetime < old.lastdatetime
> )
> )
>
> So my question is how can I update this view?
> Thanks...
> -Nikhil
>