Run-time error "7": Error executing statement! Error:[Microsoft][ODBC dBase Driver]

[DontB3]

Hi, I'm new in this forum, and i hope someone can help.

I'm creating an automatic application that transfer a database from Access -> DBF -> Oracle.

When My App try to execute Insert SQL statement from DBF to Oracle database i found an error like this

{Run-time error "7": Error executing statement! Error:[Microsoft][ODBC dBase Driver] System resource exceeded.}

and then my program crash! i have to close my app, and start again.
could some one help me to solve my problem, below this is Example of SQL statement wrote for transfering databse from dbf to oracle:

Private Sub Simpan_AKU()
2274   On Error GoTo vbwErrHandler
2275   On Error GoTo B_Aku
2276   database_ora.BeginTrans
2277   If Adodc_DBF.Recordset.RecordCount > 0 Then
2278       Adodc_DBF.Recordset.MoveLast
2279       pbarStatus.Min = 0
2280       pbarStatus.Max = Adodc_DBF.Recordset.RecordCount
2281       Adodc_DBF.Recordset.MoveFirst
2282       ssql = "delete amu_cust_temp_t "
2283       ssql = ssql & " where kd_Store = '" & Mid(xnama_file, 1, 4) & "'"
2284       ssql = ssql & " and tgl_input = to_date('" & dtPilih.Value & "','dd-mm-rrrr')"
2285       database_ora.Execute ssql
2286       xOno = 0
2287   Else
2288       xOno = 1
2289   End If
2290   coun = 0
2291   Do While Adodc_DBF.Recordset.EOF = False
2292       coun = coun + 1
2293       pbarStatus = coun
2294       ssql = " insert into amu_cust_temp_t (KD_STORE, NO_KARTU, NM_PANG, "
2295       ssql = ssql & " ALAMAT, TELP, KD_KASIR, TGL_INPUT, TGL_PROSES, FLAG) "
2296       ssql = ssql & " values ("
2297       ssql = ssql & "'" & Adodc_DBF.Recordset![Kd_Store] & "',"
2298       ssql = ssql & "'" & Adodc_DBF.Recordset![NO_KARTU] & "',"
2299       ssql = ssql & "'" & Adodc_DBF.Recordset![NMPANG] & "',"
2300       ssql = ssql & "'" & Left(Trim(Adodc_DBF.Recordset![alamat]), 40) & "',"
2301       ssql = ssql & "'" & Adodc_DBF.Recordset![TELP] & "',"
2302       ssql = ssql & Val(Adodc_DBF.Recordset![kd_kasir] & "") & ","
2303       ssql = ssql & "'" & Format(Adodc_DBF.Recordset![TGL_INPUT], "dd-mmm-yyyy") & "',"
2304       ssql = ssql & "'" & Format(Date, "dd-mmm-yyyy") & "',0)"
2305       database_ora.Execute ssql
2306       xOno = 1
2307       Adodc_DBF.Recordset.MoveNext
2308   Loop
2309   Adodc_DBF.Recordset.Close
2310   If xOno > 0 Then
2311     On Error GoTo B_Log
2312     ssql = "insert into amu_log_data_toko_t ("
2313     ssql = ssql & " KD_STORE,TANGGAL,TGL_RUBAH,AKU "
2314     ssql = ssql & " ) VALUES ( "
2315     ssql = ssql & "'" & Mid(xnama_file, 1, 4) & "',"
2316     ssql = ssql & "'" & Format(dtPilih, "dd-mmm-yyyy") & "',"
2317     ssql = ssql & "'" & Format(Date, "dd-mmm-yyyy") & "',"
2318     ssql = ssql & "1) "
2319     database_ora.Execute ssql
2320   End If
2321   database_ora.CommitTrans
2322   Exit Sub
2323 B_Log:
2324   If Err.Number = -2147217873 Then
2325       ssql = "update amu_log_data_toko_t set "
2326       ssql = ssql & " TGL_RUBAH='" & Format(Date, "dd-mmm-yyyy") & "', "
2327       ssql = ssql & " AKU = nvl(AKU,0)+1"
2328       ssql = ssql & " where "
2329       ssql = ssql & "kd_store = '" & Mid(xnama_file, 1, 4) & "' and "
2330       ssql = ssql & "tanggal = '" & Format(dtPilih, "dd-mmm-yyyy") & "'"
2331       database_ora.Execute ssql
2332       Resume Next
2333   Else
2334       Print #1, Time & " " & Err.Description & " " & xnama_file
2335       database_ora.RollbackTrans
2336       database_ora.CommitTrans
2337       Exit Sub
2338   End If
2339   Exit Sub
2340 B_Aku:
2341   If Err.Number = -2147217873 Then
2342       Resume Next
2343   Else
2344       Print #1, Time & " " & Err.Description & " " & xnama_file
2345       database_ora.RollbackTrans
2346       database_ora.CommitTrans
2347       Exit Sub
2348   End If
' <VB WATCH>
2349       Exit Sub
    ' ----- Error Handler ------
