Avatar billede dougheffernan Nybegynder
05. juli 2006 - 12:06 Der er 4 kommentarer og
1 løsning

Error handling

Det med error handling i VBA synes jeg ikke er SÅ nemt igen...

Koden:
***************************************************************
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
  On Error GoTo Form_Open_Error
 
  fRunSQL "DELETE * FROM tblMinTabel1"
  fRunSQL "DELETE * FROM tblMinTabel2"
  fRunSQL "DELETE * FROM tblMinTabell3"
  fRunSQL "DELETE * FROM tblMinTabel4"
  fRunSQL "DELETE * FROM tblMinTabel5"
 
  DoCmd.Close acForm, Me.Name
  Raise
  MsgBox "Alle data slettet!", vbInformation

  Exit Sub

Form_Open_Exit:
   
    Exit Sub

Form_Open_Error:

    MsgBox "Der opstod følgende fejl i programmet: " & vbCrLf & Err.Number & " - " & Err.Description, vbInformation
    fErrorLog Err.Number, Err.Description
   
    Resume Form_Open_Exit

End Sub

Private Function fRunSQL(strSQLString As String)
  On Error GoTo Form_Open_Error
 
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQLString
  DoCmd.SetWarnings True

  Exit Function

Form_Open_Exit:
   
    Exit Function

Form_Open_Error:

    MsgBox "Der opstod følgende fejl i programmet: " & vbCrLf & Err.Number & " - " & Err.Description, vbInformation
    fErrorLog Err.Number, Err.Description
   
    Resume Form_Open_Exit
 
End Function
***************************************************************

I ovenstående kode er tblMinTabell3 "stavet forkert" for at fremprovokere en fejl. Denne fejl fanges i fRunSQL og en fejlmeddelelse vises, men så fortsætter rutinen og til sidst vises "Alle data slettet!", selvom en af tabellerne "ikke blev fundet".

Hvordan skulle ovenstående se ud, hvis jeg vil have vist fejlmeddelelsen og samtidig hvilke tabeller data rent faktisk blev slettet fra? (uden brug af 5 MsgBox'es!)

"Nested error handling..."
Avatar billede terry Ekspert
06. juli 2006 - 19:48 #1
The error handler in Form_Open is never used because the error occurs in fRunSQL. You have to decide what you want before ist possible to give a solution.

Do you want to continue with the other tables even though one delete fails, or do you want to stop as soon as one error occurs?


If I have understood your code correctly then you will only get one error stating that tblMinTabell3 doesnt exist bit the other tables will get delete.
Avatar billede dougheffernan Nybegynder
10. juli 2006 - 08:53 #2
yes, that's correct terry.
Well, it's difficult, VERY difficult to say what I need...on one hand, I'd like to notify the user that all but one table was transferred, but at the same time, I'd like him/her to know that NOT everything is ok. ;)

If I could some how "show a status list" or better yet, implement some form of roll-back......I would definetly prefer a roll-back, to maintain ACID rules, but I haven't worked with transactions in VBA, and the above code is used in Excel!
Avatar billede terry Ekspert
10. juli 2006 - 09:17 #3
I know very little about Excel, bit I'm sure that if you can use a an ADO connection up against an Access database then you can use transactions.

Connection.BeginTrans

'Processing goes here

Connection.CommitTrans 'Or Connection.RollbackTrans
Avatar billede dougheffernan Nybegynder
11. juli 2006 - 10:47 #4
I'll give it a try, terry, thank you for your help. If you could give an "answer", then I'll close the Q.
Avatar billede terry Ekspert
11. juli 2006 - 12:09 #5
Hope you can get it to work, otherwise drop a comment.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester