MartyConnelly
martyconnelly at shaw.ca
Mon Feb 7 19:03:33 CST 2005
SELECT substr(case_id,1) as ttt from ps_rc_case WHERE case_id<100" You might want to try substr(case_id,3) This might get you 3 characters returned instead of one and maybe substr is doing a forced cast to text or number, I am not sure I may have some code to return the ado rst.field.type number and see what it really is. I am confused as to what is happening, I assume you are using a pass through query cause there is no substr intrinsic function in Access SQL also Oracle and DB2 versions of substr have 3 arguments substr STRING, POS, LENGTH Informix internal sql function substring has two arguments columnStart, columnEnd I don't know of any faster way than standard read through the ado fields and do an update to an access table Mark A Matte wrote: > 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 > > > -- Marty Connelly Victoria, B.C. Canada