By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 826 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

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

P: 2
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:


Expand|Select|Wrap|Line Numbers
  1. Private Sub Simpan_AKU()
  2. 2274   On Error GoTo vbwErrHandler
  3. 2275   On Error GoTo B_Aku
  4. 2276   database_ora.BeginTrans
  5. 2277   If Adodc_DBF.Recordset.RecordCount > 0 Then
  6. 2278       Adodc_DBF.Recordset.MoveLast
  7. 2279       pbarStatus.Min = 0
  8. 2280       pbarStatus.Max = Adodc_DBF.Recordset.RecordCount
  9. 2281       Adodc_DBF.Recordset.MoveFirst
  10. 2282       ssql = "delete amu_cust_temp_t "
  11. 2283       ssql = ssql & " where kd_Store = '" & Mid(xnama_file, 1, 4) & "'"
  12. 2284       ssql = ssql & " and tgl_input = to_date('" & dtPilih.Value & "','dd-mm-rrrr')"
  13. 2285       database_ora.Execute ssql
  14. 2286       xOno = 0
  15. 2287   Else
  16. 2288       xOno = 1
  17. 2289   End If
  18. 2290   coun = 0
  19. 2291   Do While Adodc_DBF.Recordset.EOF = False
  20. 2292       coun = coun + 1
  21. 2293       pbarStatus = coun
  22. 2294       ssql = " insert into amu_cust_temp_t (KD_STORE, NO_KARTU, NM_PANG, "
  23. 2295       ssql = ssql & " ALAMAT, TELP, KD_KASIR, TGL_INPUT, TGL_PROSES, FLAG) "
  24. 2296       ssql = ssql & " values ("
  25. 2297       ssql = ssql & "'" & Adodc_DBF.Recordset![Kd_Store] & "',"
  26. 2298       ssql = ssql & "'" & Adodc_DBF.Recordset![NO_KARTU] & "',"
  27. 2299       ssql = ssql & "'" & Adodc_DBF.Recordset![NMPANG] & "',"
  28. 2300       ssql = ssql & "'" & Left(Trim(Adodc_DBF.Recordset![alamat]), 40) & "',"
  29. 2301       ssql = ssql & "'" & Adodc_DBF.Recordset![TELP] & "',"
  30. 2302       ssql = ssql & Val(Adodc_DBF.Recordset![kd_kasir] & "") & ","
  31. 2303       ssql = ssql & "'" & Format(Adodc_DBF.Recordset![TGL_INPUT], "dd-mmm-yyyy") & "',"
  32. 2304       ssql = ssql & "'" & Format(Date, "dd-mmm-yyyy") & "',0)"
  33. 2305       database_ora.Execute ssql
  34. 2306       xOno = 1
  35. 2307       Adodc_DBF.Recordset.MoveNext
  36. 2308   Loop
  37. 2309   Adodc_DBF.Recordset.Close
  38. 2310   If xOno > 0 Then
  39. 2311     On Error GoTo B_Log
  40. 2312     ssql = "insert into amu_log_data_toko_t ("
  41. 2313     ssql = ssql & " KD_STORE,TANGGAL,TGL_RUBAH,AKU "
  42. 2314     ssql = ssql & " ) VALUES ( "
  43. 2315     ssql = ssql & "'" & Mid(xnama_file, 1, 4) & "',"
  44. 2316     ssql = ssql & "'" & Format(dtPilih, "dd-mmm-yyyy") & "',"
  45. 2317     ssql = ssql & "'" & Format(Date, "dd-mmm-yyyy") & "',"
  46. 2318     ssql = ssql & "1) "
  47. 2319     database_ora.Execute ssql
  48. 2320   End If
  49. 2321   database_ora.CommitTrans
  50. 2322   Exit Sub
  51. 2323 B_Log:
  52. 2324   If Err.Number = -2147217873 Then
  53. 2325       ssql = "update amu_log_data_toko_t set "
  54. 2326       ssql = ssql & " TGL_RUBAH='" & Format(Date, "dd-mmm-yyyy") & "', "
  55. 2327       ssql = ssql & " AKU = nvl(AKU,0)+1"
  56. 2328       ssql = ssql & " where "
  57. 2329       ssql = ssql & "kd_store = '" & Mid(xnama_file, 1, 4) & "' and "
  58. 2330       ssql = ssql & "tanggal = '" & Format(dtPilih, "dd-mmm-yyyy") & "'"
  59. 2331       database_ora.Execute ssql
  60. 2332       Resume Next
  61. 2333   Else
  62. 2334       Print #1, Time & " " & Err.Description & " " & xnama_file
  63. 2335       database_ora.RollbackTrans
  64. 2336       database_ora.CommitTrans
  65. 2337       Exit Sub
  66. 2338   End If
  67. 2339   Exit Sub
  68. 2340 B_Aku:
  69. 2341   If Err.Number = -2147217873 Then
  70. 2342       Resume Next
  71. 2343   Else
  72. 2344       Print #1, Time & " " & Err.Description & " " & xnama_file
  73. 2345       database_ora.RollbackTrans
  74. 2346       database_ora.CommitTrans
  75. 2347       Exit Sub
  76. 2348   End If
  77. ' <VB WATCH>
  78. 2349       Exit Sub
  79.     ' ----- Error Handler ------
  80. vbwErrHandler:
  81.     Const VBWPROCEDURE = "Simpan_Aku"
  82.  
  83.     Select Case MsgBox("Error " & Err.Number & vbCrLf & _
  84.                       Err.Description & vbCrLf & _
  85.                      "Location: " & VBWPROJECT & "." & VBWMODULE & "." & VBWPROCEDURE & vbCrLf & _
  86.                      "Line " & Erl, _
  87.                      vbAbortRetryIgnore)
  88.       Case vbAbort
  89.           End
  90.       Case vbRetry
  91.           Resume
  92.       Case vbIgnore
  93.           Resume Next
  94.   End Select
  95.  
  96. End Sub
