Linking to tables with greater than 255 fields/columns

#1 (**permalink**) 28-Jul-2006, 08:38 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13634-linking-tables-greater-than-255-fields.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
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

#2 (**permalink**) 28-Jul-2006, 08:38 AM

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
(no e-mails, please!)

"Peter S." wrote in message
news:43B33884-D7CA-42AE-8DE9-31C110E788FF@microsoft.com...
> 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

#3 (**permalink**) 28-Jul-2006, 08:38 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
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....

"Douglas J. Steele" wrote:

> 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)
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Peter S." wrote in message
> news:43B33884-D7CA-42AE-8DE9-31C110E788FF@microsoft.com...
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
> > 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
>
>
>

#4 (**permalink**) 28-Jul-2006, 08:38 AM

Peter S. wrote:
> 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....
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
>
> 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?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

#5 (**permalink**) 28-Jul-2006, 08:38 AM

Access 2007 DOES allow over 255 fields.

Peter S. wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
> would
> certainly love to try that as a workaround....
>
> -Peter

#6 (**permalink**) 28-Jul-2006, 08:38 AM

On Thu, 20 Jul 2006 11:23:02 -0700, Peter S.
wrote:

>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
>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????
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
>
>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]

#7 (**permalink**) 28-Jul-2006, 08:39 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
having the linked tables and quickly being able to produce queries. Which
is what Access is all about, isn't it?

"John Vinson" wrote:

> On Thu, 20 Jul 2006 11:23:02 -0700, Peter S.
> wrote:
>
> >Some workarounds I am hoping are as follows. After/during when I link to
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
> >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]
>

#8 (**permalink**) 28-Jul-2006, 08:39 AM

Peter S. wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
> CACHE has an SQL Manager utility where I can write SQL
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13634
> 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.

--