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

MartyConnelly martyconnelly at shaw.ca
Mon Jan 24 09:45:03 CST 2005


*	Create a table in Access and destroy it when finished with it. Can't
think of a better way to induce bloating...

If this is one solution you could use a temp mdb to store the table, 
probably use a skeleton
master temp mdb and copy it initially to get your temp mdb going.
For methods and problems associated with using a temp mdb to avoid FE 
bloat see

Bloating Front End (FE) Microsoft Access MDB/MDEs
http://www.granite.ab.ca/access/bloatfe.htm

Roz Clarke wrote:

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

-- 
Marty Connelly
Victoria, B.C.
Canada






More information about the AccessD mailing list