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

Mark A Matte markamatte at hotmail.com
Mon Jan 31 08:12:47 CST 2005


Tom,

Just for testing...I tried to pull 1 record and 1 field using:
strSQL = "SELECT ps_rc_case.case_id FROM ps_rc_case WHERE 
(((ps_rc_case.case_id) = 100));"

Any ideas?

Thanks, Again,

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: Mon, 31 Jan 2005 10:14:24 -0000
>
>Hi Mark
>
>What does your SQL query look like?
>
>Cheers,
>Tom
>
>
>-----Original Message-----
>From: Mark A Matte [mailto:markamatte at hotmail.com]
>Sent: 28 January 2005 17:41
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] Data from Informix to Access - dirty reads?
>
>Ok...I think I'm almost there.  With a little tweaking I've made it to:
>Debug.Print rsRecordset("myField1")  'etc...
>
>Then I get this error:"Multiple-step OLE DB operation generated errors.
>Check eash OLE DB status value, if available.  No work was done."
>
>Any suggestions?
>
>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: Fri, 28 Jan 2005 09:38:07 -0000
> >
> >Mark
> >
> >No probs, the following will retrieve data into an ADO recordset.  Check
> >the
> >syntax first mind, as I'm just freestyling this into my email editor...
> >
> >		Dim rsRecordset as ADODB.Recordset
> >		Dim cnConnection as ADODB.Connection
> >
> >		Dim strConnectionString as String
> >		Dim strSQL as String
> >
> >	' instantiate data objects
> >	set rsRecordset = New ADODB.Recordset
> >	Set cnConnection = New ADODB.Connection
> >
> >	' connection string
> >	strConnectionString =
> >"DSN=mydsn;UID=myUID;PWD=myPWD;DATABASE=myDB;HOST=myHost;SRVR=myServer;SERV
>=
> >myPortNumber;PRO=onsoctcp;"
> >
> >	' opne connection
> >	cnConnection.Open strConnectionString
> >
> >	' query
> >	strSQL = "SELECT blah blah FROM yadda yadda WHERE some things"
> >
> >	' pull records
> >	' keyset cursor type is compatible with DAO
> >	rsRecordset.open strSQL, cnConnection, adOpenKeyset, adLockReadOnly
> >
> >
> >	' loop through recordset
> >	Do While Not rsRecordset.EOF
> >
> >		Debug.Print rsRecordset("myField1")  'etc...
> >
> >		rsRecordset.MoveNext
> >
> >	Loop
> >
> >
> >This should get you started - remember, http://www.connectionstrings.com 
>if
> >you get stuck on the connection string.  You'll need a reference set to 
>MS
> >ActiveX Data Objects.
> >
> >HTH
> >Tom
> >
> >
> >
> >-----Original Message-----
> >From: Mark A Matte [mailto:markamatte at hotmail.com]
> >Sent: 27 January 2005 19:56
> >To: accessd at databaseadvisors.com
> >Subject: RE: [AccessD] Data from Informix to Access - dirty reads?
> >
> >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
> >
> >
> >--
> >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
>
>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