Cannot see all the fields in an ODBC table/view

[BAC]

Win 2000 Pro
Office 2003 Pro

I have a Redbrick data warehouse that includes a view of a table that has
181 fields in it. Using the Access Query grid, I get only 134 of these fields.

Using MSQuery or a Pass-through query I can access all 181. One of the
fields that does not show up in the Access query grid is "LOB" that is the
60th field in the View and 72nd field in the underlying table.

I was under the impression that Access would allow me to see up to the 1st
255 fields in ODBC datasets.

I have verified that the query properties option "Output all fields" is set
to yes. Is there any way to be able to access all 181 fields without using
MSQuery or a passthrough query?

I now have a situation that will require users to maintain an "exclusion
list". My plan is to have them add the key value for excluded items to a
table and include an "is null" test against the table to excluded those
records. But Access will not allow me to reference local tables in
passthrough queries.

Buty if i could access that LOB field directly, I wouldn't have a problem (i
use this technique in other places and it works flawlessly)

TIA

BAC

 

[Michel Walsh]

Hi,


If you start a brand new query with a simple SQL statement like:

SELECT * FROM tableName

do you get all the 181 columns?

If not, then *maybe* some of the 181 columns are not real columns but
computed columns (or otherwise) and NOT transmitted back... I am just
guessing here. Also note that each single record cannot exceed 4000
characters (excluding memo and ole fields), 2000 if Unicode compression if
off.

Also, if columnX is missing, does


SELECT columnX FROM tableName


get it? if not, maybe the datatype is somehow incompatible.

Note that I don't know anything about "Redbrick data warehouse".

Hoping it may help,
Vanderghast, Access MVP

 

[BAC]

I wasn't aware of the "number of characters" restriction and I'll have to
check that one out.

If I create a query grid from inside access with Select * from table: I will
get 134 fields (the same ones that appear in the field list for that table at
the top of the query grid). Using the same SQL as a pass-through query gives
me the 181 fields.

Redbrick is like Oracle -> a data storage methodology for large data sets.

I can read the data from the various views presesnted, and until now, I was
always able to use "regular" queries to link local (i.e. Access tables) to
the Warehouse (i.e. redbrick tables) based on a common field. But now I have
to link on this LOB field, and since it is not addressable thru the query
grid I'm having to "data mine" off a somewhat related field that returns some
185,000 records that then need to be filtered against a local "LOB" table.

In any event, thatnks for the point toward character count..I'll see what
that does for me and try to find a way around that if, indeed, that is the
problem..

tks
bac