Steps to Reproduce the Problem

1. Create a table in Oracle with the following statement, by using SQL*Plus or some other database utility, and by using the DEMO UserID and DEMO password:

     CREATE TABLE TABLE1 (FIELD1 NUMERIC (4,0))
   						

2. Create a Visual Basic application and put the following code in the Form's Load section (you need to create a DSN named "ORACONN" or modify the code to reflect a DSN you have already created):

   cnn.Open "dsn=ORACONN;uid=demo;pwd=demo", , , -1
     cmd.ActiveConnection = cnn
    
     For i = 1 To 7000
       cmd.CommandText = "insert into DEMO.TABLE1 (FIELD1) Values(" & Str(i) & ")"
       cmd.Execute
     Next i
   						

3. Create a Visual Basic form with a list box and a button. In the handler for the button, paste the following code:

   Dim cnn As ADODB.Connection
       Dim cmd As ADODB.Command
       Dim rstRecordIDs As ADODB.Recordset
       Dim strCnn As String
   
       Set cnn = New ADODB.Connection
       Set cmd = New ADODB.Command
      
      On Error GoTo Err_cmdDBTest_Click
      
      'Open recordset from table.
      
      'Connection string #1 using OLEDB provider for oracle fails when attempting to move to
      'rows around 5000.
      strCnn = "Provider=MSDAORA.1;Password=demo;User ID=demo;Data Source=dseoracle8"
      
      'Connection string #2 using MS ODBC for Oracle works fine
      'strCnn = "DSN=ORACONN;pwd=demo;uid=demo"
      
      Set rstRecordIDs = New ADODB.Recordset
      rstRecordIDs.CursorType = adOpenForwardOnly
      rstRecordIDs.LockType = adLockReadOnly
      rstRecordIDs.CursorLocation = adUseServer
      rstRecordIDs.MaxRecords = 100000
      rstRecordIDs.CacheSize = 100
      Debug.Print cnn.Version
      rstRecordIDs.Open "SELECT FIELD1 FROM DEMO.TABLE1 ORDER BY FIELD1", strCnn, , , adCmdText
       
       If rstRecordIDs.EOF Then
         MsgBox "No records!"
         Exit Sub
       End If
      
      rstRecordIDs.MoveFirst
      If rstRecordIDs.EOF Then
        MsgBox "No Rows!"
        Exit Sub
      End If
      
      Do While True
        lstData.AddItem "Record ID: " & rstRecordIDs!FIELD1
        rstRecordIDs.MoveNext
        
        If rstRecordIDs.EOF Then
          MsgBox "At end of recordset!"
          Exit Do
        End If
      Loop
      
      rstRecordIDs.Close
      Exit Sub
   Err_cmdDBTest_Click:
       Debug.Print "Error Description : " + Err.Description
   End Sub"
   						

Back to the top