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

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






More information about the AccessD mailing list