vbwErrHandler:
    Const VBWPROCEDURE = "Simpan_Aku"
 
    Select Case MsgBox("Error " & Err.Number & vbCrLf & _
                      Err.Description & vbCrLf & _
                     "Location: " & VBWPROJECT & "." & VBWMODULE & "." & VBWPROCEDURE & vbCrLf & _
                     "Line " & Erl, _
                     vbAbortRetryIgnore)
      Case vbAbort
          End
      Case vbRetry
          Resume
      Case vbIgnore
          Resume Next
  End Select
 
End Sub

is there Something Wrong in My Code???

[fplesco]

Hi, I'm new in this forum, and i hope someone can help.

I'm creating an automatic application that transfer a database from Access -> DBF -> Oracle.

When My App try to execute Insert SQL statement from DBF to Oracle database i found an error like this

{Run-time error "7": Error executing statement! Error:[Microsoft][ODBC dBase Driver] System resource exceeded.}

and then my program crash! i have to close my app, and start again.
could some one help me to solve my problem, below this is Example of SQL statement wrote for transfering databse from dbf to oracle:

Private Sub Simpan_AKU()
2274   On Error GoTo vbwErrHandler
2275   On Error GoTo B_Aku
2276   database_ora.BeginTrans
2277   If Adodc_DBF.Recordset.RecordCount > 0 Then
2278       Adodc_DBF.Recordset.MoveLast
2279       pbarStatus.Min = 0
2280       pbarStatus.Max = Adodc_DBF.Recordset.RecordCount
2281       Adodc_DBF.Recordset.MoveFirst
2282       ssql = "delete amu_cust_temp_t "
2283       ssql = ssql & " where kd_Store = '" & Mid(xnama_file, 1, 4) & "'"
2284       ssql = ssql & " and tgl_input = to_date('" & dtPilih.Value & "','dd-mm-rrrr')"
2285       database_ora.Execute ssql
2286       xOno = 0
2287   Else
2288       xOno = 1
2289   End If
2290   coun = 0
2291   Do While Adodc_DBF.Recordset.EOF = False
2292       coun = coun + 1
2293       pbarStatus = coun
2294       ssql = " insert into amu_cust_temp_t (KD_STORE, NO_KARTU, NM_PANG, "
2295       ssql = ssql & " ALAMAT, TELP, KD_KASIR, TGL_INPUT, TGL_PROSES, FLAG) "
2296       ssql = ssql & " values ("
2297       ssql = ssql & "'" & Adodc_DBF.Recordset![Kd_Store] & "',"
2298       ssql = ssql & "'" & Adodc_DBF.Recordset![NO_KARTU] & "',"
2299       ssql = ssql & "'" & Adodc_DBF.Recordset![NMPANG] & "',"
2300       ssql = ssql & "'" & Left(Trim(Adodc_DBF.Recordset![alamat]), 40) & "',"
2301       ssql = ssql & "'" & Adodc_DBF.Recordset![TELP] & "',"
2302       ssql = ssql & Val(Adodc_DBF.Recordset![kd_kasir] & "") & ","
2303       ssql = ssql & "'" & Format(Adodc_DBF.Recordset![TGL_INPUT], "dd-mmm-yyyy") & "',"
2304       ssql = ssql & "'" & Format(Date, "dd-mmm-yyyy") & "',0)"
2305       database_ora.Execute ssql
2306       xOno = 1
2307       Adodc_DBF.Recordset.MoveNext
2308   Loop
2309   Adodc_DBF.Recordset.Close
2310   If xOno > 0 Then
2311     On Error GoTo B_Log
2312     ssql = "insert into amu_log_data_toko_t ("
2313     ssql = ssql & " KD_STORE,TANGGAL,TGL_RUBAH,AKU "
2314     ssql = ssql & " ) VALUES ( "
2315     ssql = ssql & "'" & Mid(xnama_file, 1, 4) & "',"
2316     ssql = ssql & "'" & Format(dtPilih, "dd-mmm-yyyy") & "',"
2317     ssql = ssql & "'" & Format(Date, "dd-mmm-yyyy") & "',"
2318     ssql = ssql & "1) "
2319     database_ora.Execute ssql
2320   End If
2321   database_ora.CommitTrans
2322   Exit Sub
2323 B_Log:
2324   If Err.Number = -2147217873 Then
2325       ssql = "update amu_log_data_toko_t set "
2326       ssql = ssql & " TGL_RUBAH='" & Format(Date, "dd-mmm-yyyy") & "', "
2327       ssql = ssql & " AKU = nvl(AKU,0)+1"
2328       ssql = ssql & " where "
2329       ssql = ssql & "kd_store = '" & Mid(xnama_file, 1, 4) & "' and "
2330       ssql = ssql & "tanggal = '" & Format(dtPilih, "dd-mmm-yyyy") & "'"
2331       database_ora.Execute ssql
2332       Resume Next
2333   Else
2334       Print #1, Time & " " & Err.Description & " " & xnama_file
2335       database_ora.RollbackTrans
2336       database_ora.CommitTrans
2337       Exit Sub
2338   End If
2339   Exit Sub
2340 B_Aku:
2341   If Err.Number = -2147217873 Then
2342       Resume Next
2343   Else
2344       Print #1, Time & " " & Err.Description & " " & xnama_file
2345       database_ora.RollbackTrans
2346       database_ora.CommitTrans
2347       Exit Sub
2348   End If
' <VB WATCH>
2349       Exit Sub
    ' ----- Error Handler ------
