MartyConnelly
martyconnelly at shaw.ca
Mon Feb 7 13:48:14 CST 2005
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