Welcome to SPN

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

Sign Up Now!

Access Linking to DB2 Tables very slow

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

  1. KML

    KML
    Expand Collapse
    Guest

    Hello,

    We have an Access 2003 database that we are attempting to link to some
    DB2 tables using ODBC. It works ok sometimes, but other times it does
    not, and I think we have narrowed down the problem.

    The DB2 database has between 35,000 and 40,000 tables in it, and Access
    can link to the tables with no problem if it is approximately the
    1-32,000th table listed in the dialog box. However, if the table is
    (approximately) above the 32,000th table listed, Access hangs and does
    not complete the link.

    Is there any kind of limitation to the number of tables shown when
    linking tables with Access?

    Thanks!
     
  2. Loading...


  3. Amy Blankenship

    Amy Blankenship
    Expand Collapse
    Guest

    Why on earth do you have that many tables?

    "KML" <lillestol1763@hotmail.com> wrote in message
    news:1153337631.123873.29950@h48g2000cwc.googlegroups.com...
    >
    > Hello,
    >
    > We have an Access 2003 database that we are attempting to link to some
    > DB2 tables using ODBC. It works ok sometimes, but other times it does
    > not, and I think we have narrowed down the problem.
    >
    > The DB2 database has between 35,000 and 40,000 tables in it, and Access
    > can link to the tables with no problem if it is approximately the
    > 1-32,000th table listed in the dialog box. However, if the table is
    > (approximately) above the 32,000th table listed, Access hangs and does
    > not complete the link.
    >
    > Is there any kind of limitation to the number of tables shown when
    > linking tables with Access?
    >
    > Thanks!
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    yes there is - I haven't actually counted the limit, but I have experienced
    similar behaviour.
    I'm not familiar with db2 but try to link the tables through vba code using
    DoCmd.Transferdatabase.
    in Oracle /MSSQL you can create a Passthrough query against
    INFORMATION_SCHEMA.Tables to retrieve the table names.
    alternatively quering USER_TABLES or TABS (Oracle), as Oracle is derived
    from db2 I think the latter is likely to work

    HTH

    Pieter

    PS sound like a nasty backend with that many tables (SAP?)

    "KML" <lillestol1763@hotmail.com> wrote in message
    news:1153337631.123873.29950@h48g2000cwc.googlegroups.com...
    >
    > Hello,
    >
    > We have an Access 2003 database that we are attempting to link to some
    > DB2 tables using ODBC. It works ok sometimes, but other times it does
    > not, and I think we have narrowed down the problem.
    >
    > The DB2 database has between 35,000 and 40,000 tables in it, and Access
    > can link to the tables with no problem if it is approximately the
    > 1-32,000th table listed in the dialog box. However, if the table is
    > (approximately) above the 32,000th table listed, Access hangs and does
    > not complete the link.
    >
    > Is there any kind of limitation to the number of tables shown when
    > linking tables with Access?
    >
    > Thanks!
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4285 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  5. KML

    KML
    Expand Collapse
    Guest

    Thank you for the responses.

    I suspected there may be such a limitation.

    I have no idea why there are so many tables... I don't administer this
    database, I'm just trying to troubleshoot the problem. Yes, it is SAP.

    I am familiar with using VB in Access so that may be a decent
    workaround. Again, thanks for the suggestions
     
  6. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    Searching on "specifications" in Access 2003 Help, and choosing Access
    database shows that the total number of objects supported by an Access DB is
    32,768. Linked Tables are objects, as are local Tables, Queries, Forms,
    Reports, Macros, and Modules. You have simply hit a built-in limit.

    It is hard for me to imagine a properly designed relational database, no
    matter what the server or software, that has that many tables. However, I
    will assume that a "mere desktop database" user will not be able to effect a
    change in the design. So I suggest you will have to resdesign your Access
    application to use something less than all those tables at any given time.

    Larry Linson
    Microsoft Access MVP



    "KML" <lillestol1763@hotmail.com> wrote in message
    news:1153337631.123873.29950@h48g2000cwc.googlegroups.com...
    >
    > Hello,
    >
    > We have an Access 2003 database that we are attempting to link to some
    > DB2 tables using ODBC. It works ok sometimes, but other times it does
    > not, and I think we have narrowed down the problem.
    >
    > The DB2 database has between 35,000 and 40,000 tables in it, and Access
    > can link to the tables with no problem if it is approximately the
    > 1-32,000th table listed in the dialog box. However, if the table is
    > (approximately) above the 32,000th table listed, Access hangs and does
    > not complete the link.
    >
    > Is there any kind of limitation to the number of tables shown when
    > linking tables with Access?
    >
    > Thanks!
    >
     
  7. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    "KML" <lillestol1763@hotmail.com> wrote:

    >Yes, it is SAP.


    Ahhhhh enough said.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
     

Share This Page