vbwErrHandler:
    Const VBWPROCEDURE = "Simpan_Aku"
 
    Select Case MsgBox("Error " & Err.Number & vbCrLf & _
                      Err.Description & vbCrLf & _
                     "Location: " & VBWPROJECT & "." & VBWMODULE & "." & VBWPROCEDURE & vbCrLf & _
                     "Line " & Erl, _
                     vbAbortRetryIgnore)
      Case vbAbort
          End
      Case vbRetry
          Resume
      Case vbIgnore
          Resume Next
  End Select
 
End Sub

is there Something Wrong in My Code???

Hi there DontB3 -

Your code is very complicated and it would take quite sometime to trace it. In my perspective, you are the best one to find it out why. Well, its because you have the test environment setup already in your workstation.

For now, maybe I can give you a clue on how to deal with it (debug). Comment all error handlers and labels you have .

1. On Error GoTo vbwErrHandler
2. On Error GoTo B_Aku

And see where that error points at. That way, you can focus on the code that is really causing the error and the program crash. If you happen to locate it, and still dont know why and how to deal with it, you can get back to this forum, okay?

Goodluck...

[DontB3]

Hi there DontB3 -

Your code is very complicated and it would take quite sometime to trace it. In my perspective, you are the best one to find it out why. Well, its because you have the test environment setup already in your workstation.

For now, maybe I can give you a clue on how to deal with it (debug). Comment all error handlers and labels you have .

1. On Error GoTo vbwErrHandler
2. On Error GoTo B_Aku

And see where that error points at. That way, you can focus on the code that is really causing the error and the program crash. If you happen to locate it, and still dont know why and how to deal with it, you can get back to this forum, okay?

Goodluck...

Thank's fplesco for the clue,.. but still i haven't found away to solve this problem. Error handlers on my code it for handling error an write it to a file (vbwErrHandler) and to Update a record on Oracle if theres already same record on the table.

trully This error not often happent, but cause i work on online database that using MsAccess, DBF, and Oracle. My Company said this is a problem, cause i hape to transfer database moreless 200 DBF's file/City(Branch) into Oracle in onenight. and if one of file is crash/erro there's alot a file in Qued.

Maybe Could You tell me what cause that error appear! is there something wrong with my Database Connection or my Query Script?

(if sorry if english not as good as you, i'm still learning. And i hope this is not become a redthinline to join in this forum)

[fplesco]

Thank's fplesco for the clue,.. but still i haven't found away to solve this problem. Error handlers on my code it for handling error an write it to a file (vbwErrHandler) and to Update a record on Oracle if theres already same record on the table.

trully This error not often happent, but cause i work on online database that using MsAccess, DBF, and Oracle. My Company said this is a problem, cause i hape to transfer database moreless 200 DBF's file/City(Branch) into Oracle in onenight. and if one of file is crash/erro there's alot a file in Qued.

Maybe Could You tell me what cause that error appear! is there something wrong with my Database Connection or my Query Script?

(if sorry if english not as good as you, i'm still learning. And i hope this is not become a redthinline to join in this forum)

Hi there -

I have spotted some contrasting statements in your code.

database_ora.RollbackTrans
database_ora.CommitTrans

I am not sure about this because I haven't been using Oracle. I am used to MS SQL Server backend. But I think these are contrasting statements.

Since, it is in the Error Handler statement block, I think what you need is only the "database_ora.RollbackTrans".