Attach Tables - Too Many Fields

[Andy]

Is it possible to difine which fields to attach on a Oracle ODBC linked
table? The table has 300+ fields but I only need data from about 20 of them.
I can't see the complete filed list since Access will stop at 256 fields. I
was wondering if it's possible to create a tabledef with the fileds I need
and use that during the link process.

 

[Robert Morley]

I don't think you can do it with a TableDef, but you might be able to do it
with a pass-through query. Pass-through queries don't require you to link
in the table first (the query defines its own link), so there wouldn't be a
problem with the usual number-of-fields limitation.

It's been 12 years or so since I last wrote a pass-through query to Oracle,
so I can't exactly give you any details on how to do that, but if you can't
figure it out, someone else will probably chime in to help.


Rob

 

[Jeff Boyce]

Andy

Another approach in addition to the one Robert offered would be to create a
view in Oracle that contains only the fields you need. Then link to that
view from within Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Robert Morley]

<blush> That would be the OBVIOUS method...that's why I didn't think of it.
Of course, that presumes you have design access to the Oracle database,
but still, it never even occurred to me!


Rob

 

[mcescher]

I don't think you can do it with a TableDef, but you might be able to do it
with a pass-through query.  Pass-through queries don't require you to link
in the table first (the query defines its own link), so there wouldn't be a
problem with the usual number-of-fields limitation.

It's been 12 years or so since I last wrote a pass-through query to Oracle,
so I can't exactly give you any details on how to do that, but if you can't
figure it out, someone else will probably chime in to help.

Rob




- Show quoted text -

Have you tried writing a passthrough query? Then the SQL would
execute on the Oracle engine, and not on your local PC.

Hope this helps,
Chris M.