Thanks MGFoster,
I've checked that Access recognises the PK and it does in these cases ... so
knocks that on the head :(
I did more playing around and dropped any unique indexes and PKs on the
culprit tables in Oracle. I then relinked the table in Access. And ... it
works, I can see the data. Great, but this is not a solution, as we do need
PKs and unique indexes on our tables. So I reapplied the PK, and the
problem came back. I also tried just applying a unique index on the field
that should have been the PK (i.e. Code), without implementing the PK.
Problem also shows up in this case.
So, I am pretty sure that the unique index on a char or varchar2 field is
the thing causing all this. But still no real solution.
Any other suggestions?
Thanks
Joe
"MGFoster" <me@privacy.com> wrote in message
news:iaMtd.5469$yr1.3492@newsread3.news.pas.earthl ink.net...[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> IDEA 1:
>
> It may be possible that Access is not recognizing the primary key/unique
> index on the Oracle table. In that case you can create PK/unique index
> in access for the linked table - just be sure you are creating the
> PK/index for the real PK/index on the Oracle table. E.g.: Oracle table
> has ManagerID as the PK. In an Access query SQL view do this:
>
> ALTER TABLE table_name ADD CONSTRAINT PK_table_name PRIMARY KEY
> (ManagerID)
>
> Run that, then check the design view of the linked Oracle table. There
> should be a primary key icon on the ManagerID column.
>
> You can do the same thing for a unique index:
>
> CREATE UNIQUE INDEX idx_Index_name ON table_name (column_name)
>
> See the Access Help articles on ALTER TABLE and CREATE INDEX for more
> info.
>
> IDEA 2:
>
> In some linked SQL Server tables I had the same problem. Changed the
> driver to most recent & problem went away. The Oracle driver may be out
> of date. Get the most recent driver for the Oracle db version.
>
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQbePAYechKqOuFEgEQJEtgCfeKMOOsaqzLvME5uVD5MyeN qJEdEAnjfI
> tFGr5nBr5tg8P65pCI3br678
> =/3ky
> -----END PGP SIGNATURE-----
>
>
> Joe wrote:[color=green]
>> Thanks Tim,
>>
>> I have actually tried what you say, but the problem IS with the primary
>> key field or unique index (not exactly sure which is most accurate to use
>> in this case). As I said, I have a colleague who is able to link the
>> tables without any probs, and I try EXACTLY the same thing on my comp but
>> it doesn't work :(.
>>
>> I know that linked tables aren't the best and that I can use pass through
>> queries. However, pass through queries are not updateable ... so I would
>> have to built views at Oracle and link them anyway (a layer of
>> indirection I would prefer not to have to go through).
>>
>> I did a bit more playing around and find that if I build an Access query
>> based on the culprit table, regardless of which fields I select, I still
>> experience the same problem. However, if I add a "distinct" keyword into
>> the query, problem goes away. i.e. SELECT DISTINCT CODE, DESCR FROM
>> ANZSIC.
>>
>> Mind you, I know that adding "distinct" results in a sort and possible
>> slowness and would much prefer not to have to use it. Also, a query/view
>> built using the "distinct" keyword is not updateable, so it doesn't help
>> me anyway.
>>
>> I'm still a little stumped
>>
>> Thanks
>> Joe
>>
>> "Tim Marshall" <TIMMY!@antarctic.flowerpots> wrote in message
>> news:cp7bk1$9oa$1@coranto.ucs.mun.ca...
>>[color=darkred]
>>>Joe wrote:
>>>
>>>
>>>>I am using Access 2003 and are linking to an Oracle 9i ODBC datasource
>>>>(using Oracle ODBC drivers). After linking the tables in Access, I
>>>>inspect the data contained in the linked tables. For tables that
>>>>involve a number field as the primary key, the data is returned
>>>>successfully.
>>>
>>>IS this the issue? I have experienced exactly this situation when an
>>>Oracle data type will not translate across the ODBC/Jet interface. In my
>>>case it's been largely when an App I administer has Oracle date fields
>>>with just time and no date in them.
>>>
>>>What I've done and counseled other users of the maintenance app I
>>>administer to do is to create the query "gradually". This means bringing
>>>the linked tables into the query grid, linking them and bring down ONE
>>>field at a time. Run the query. If you get data then go back to design
>>>and bring down ONE more field. Repeat until you get the extremely
>>>irritating #Deleted result and you'll know the culprit. Construct queries
>>>omitting the incompatible field(s).
>>>
>>>If you have users who want to be able to access the Oracle tables at
>>>will, consider creating an Oracle view that does not have the "bad"
>>>columns and have them link to that instead.
>>>
>>>I find it works best to have a Oracle user with grant select (and
>>>possibly other grant privileges depending on the tables/views included)
>>>that users who want to do ODBC stuff to access.
>>>
>>>The other bit of advice is to not bother using linked tables. Depending
>>>on the number and nature of Jet joins performed, I've found, in my Oracle
>>>app at least, that with 5 or more tables in an Access Jet query, you run
>>>the danger of the results dropping data for whole columns. Use pass
>>>through queries instead. I use DAO to create various PTQ for list and
>>>combo box data sources, Access form recordsources and so on. In the
>>>latter, it's great because you can apply filters to the long data type if
>>>you have it in your Oracle table.
>>>--
>>>Tim
http://www.ucs.mun.ca/~tmarshal/
>>>^o<
>>>/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
>>>/^^ "What's UP, Dittoooooo?" - Ditto[/color]
>>
>>[/color][/color]
