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

Here is a function that reads an external ado source and then writes its 
records and fields to an access table via DAO
Asumes table already created in Access


Function CopytoTable()
  Dim db As Database
  Dim NewRes As DAO.Recordset
  Dim ErrMsg1 As String
  Dim RecCount As Long
  Dim sSQL As String

    
   Dim oConnection As ADODB.Connection
   Dim oRecordset As ADODB.Recordset
   Dim rstSchema As ADODB.Recordset
   Dim sConnStr As String

   'sConnStr = "Provider=SQLOLEDB;Data Source=MySrvr;"  & _
   '     "Initial Catalog=Northwind;User Id=MyId;Password=123aBc;"

   'Access XP Jet 4
   sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Program Files\Microsoft 
Office\Office\Samples\Northwind.mdb;" & _
           "User Id=admin;" & "Password="
        
   On Error GoTo GetDataError
  
   ' Create and Open the Connection object.
   Set oConnection = New ADODB.Connection
   oConnection.CursorLocation = adUseClient
   oConnection.Open sConnStr
  
   sSQL = "SELECT ProductID, ProductName, CategoryID, UnitPrice " & _
            "FROM Products"
  
   ' Create and Open the Recordset object.
   Set oRecordset = New ADODB.Recordset
   oRecordset.Open sSQL, oConnection, adOpenStatic, _
                       adLockBatchOptimistic, adCmdText
 
  '----  Open up Access table to be added to
    Set db = CurrentDb()
   
    Set NewRes = db.OpenRecordset("tbl_New")
'read through input records
  RecCount = 0
  oRecordset.MoveFirst
'
Do While Not oRecordset.EOF
Addit:
  NewRes.AddNew
  '   copy relevant fields
  NewRes![ProductID] = oRecordset![ProductID]
  NewRes![ProductName] = oRecordset![ProductName]
  'CONTINUE COPYING ALL ROWS FROM OLD TBL TO NEW
  NewRes.Update
  RecCount = RecCount + 1
  DoEvents
  If RecCount Mod 10000 = 0 Then
    MsgBox RecCount 'Show progress every 10,000 rows
  End If
   oRecordset.MoveNext
Loop
  MsgBox RecCount 'Show total successful record count
  oRecordset.Close
  NewRes.Close
  oConnection.Close
Proc_Exit:
  Exit Function
GetDataError:
  MsgBox "<Error>" & Error$
   If oConnection Is Nothing Then
      HandleErrs "GetData", oRecordset.ActiveConnection
   Else
      HandleErrs "GetData", oConnection
   End If
 
  oRecordset.MoveNext 'Skip this corrupt row
  Resume Addit    'Continue at Addit
End Function

Sub HandleErrs(sSource As String, ByRef oConnection1 As ADODB.Connection)
Dim sDisplayMsg As String
   sDisplayMsg = sDisplayMsg & "ADO (OLE) ERROR IN " & sSource
   sDisplayMsg = sDisplayMsg & vbCrLf & "Error: " & Err.Number
   sDisplayMsg = sDisplayMsg & vbCrLf & "Description: " & Err.Description
   sDisplayMsg = sDisplayMsg & vbCrLf & "Source: " & Err.Source
  
   If Not oConnection1 Is Nothing Then
      If oConnection1.Errors.Count <> 0 Then
         sDisplayMsg = sDisplayMsg & vbCrLf & "PROVIDER ERROR"
         Dim oError1 As ADODB.Error
         For Each oError1 In oConnection1.Errors
            sDisplayMsg = sDisplayMsg & vbCrLf & "Error: " & oError1.Number
            sDisplayMsg = sDisplayMsg & vbCrLf & "Description: " & 
oError1.Description
            sDisplayMsg = sDisplayMsg & vbCrLf & "Source: " & oError1.Source
            sDisplayMsg = sDisplayMsg & vbCrLf & "Native Error:" & 
oError1.NativeError
            sDisplayMsg = sDisplayMsg & vbCrLf & "SQL State: " & 
oError1.SQLState
         Next oError1
         oConnection1.Errors.Clear
         Set oError1 = Nothing
      End If
   End If
   
   MsgBox "Error(s) occurred. See sDisplayMsg for specific 
information.", , _
           "Hello Data"
   MsgBox sDisplayMsg
   Err.Clear
End Sub
 
 


Mark A Matte wrote:

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

-- 
Marty Connelly
Victoria, B.C.
Canada