Row value out of range - Take 2

[Jos� Miselem]

Hello All!

 

A few minutes ago I posted a message indicating an error while updating a table.

 

Well, I was using DAO and the VB program was failing. I switched to ADO and it's working just fine.

 

Any ideas why the SAPDB ODBC driver is behaiving like this?

 

Thanks in advance for your help.

 

 

Saludos,

 

 

Jos�

 

Follows the ADO code that works:

 

Dim MC As New ADODB.Connection
Dim rs As ADODB.Recordset

 

Dim MyError As Error

 

Dim obFolder, obFile As Object

 

Dim FoxDB As DAO.Database
Dim FoxRS As DAO.Recordset

 

Dim wSQLCreate, wSQL, wdType(100), wFolder, wDrive, wFile, wODBC As String

 

Dim wi, wc As Double

 

Dim fso As Object

 

Option Explicit
Private Sub Form_Load()

 

   Form1.Show

 

   cbCount = "Init..."
   DoEvents

 

   Set fso = CreateObject("Scripting.FileSystemObject")

 

   Set MC = CreateObject("ADODB.Connection")
   Set rs = CreateObject("ADODB.Recordset")

 

   wODBC = "ODBC;DATABASE=TST;UID=dba;PWD=dba;DSN=TST"
   MC.Open (wODBC)

 

   wDrive = "f:\"
   wFolder = "DBF"

 

   wdType(10) = "varchar"
   wdType(8) = "date"
   wdType(7) = "float"

 

   Main

 

End Sub

 

Private Sub Main()

 

'   On Error GoTo Error_Handler

 

   wSQLCreate = ""

 

   Set FoxDB = Workspaces(0).OpenDatabase(wDrive & wFolder, False, False, "dBase III;")
   Set obFolder = fso.GetFolder(wDrive & wFolder)

 

   For Each obFile In obFolder.Files

 

      If Right(UCase(obFile), 4) = ".DBF" Then

 

         wFile = Left(obFile, Len(obFile) - 4)
         wFile = Right(wFile, Len(wFile) - 7)

 

         cbCount = wFile
         DoEvents

 

         Set FoxRS = FoxDB.OpenRecordset(wFile)

 

         wSQLCreate = "create table " & wFile & " ( "

 

         For wi = 0 To FoxRS.Fields.Count - 1

 

            If FoxRS(wi).Type = 10 Then
               wSQLCreate = wSQLCreate & FoxRS(wi).Name & " " & wdType(FoxRS(wi).Type) & " (" & FoxRS(wi).Size & ") NULL,"
            Else
               wSQLCreate = wSQLCreate & FoxRS(wi).Name & " " & wdType(FoxRS(wi).Type) & " NULL,"
            End If
         Next

 

         wSQLCreate = Left(wSQLCreate, Len(wSQLCreate) - 1)
         wSQLCreate = wSQLCreate & " ) "

 

         MC.Execute wSQLCreate

 

         rs.Open "select * from " & wFile, MC, adOpenStatic, adLockOptimistic

 

         wc = 0
         FoxRS.MoveFirst

 

         While Not FoxRS.EOF

 

            wc = wc + 1
            If wc Mod 100 = 0 Then
               cbCount = wFile & " " & wc
               DoEvents
            End If

 

            rs.AddNew
               For wi = 0 To FoxRS.Fields.Count - 1
                  rs(wi) = FoxRS(wi)
               Next
            rs.Update

 

            FoxRS.MoveNext
            DoEvents

 

         Wend

 

         rs.Close
         FoxRS.Close

 

      End If

 

   Next

 

   MC.Close

 

Sub_Exit:
   End

 

Error_Handler:
  wi = 0
   For Each MyError In DBEngine.Errors
      With MyError
         wi = wi + 1
         MsgBox .Number & " - " & .Description
      End With
   Next
     
   Select Case Err
      Case 13
         Resume Next
      Case 53
         MsgBox "File not Found", vbCritical
         Resume Sub_Exit
      Case Else
         MsgBox "Unexpected Error# " & Err & "-" & Error(Err), vbCritical
         Resume Sub_Exit
   End Select

 

End Sub