is there Something Wrong in My Code???
Aug 1 '07 #1
Share this Question
Share on Google+
3 Replies


P: 82
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:


Expand|Select|Wrap|Line Numbers
  1. Private Sub Simpan_AKU()
  2. 2274   On Error GoTo vbwErrHandler
  3. 2275   On Error GoTo B_Aku
  4. 2276   database_ora.BeginTrans
  5. 2277   If Adodc_DBF.Recordset.RecordCount > 0 Then
  6. 2278       Adodc_DBF.Recordset.MoveLast
  7. 2279       pbarStatus.Min = 0
  8. 2280       pbarStatus.Max = Adodc_DBF.Recordset.RecordCount
  9. 2281       Adodc_DBF.Recordset.MoveFirst
  10. 2282       ssql = "delete amu_cust_temp_t "
  11. 2283       ssql = ssql & " where kd_Store = '" & Mid(xnama_file, 1, 4) & "'"
  12. 2284       ssql = ssql & " and tgl_input = to_date('" & dtPilih.Value & "','dd-mm-rrrr')"
  13. 2285       database_ora.Execute ssql
  14. 2286       xOno = 0
  15. 2287   Else
  16. 2288       xOno = 1
  17. 2289   End If
  18. 2290   coun = 0
  19. 2291   Do While Adodc_DBF.Recordset.EOF = False
  20. 2292       coun = coun + 1
  21. 2293       pbarStatus = coun
  22. 2294       ssql = " insert into amu_cust_temp_t (KD_STORE, NO_KARTU, NM_PANG, "
  23. 2295       ssql = ssql & " ALAMAT, TELP, KD_KASIR, TGL_INPUT, TGL_PROSES, FLAG) "
  24. 2296       ssql = ssql & " values ("
  25. 2297       ssql = ssql & "'" & Adodc_DBF.Recordset![Kd_Store] & "',"
  26. 2298       ssql = ssql & "'" & Adodc_DBF.Recordset![NO_KARTU] & "',"
  27. 2299       ssql = ssql & "'" & Adodc_DBF.Recordset![NMPANG] & "',"
  28. 2300       ssql = ssql & "'" & Left(Trim(Adodc_DBF.Recordset![alamat]), 40) & "',"
  29. 2301       ssql = ssql & "'" & Adodc_DBF.Recordset![TELP] & "',"
  30. 2302       ssql = ssql & Val(Adodc_DBF.Recordset![kd_kasir] & "") & ","
  31. 2303       ssql = ssql & "'" & Format(Adodc_DBF.Recordset![TGL_INPUT], "dd-mmm-yyyy") & "',"
  32. 2304       ssql = ssql & "'" & Format(Date, "dd-mmm-yyyy") & "',0)"
  33. 2305       database_ora.Execute ssql
  34. 2306       xOno = 1
  35. 2307       Adodc_DBF.Recordset.MoveNext
  36. 2308   Loop
  37. 2309   Adodc_DBF.Recordset.Close
  38. 2310   If xOno > 0 Then
  39. 2311     On Error GoTo B_Log
  40. 2312     ssql = "insert into amu_log_data_toko_t ("
  41. 2313     ssql = ssql & " KD_STORE,TANGGAL,TGL_RUBAH,AKU "
  42. 2314     ssql = ssql & " ) VALUES ( "
  43. 2315     ssql = ssql & "'" & Mid(xnama_file, 1, 4) & "',"
  44. 2316     ssql = ssql & "'" & Format(dtPilih, "dd-mmm-yyyy") & "',"
  45. 2317     ssql = ssql & "'" & Format(Date, "dd-mmm-yyyy") & "',"
  46. 2318     ssql = ssql & "1) "
  47. 2319     database_ora.Execute ssql
  48. 2320   End If
  49. 2321   database_ora.CommitTrans
  50. 2322   Exit Sub
  51. 2323 B_Log:
  52. 2324   If Err.Number = -2147217873 Then
  53. 2325       ssql = "update amu_log_data_toko_t set "
  54. 2326       ssql = ssql & " TGL_RUBAH='" & Format(Date, "dd-mmm-yyyy") & "', "
  55. 2327       ssql = ssql & " AKU = nvl(AKU,0)+1"
  56. 2328       ssql = ssql & " where "
  57. 2329       ssql = ssql & "kd_store = '" & Mid(xnama_file, 1, 4) & "' and "
  58. 2330       ssql = ssql & "tanggal = '" & Format(dtPilih, "dd-mmm-yyyy") & "'"
  59. 2331       database_ora.Execute ssql
  60. 2332       Resume Next
  61. 2333   Else
  62. 2334       Print #1, Time & " " & Err.Description & " " & xnama_file
  63. 2335       database_ora.RollbackTrans
  64. 2336       database_ora.CommitTrans
  65. 2337       Exit Sub
  66. 2338   End If
  67. 2339   Exit Sub
  68. 2340 B_Aku:
  69. 2341   If Err.Number = -2147217873 Then
  70. 2342       Resume Next
  71. 2343   Else
  72. 2344       Print #1, Time & " " & Err.Description & " " & xnama_file
  73. 2345       database_ora.RollbackTrans
  74. 2346       database_ora.CommitTrans
  75. 2347       Exit Sub
  76. 2348   End If
  77. ' <VB WATCH>
  78. 2349       Exit Sub
  79.     ' ----- Error Handler ------
  80. vbwErrHandler:
  81.     Const VBWPROCEDURE = "Simpan_Aku"
  82.  
  83.     Select Case MsgBox("Error " & Err.Number & vbCrLf & _
  84.                       Err.Description & vbCrLf & _
  85.                      "Location: " & VBWPROJECT & "." & VBWMODULE & "." & VBWPROCEDURE & vbCrLf & _
  86.                      "Line " & Erl, _
  87.                      vbAbortRetryIgnore)
  88.       Case vbAbort
  89.           End
  90.       Case vbRetry
  91.           Resume
  92.       Case vbIgnore
  93.           Resume Next
  94.   End Select
  95.  
  96. 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...
Aug 1 '07 #2

P: 2
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)
Aug 1 '07 #3

P: 82
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".
Aug 2 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.