Jim Lawrence
accessd at shaw.ca
Wed Jan 26 22:48:16 CST 2005
Hi Mark: Your bottle-neck is ODBC. The only way around that is if there is a direct ADO-OLE equivalent. I am sure there are ADO drivers for every version of Informix...check these out: http://www.gold-software.com/LuxenadbExpressdriverforInformixPro-review9342. htm (ADO type driver... shareware. You can download the driver from IBM at: http://www.ibm.com/products/finder/us/en/finders?pg=ddfinder To then use it the code is something like this: Dim objConn As ADODB.Connection Dim gsConnectionString as String gsConnectionString = "Provider=Ifxoledbc;Password=pass; Persist Security Info=True;User ID=user;Data Source=market_report;" objConn.connectionstring = gsConnectionString objConn.Open HTH Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte Sent: Tuesday, January 25, 2005 6:49 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Data from Informix to Access - dirty reads? Hello All, Every week I do pulls from Informix to A97 using ODBC. 1 of my pulls now is around 1 million records. I use temp tables and append queries. This can take a few hours. Some of the replies in this thread imply there is a much 'better/faster' way. Without changing versions of Access...is there a way to 'tweak'/'speed up' this data pull? Thanks, Mark >From: Roz Clarke <roz.clarke at donnslaw.co.uk> >Reply-To: Access Developers discussion and problem >solving<accessd at databaseadvisors.com> >To: "'Access Developers discussion and problem >solving'"<accessd at databaseadvisors.com> >Subject: RE: [AccessD] Data from Informix to Access - dirty reads? >Date: Tue, 25 Jan 2005 09:52:19 -0000 > >Informix doesn't support XML afaik >< > >We did think about this as a way around connection issues for SQL Server >when we thought that we would have our main db replicating to SQL Server >rather than Informix - our recordsets are going to be up to about 50k rows >I >think. I don't know how XML would perform with that volume of data but it's >academic for now. > >Roz > >-----Original Message----- >From: John W. Colby [mailto:jwcolby at colbyconsulting.com] >Sent: 24 January 2005 20:58 >To: 'Access Developers discussion and problem solving' >Subject: RE: [AccessD] Data from Informix to Access - dirty reads? > > >How much data? Can it export it to an XML file in a location where your fe >can use it? XML has a high overhead but can be useful in cases like this. > >John W. Colby >www.ColbyConsulting.com > >Contribute your unused CPU cycles to a good cause: >http://folding.stanford.edu/ > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Roz Clarke >Sent: Monday, January 24, 2005 10:23 AM >To: 'Access Developers discussion and problem solving' >Subject: [AccessD] Data from Informix to Access - dirty reads? > > >Hi all > > >We have a bit of a problem with getting data out of our Informix server >since we recently turned on transaction logging for replication. When >extracting data with an Access XP MDE using pass-through queries, Access >locks entire tables in the Informix database, which causes transaction >errors and makes the Informix database scarily unstable. > >Our Informix suppliers told us that the way to avoid these locking issues >was to set the connection to 'dirty read' before running the SQL. However, >Access cannot execute 2 statements in a pass-through query and it does not >hold the connection open between the execution of 1 statement and the next. >Thus when the query is processed the 'dirty read' setting is no longer in >effect. > >We have been racking our brains trying to come up with a workaround. Some >further options that we have considered are: > >* Stick the data in a temp table in Informix. This is no good because >the temp table is destroyed automatically when the connection is closed and >there's no way to make it persist long enough to bind it to a report. >* Use a view in Informix. This is no good because views in Informix >cannot be set read-only. >* Create a permanent table in Informix and destroy it when finished >with it. This is far from ideal because Informix does not support SELECT >INTO and therefore a table would have to be explicitly constructed with >names columns etc. We really need the system to be flexible so that the >queries can be easily changed. >* Use an ADO recordset. This is a PITA because you cannot bind a >report to a recordset in an MDE and we cannot build the report on the fly - >we are a Terminal Services site so we will have up to 20 users in one FE. >* Create a table in Access and destroy it when finished with it. Can't >think of a better way to induce bloating... > >Has anyone faced this kind of problem before? Any bright ideas? Our >foreheads are starting to bleed... > >TIA > >Roz (and Tom) > > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > >The contents of this message and any attachments are the property of Donns >Solicitors >and are intended for the confidential use of the named recipient only. >They may be legally > privileged and should not be communicated to, or relied upon, by any >other party without >our written consent. If you are not the addressee, please notify us >immediately so that we >can make arrangements for its return. You should not show this e-mail to >any person or > take copies as you may be committing a criminal or civil offence for >which you may be > liable. The statement and opinions expressed in this e-mail message are >those of the >writer, and do not necessarily represent that of Donns Solicitors. >Although any files attached > to this e-mail will have been checked with virus protection software >prior to transmission, >you should carry out your own virus check before opening any attachment. >Donns Solicitors does not accept any liability for any damage or loss which >may be caused >by software viruses... >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com