Re: The Joys of the Jet Engine

Geoff, If you attach two Informix tables separately and run a query using both of them in Access, it downloads the two entire tables before joining them. The solution is to create Informix views containing all the conditions and joins, and returning only the columns you need, so that you have optimised queries with the amount of data passed over the network minimised. If you still have some queries taking a long time, you need to increase the ODBC timeout, as I mentioned in another thread in November as listed below. Regards, Doug Lawry www.douglawry.webhop.org I recommend increasing the timeout both in the ODBC driver and the Access query. If you are using Windows XP, enable the Administrative Tools menu first: * Right click on the task bar and choose "Properties" * Choose "Customize" on the "Start Menu" tab * Turn on the "Administrative Tools" menu To increase the ODBC driver timeout: * Select Programs / Administrative Tools / Data Sources (ODBC) * Click on the "Connection Pooling" tab * Double click on "IBM INFORMIX 3.82 32 BIT" * Enable "Pool Connections to this driver" * Set the timeout you require A search for "timeout" in the online help facility in Access 2002 returns: "Set multiuser query options Specify how long before a query displays an ODBC time-out error A query that uses an ODBC database as its underlying source of data might encounter delays due to network traffic or heavy use of the ODBC server. 1. Open a query in Design view. 2. Select the query by clicking anywhere in query Design view outside the design grid and the field lists. 3. Click Properties on the toolbar to display the query's property sheet. 4. Set the ODBCTimeout property to the number of seconds that the query waits before displaying a time-out error. The default is 60 seconds. Note: If you set the property to zero, no time-out error occurs." "Roff, Geoff" <geoff.roff@office2office.biz> wrote in message news:brs93u$87s$1@terabinaries.xmission.com... > > Informix Dynamic Server Version 7.31.UD5 > DYNIX/ptx 4.0 V4.4.10 i386 > SunOS 5.8 Generic_108528-20 sun4u sparc SUNW,Sun-Fire-480R > > Access 97 > ODBC Driver INFORMIX 3.80 32 BIT > > I unfortunately have a business user created MSAccess group of databases > that defies belief, but, guess who has to try and make some sense of the > structure and queries. I have been attempting to create some of this > summarised data on the Sun, extracted from the DYNIX (UmaQ), linking these > tables into the MSAccess environment. We are not getting the performance we > would hope for, although, not totally unexpected. > > The business are refusing to give up MSAccess as they wish to continue to > produce a multitude of adhoc queries, which only make the whole position > worse. The databases are creaking at the seems and they need compacting > every week to avoid the size limitations. We have already moved tables into > their own database and with the growth continuing they will cease to > function within a short timescale. > > Has anyone experience using large tables with complex queries in MSAccess > linked to Informix tables? Would you be prepared to impart your knowledge to > guide me in this? In the long term we will have to provide a back-end > application with some client-side FE, but, initially I must try and stop the > environment colapsing. I do have some linked tables that appear to perform > reasonably well but the more complicated enquiries just fail to perform. > > TIA > Geoff