Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Linking to tables with greater than 255 fields/columns

Discussion in 'Information Technology' started by Peter S., Jul 28, 2006.

  1. Peter S.

    Peter S.
    Expand Collapse
    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
     
  2. Loading...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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." <PeterS@discussions.microsoft.com> 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
     
  4. Peter S.

    Peter S.
    Expand Collapse
    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....


    "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." <PeterS@discussions.microsoft.com> 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

    >
    >
    >
     
  5. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    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....
    >
    > 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
     
  6. Kerry

    Kerry
    Expand Collapse
    Guest

    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
    > 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
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 20 Jul 2006 11:23:02 -0700, Peter S.
    <PeterS@discussions.microsoft.com> 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
    >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. Peter S.

    Peter S.
    Expand Collapse
    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? :)


    "John Vinson" wrote:

    > On Thu, 20 Jul 2006 11:23:02 -0700, Peter S.
    > <PeterS@discussions.microsoft.com> 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
    > >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]
    >
     
  9. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Peter S. wrote:
    > 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.

    --
     

Share This Page