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

Marty,

Thanks for the feedback.  The substr() was the solution...not the problem.  
Substr() was what made the error go away on machine1... and allowed machine2 
to interpret 100 as 100 and not as 1 .   Below this email is the current 
situation...followed by the code used.

Also, you stated "I don't know of any faster way than standard read through 
the ado fields and do an update to an access table "...This is my first 
attempt at importing records in this method...Using the Substr() I've gotten 
the code to loop through the recordset...but how/what function do I use to 
get that recordset into a table?

Thanks,

Mark


>>>>>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

>**********************************************
>         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=;UID=;PWD=;DATABASE=;HOST=;SRVR=;SERV=;PRO=onsoctcp;"
>
>'****   ' open connection
>     cnConnection.Open strConnectionString
>
>     ' query
>     'strSQL = "SELECT case_id FROM ps_rc_case WHERE case_id <100;"   'OLD 
>SQL
>     strSQL = "SELECT substr(case_id,1) as ttt from ps_rc_case WHERE 
>case_id<100"  'NEW SQL
>
>     rsRecordset.Open strSQL, cnConnection, adOpenKeyset, adLockReadOnly
>
>     ' loop through recordset
>     Do While Not rsRecordset.EOF
>
>         Debug.Print rsRecordset("case_id")  '***HERE WAS THE PROBLEM 
>BEFORE SQL CHANGE>
>         rsRecordset.MoveNext
>
>     Loop
>
>***************************************