[AccessD] Data from Informix to Access - dirty reads?

Mark A Matte markamatte at hotmail.com
Tue Jan 25 08:49:26 CST 2005


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





More information about the AccessD mailing list