Mark A Matte
markamatte at hotmail.com
Mon Feb 7 15:54:20 CST 2005
Marty, Thanks for the link. The linked Informix table shows CASE_ID datatype=NUMBER and fieldsize=DOUBLE. Thanks, Mark >From: MartyConnelly <martyconnelly at shaw.ca> >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, 07 Feb 2005 11:48:14 -0800 > >What is the Informix table field definition of case_id ie. VChar, Char, >Text etc. >It seems like Access is trying to read a null terminated string and is >treating the "0" as a null or Ascii zero. >Here is ado field type mapping between some databases unfortunately >Informix is not included >http://www.able-consulting.com/ADODataTypeEnum.htm > >Mark A Matte wrote: > >>Hello All, >> >>Any feedback on this last post? >> >>Thanks, >> >>Mark >> >> >> >>>From: "Mark A Matte" <markamatte at hotmail.com> >>>Reply-To: Access Developers discussion and problem >>>solving<accessd at databaseadvisors.com> >>>To: accessd at databaseadvisors.com >>>Subject: RE: [AccessD] Data from Informix to Access - dirty reads? >>>Date: Thu, 03 Feb 2005 14:39:12 +0000 >>> >>>Hello Everyone, >>> >>>Thanks for all the feedback. I'm still a little lost on this one. Here >>>is the current status: >>> >>>2 seperate machines: >>>Machine1 >>>NT >>>A97 >>> >>>Machine2 >>>2K Server >>>A97 >>>A2K >>> >>>Machine1 will loop through the records...but will crash when the caseID >>>ends in '0' . When I use the SQL "SELECT case_id from ps_rc_case WHERE >>>case_id<100" and loop through the case_id...I crash on 10...if I select >>> >11 it crashes on 20. If I loop though a different field(text fields) >>>"SELECT Name_First from ps_rc_case WHERE case_id<100" and loop through >>>Name_First...it works fine...but I need the case_id. >>> >>>Machine2 (using A97 or A2k) with the same code will loop through the >>>records without errors...except 10 returns as 1, 20 as 2, 25460 as 2546, >>>etc. >>> >>>So something is going on with both machines when the number ends in '0'. >>> >>>A suggestion from a friends was:"SELECT substr(case_id,1) as ttt from >>>ps_rc_case WHERE case_id<100"...and loop through 'ttt'. This actually >>>works on both machines...I'm just not sure why. >>> >>>Finally...the whole reason behind the original question was 'whats the >>>fastest way to get data from an Informix db to a local Access db'? I had >>>never used ADO...and was using Append queries from a linked Informix >>>table to a local temp table. Now that I have the recordset...whats the >>>most efficient way to get it into my temp table? >>> >>>Thanks Again, >>> >>>Mark >>> >>> >>> >>> >>>>From: Jim Lawrence <accessd at shaw.ca> >>>>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, 03 Feb 2005 03:52:04 -0800 >>>> >>>>Hi Tom: >>>> >>>>You may be right as I have never used a 'read-only' recordset but by >>>>setting >>>>the recordset to 'static' will give the same features and superior >>>>performance....Second only to 'forward-only'. >>>> >>>>Jim >>>> >>>>-----Original Message----- >>>>From: accessd-bounces at databaseadvisors.com >>>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tom Bolton >>>>Sent: Thursday, February 03, 2005 2:11 AM >>>>To: 'Access Developers discussion and problem solving' >>>>Subject: RE: [AccessD] Data from Informix to Access - dirty reads? >>>> >>>>Jim >>>> >>>>If the cursor lock type is set to read-only, you can still scroll >>>>through it >>>>but will raise an error if you try to edit it. >>>> >>>>The ability to read through the cursor depends on it's type - you can >>>>read >>>>through all of them, apart from a forward-only cursor where as the name >>>>suggests you can't scroll backwards i.e. rs.MovePrevious, rs.MoveFirst. >>>> >>>>Cheers >>>>Tom >>>> >>>> >>>> >>>> >>>>-----Original Message----- >>>>From: Jim Lawrence [mailto:accessd at shaw.ca] >>>>Sent: 03 February 2005 06:03 >>>>To: 'Access Developers discussion and problem solving' >>>>Subject: RE: [AccessD] Data from Informix to Access - dirty reads? >>>> >>>>Hi Mark: >>>> >>>>If you set the recordset to read-only (adLockReadOnly) how do you expect >>>>read through the recordset. Try something like: >>>> >>>>rsRecordset.Open strSQL cnConnection, adOpenStatic, adLockOptimistic >>>> >>>>HTH >>>>Jim >>>> >>>>-----Original Message----- >>>>From: accessd-bounces at databaseadvisors.com >>>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte >>>>Sent: Wednesday, February 02, 2005 10:59 AM >>>>To: accessd at databaseadvisors.com >>>>Subject: RE: [AccessD] Data from Informix to Access - dirty reads? >>>> >>>>Thanks for the feed back...but it didn't change anything. Something >>>>else >>>>I've noticed...I can use the same SQL criteria, except select a >>>>different >>>>field and I can loop through those records without a problem...it just >>>>occurs when I try to display or reference the case_id when it is more >>>>than 2 >>>> >>>>digits? >>>> >>>>I'm thoroughly confused at this point...and once I get it to loop >>>>through >>>>the recordset...How do I get the recordset into a local/temp table? >>>>Although...without the case_id...the last questions doesn't really >>>>matter >>>> >>>>Thanks, >>>> >>>>Mark >>>> >>>>-- >>>>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 >> >> >> > >-- >Marty Connelly >Victoria, B.C. >Canada > > > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com