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

Mark A Matte markamatte at hotmail.com
Thu Jan 27 13:55:52 CST 2005


Tom,

">Mark - instead of using APPEND queries, would it not be any faster to pull
>the data from Informix in an ADO recordset - I'm doing this via ODBC as 
>this
>is how our Terminal Servers are set up >:¬( - then open a DAO recordset on
>your Access table, and loop round in a do...while copying the records from
>one to the other"

I've never used ADO...can you send a sample of code?

Thanks,

Mark


>From: Tom Bolton <tom.bolton 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: Thu, 27 Jan 2005 10:57:05 -0000
>
>Mark (and Jim)
>
>Sorry for the delay, had a bit on yesterday.  There are indeed ADO/OLEDB
>drivers for Informix, a colleague has installed one but is having a little
>difficulty with the connection string.  Try 
>http://www.connectionstrings.com
>
>
>Mark - instead of using APPEND queries, would it not be any faster to pull
>the data from Informix in an ADO recordset - I'm doing this via ODBC as 
>this
>is how our Terminal Servers are set up >:¬( - then open a DAO recordset on
>your Access table, and loop round in a do...while copying the records from
>one to the other?  Jim has an excellent link explicitly explaining how to 
>do
>just this.  However, I've a sneaking suspicion that he's right, and ODBC is
>at the bottom of your woes - definitely worth exploring OLEDB, as methinks
>ODBC is a little old-hat these days.
>
>It might just be me (ASP/Oracle/SQL Server background) but I like to try 
>and
>avoid letting Jet anywhere near anything that's not solely concerned with
>Access FE tables (for which, of course, it's excellent).
>
>Cheers
>Tom
>
>
>-----Original Message-----
>From: Jim Lawrence [mailto:accessd at shaw.ca]
>Sent: 27 January 2005 04:48
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Data from Informix to Access - dirty reads?
>
>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
>
>--
>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