Brian,
I'm using ODBCLink version 5.34 (I think), and maybe it doesn't fully
support the _RECNUM option you mention below. This is better than
specifying a non-unique key, which causes Access fits (it will return
a seemingly random number of records for the offending data set, with
incorrect data). The _RECNUM option duplicates only 100 of the
records (records 101-200), and has the correct data - does 5.35 or
5.36 fix this?
Thanks!
-Bob
______________________________ Reply Separator _________________________________
Subject: Re: Re[2]: ODBC & Image
Author: Brian Duncombe <[log in to unmask]> at Internet
Date: 11/13/95 9:39 AM
At 04:41 PM 11/10/95 -0500, Denys wrote:
>In a message dated 95-11-10 14:26:10 EST, [log in to unmask] (Chris Bartram) writes:
>
>>I'm no odbc expert, as we're just starting to experiment with it ourselves
>>(and also ran into the unique-key requirement problem in MS Access), but I
>>wonder if a way around it wouldn't be to have HP automatically generate a
>>pseudo-item for every detail dataset - say a numeric field which gets
>>initialized to the Image record# of the record... Ignore updates to that field
>>when records are being updated but use it as an index if possible. Seems like
>>that field could then be used as (or made part of) the unique key for MS
>>Access?
>> Just a thought...
>>
>>
>
>Funny you should mention that. This is the exact solution I presented to HP
>at the last IRPOF. I spent quite a bit of time discussing it with them since
>then. At the last 2 C/S UG meetings, HP said they were not going to do that.
> They said that using a view which as in it a call to TID with a new function
>to be released, should handle the problem. We shall see.
>
>However, you still do not have to use a unique key when you attach an IMAGE
>table to a MS Access, just lie and tell it that the column you are telling it
>to use is unique. MS Access won't trust you anyways, because when it does a
>delete or an update, it verifies all the columns in your tuple.
During the C/S Audio Conference meeting a number of weeks ago when this
issue came up, I indicated that I thought that we at M.B. Foster had already
done this with our ODBCLink driver but that I wasn't sure how well it
worked. We have subsequently investigated this further and are pleased to
report the following:
We have worked extensively with MS Access and ODBC and wish to set the
record straight with respect to the need for primary keys and updatability
of data from MS-Access.
1) It won't work to tell Access that any field in the record is a primary
key and hope that it will do a field-by-field comparison when updating
the record. It's true that Access does a field-by-field comparison during
an update (to ensure the record hasn't been changed by another user)
but it is also true that during the initial read of the data it emits
one SQL statement to read all the primary keys, and a second SQL statement
of the form "SELECT ... FROM table WHERE primary_key=?" and then substitutes
the values of the primary keys. If the value is not a primary key, and,
say, more than one record qualifies, Access will be very unhappy. And
rightly so.
2) We at MB Foster get around this problem in our ODBCLink product (ODBC
driver for HP-3000 and HP-UX) in the following way: first of all,
if a unique key actually exists in a detail dataset, it can be specified.
If not, we have an option to return the Image relative record number
as a column in the table, and also as the primary key. This fixes the
problem of updatability for all cases except where the record number
would be changed by an update (which may be possible if CIUPDATE is on and
a key field in a sorted chain is changed by the update).
<PLUG>
For information about our ODBCLink option, please call 1-800-ANSWERS or
1-613-448-2333
</PLUG>
Brian Duncombe ([log in to unmask]) - M.B. Foster Software Labs
"Inside every large program is a small one struggling to get out."
C. A. R. Hoare
|
