Access Linking to DB2 Tables very slow

[KML]

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!

 

[Amy Blankenship]

Why on earth do you have that many tables?

 

[Pieter Wijnen]

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?)

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!

--

 

[KML]

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

 

[Larry Linson]

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

 

[Tony Toews]

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