row value out of range

[Jos� Miselem]

Hello All!

 

I am writing a small Visual Basic 6.0 program to convert DBFs files into SAPDB, and whenever the program adds a new record into the SAPDB table, I get "row value out of range".

 

Any ideas what could be causing this?

 

I used SAPDB ODBC 7.3 and 7.4 with the same results.

 

Thanks in advance for your help.

 

 

Saludos,

 

 

Jos�

 

 

Follows the unfinished code:

 

Dim WS As DAO.Workspace
Dim MC As DAO.Connection
Dim rs As DAO.Recordset

 

Dim MyError As Error

 

Dim obFolder, obFile As Object

 

Dim FoxDB As Database
Dim FoxRS As 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")

 

   wODBC = "ODBC;DATABASE=TST;UID=dba;PWD=dba;DSN=TST73"

 

   Set WS = CreateWorkspace("ODBCWorkspace", "dba", "dba", dbUseODBC)

 

   Set MC = WS.OpenConnection("SAPDB", dbDriverCompleteRequired, False, "ODBC;DATABASE=TST;UID=dba;PWD=dba;DSN=TST")

 

   MC.QueryTimeout = 480

 

   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

 

         Set rs = MC.OpenRecordset(wFile, dbOpenDynaset, 0, dbPessimistic)

 

         wc = 0
         FoxRS.MoveFirst

 

         While Not FoxRS.EOF

 

            wc = wc + 1
            cbCount = wFile & " " & wc
            DoEvents

 

           rs.AddNew
               For wi = 0 To FoxRS.Fields.Count - 1
                  rs(wi) = FoxRS(wi)
               Next
            rs.Update  ' The error happens here.

 

            FoxRS.MoveNext
            DoEvents

 

         Wend

 

         rs.Close
         FoxRS.Close

 

      End If

 

   Next

 

   MC.Close
   WS.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