Linking to tables with greater than 255 fields/columns

[Guest]

Okay I know this has been discussed ad naseum before, regarding
the limit of Access not being able to contain greater than 255 columns
in a linked table. I have this issue as well.

However the table I am dealing with(via an ODBC) connection is a CACHE
database that interfaces with some company software. I have no control
*whatsoever* to manipulate its size. I would like to inspect the 255+ columns
and write some queries against it but this 255 field limit is a MAJOR hassle.

Some workarounds I am hoping are as follows. After/during when I link to
the table can I control what fields that Access contains in the linked table
in
my database? (Unfortunately Access is not showing a good chunk of the fields
I would like to inspect). Can I manipulate my query (I gave this a try in SQL
View) to pull fields that are not currently in my linked table. Access
doesn't seem to like
this but perhaps there are other ways around this????

Last, is it confirmed that Access 2007 won't allow over 255 fields? If not I
would
certainly love to try that as a workaround....

-Peter

 

[Douglas J. Steele]

I'm sure I've done the following successfully dealing with ODBC databases,
but I can't test at the moment to confirm.

Link the table. Create a query that includes fewer than 255 fields. Run the
query. Create a query that shows other fields (as long as it's less than
255)

 

[Guest]

This won't work. The problem is that when I link the table there are
missing fields in the linked table in my Access database. (It looks like the
fields are sorted alphabetically and fields after the 255th field are
deliberately omitted).

So I cannot do any sort of query tasks with those omitted fields because
when I add the table to my query they are not present and therefore
are unselectable....

If Microsoft is going to hold fast to the 255 field limit in Access it would
be nice if I could at least I could pick the 255 fields that are present in
my
linked (Access) table....

 

[Rick Brandt]

This won't work. The problem is that when I link the table there are
missing fields in the linked table in my Access database. (It looks
like the fields are sorted alphabetically and fields after the 255th
field are deliberately omitted).

So I cannot do any sort of query tasks with those omitted fields
because when I add the table to my query they are not present and
therefore
are unselectable....

If Microsoft is going to hold fast to the 255 field limit in Access
it would be nice if I could at least I could pick the 255 fields that
are present in my
linked (Access) table....

Have you tried creating a passthrough query specifying only the fields you
need?

 

[Kerry]

Access 2007 DOES allow over 255 fields.

 

[John Vinson]

Some workarounds I am hoping are as follows. After/during when I link to
the table can I control what fields that Access contains in the linked table
in
my database? (Unfortunately Access is not showing a good chunk of the fields
I would like to inspect). Can I manipulate my query (I gave this a try in SQL
View) to pull fields that are not currently in my linked table. Access
doesn't seem to like
this but perhaps there are other ways around this????

Last, is it confirmed that Access 2007 won't allow over 255 fields? If not I
would
certainly love to try that as a workaround....

A2007 has the same limit.

You might be able to use PassThrough queries in T-SQL, or create a
View on the host returning the fields past the 255 limit - but I can't
think of any way to use a plain-vanilla ODBC table connection.

John W. Vinson[MVP]

 

[Guest]

I had read about PassThrough queries in other posts. It's okay but it doesn't
buy me much. CACHE has an SQL Manager utility where I can write SQL
queries. I can (and have been using that). I just like the convienience of
having the linked tables and quickly being able to produce queries. Which
is what Access is all about, isn't it?

 

[Jamie Collins]

CACHE has an SQL Manager utility where I can write SQL
queries. I can (and have been using that). I just like the convienience of
having the linked tables and quickly being able to produce queries. Which
is what Access is all about, isn't it?

I used Caché a for a year, a few years back now. I didn't rate the UI
either. Its SQL 'gateway' stuff felt retro fitted compared to the
'mumps' foundations or even the RAD web stuff. I have a recollections
of having to flush the cached queries else bad things would happen, no
OLE DB provider, etc.

I'm guessing you like the graphical aspect of the Access UI? Well, I
was ultimately thankful for my Caché experience because it drove me to
really get to grips with the SQL language. I also ended up developing
my own SQL language-based UI...and I'm still using it today for Jet,
MSDE, etc.

For me, Access has the opposite effect i.e. it favours the mindset that
prefers a GUI tool to that insulates them from SQL code. Nothing wrong
with that, horses for courses, etc. The SQL coder is second class
citizen in Access land, it seems (e.g. monochrome text, formatting not
respected and not retained, etc).

Jamie.

--