|
|
|
date: Thu, 5 Jun 2008 00:16:01 -0700,
group: microsoft.public.access.odbcclientsvr
back
Re: Cannot Add to query to ODBC linked table if Identity column not sh
hi,
can't say why it was done like this, but I think this is by design
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
"ASB" wrote in message
news:C48EEAAE-9F21-41C1-8492-66A4F0BEC643@microsoft.com...
> Recently had some weird behaviour in Access 2007 SP1 on Windows XP SP2.
> Application with odbc linked tables ( SQL SERVER 2005). If I'm showing an
> updatable query , linked to a table with an primary key by odbc
> connection,
> and i don't include the primary key field, I cannot add a record! I am
> able
> to do workaround with a saved query though: add th PK , but hide the field
> in
> the query ( I have no need to whoe the PK field).
> With a link to an Access database this problem doese not exist.
> Is this a possible bug , or default behaveour?
> btw, I saw this was also the case in access 2003, so appearently its not
> something exclusively for access 2007 .
> Thanks
>
> Alain Bobbe
> BM Systems
date: Thu, 5 Jun 2008 13:21:33 +0400
author: Alex Dybenko
Re: Cannot Add to query to ODBC linked table if Identity column not sh
By definition, ODBC does not select the PK field unless you tell it to.
Without the PK field, ODBC does not know which record to update.
Internally inside a database system, such as Access or SQL Server,
records are identified by a physical record number, so you can
update any record as long as you stay "inside" Access or SQL Server.
But ODBC is designed to be a simple system that does not make
any assumptions about the internal structure of the database system.
Using ODBC, the only way to identify a record is from the fields
included in the record set. This works as long as you include a
field or set of fields that has a "unique index" (a primary key).
Access adds the additional restriction that it has to be able to
identify and use the primary key/unique index. Access primary
keys can't include nulls or bit fields, so this restriction is added
to the ODBC requirement that the fields must be present.
(david)
"ASB" wrote in message
news:C48EEAAE-9F21-41C1-8492-66A4F0BEC643@microsoft.com...
> Recently had some weird behaviour in Access 2007 SP1 on Windows XP SP2.
> Application with odbc linked tables ( SQL SERVER 2005). If I'm showing an
> updatable query , linked to a table with an primary key by odbc
connection,
> and i don't include the primary key field, I cannot add a record! I am
able
> to do workaround with a saved query though: add th PK , but hide the field
in
> the query ( I have no need to whoe the PK field).
> With a link to an Access database this problem doese not exist.
> Is this a possible bug , or default behaveour?
> btw, I saw this was also the case in access 2003, so appearently its not
> something exclusively for access 2007 .
> Thanks
>
> Alain Bobbe
> BM Systems
date: Sun, 8 Jun 2008 08:33:01 +1000
author: david@epsomdotcomdotau
Re: Cannot Add to query to ODBC linked table if Identity column not sh
Sorry, did not finish:
Access optimises queries down through the stack of subqueries
and linked tables, so there is no "base query" in the optimised
query plan. Fields that are not included in the final query are not
included in the base query: they are not included at all.
So top-level queries on ODBC sources have to include the PK
fields, or they are not included.
Linked tables internally store the PK field definition, so linked
tables can handle the situation where the PK field is not included
in the query.
(david)
<david@epsomdotcomdotau> wrote in message
news:e72H$5OyIHA.2184@TK2MSFTNGP02.phx.gbl...
> By definition, ODBC does not select the PK field unless you tell it to.
> Without the PK field, ODBC does not know which record to update.
>
> Internally inside a database system, such as Access or SQL Server,
> records are identified by a physical record number, so you can
> update any record as long as you stay "inside" Access or SQL Server.
>
> But ODBC is designed to be a simple system that does not make
> any assumptions about the internal structure of the database system.
> Using ODBC, the only way to identify a record is from the fields
> included in the record set. This works as long as you include a
> field or set of fields that has a "unique index" (a primary key).
>
> Access adds the additional restriction that it has to be able to
> identify and use the primary key/unique index. Access primary
> keys can't include nulls or bit fields, so this restriction is added
> to the ODBC requirement that the fields must be present.
>
> (david)
>
> "ASB" wrote in message
> news:C48EEAAE-9F21-41C1-8492-66A4F0BEC643@microsoft.com...
> > Recently had some weird behaviour in Access 2007 SP1 on Windows XP SP2.
> > Application with odbc linked tables ( SQL SERVER 2005). If I'm showing
an
> > updatable query , linked to a table with an primary key by odbc
> connection,
> > and i don't include the primary key field, I cannot add a record! I am
> able
> > to do workaround with a saved query though: add th PK , but hide the
field
> in
> > the query ( I have no need to whoe the PK field).
> > With a link to an Access database this problem doese not exist.
> > Is this a possible bug , or default behaveour?
> > btw, I saw this was also the case in access 2003, so appearently its not
> > something exclusively for access 2007 .
> > Thanks
> >
> > Alain Bobbe
> > BM Systems
>
>
date: Sun, 8 Jun 2008 08:41:09 +1000
author: david@epsomdotcomdotau
|
|
