My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
Posts: 75 Joined: 7-January 11 ![]() | So I'm pretty new to building error handlers within my functions (and its actually been kind of eye opening personally as its allowed to find problem BEFORE they become a problem ![]() I have the below code that I cobbled together that is supposed to import a bunch of excel files into one table in MS Access and I tried to put an error handler in it so that I could identify any files that were not able to be imported but for some reason, it's not flagging the import error that I should be getting from one file (the file in question has a typo in the field "Minimum Warehouse Order"; it says Mininum instead of Minimum). What am I not doing correctly in my code below? Thanks in advance! CODE Option Compare Database Function Import_Excel(myPath As String, myFile As String, myExt As String, myTable As String, Optional myRange As String, Optional mySQLTable As String, Optional mySQLField As String) DoCmd.RunSQL "DELETE * FROM WrongSheetErrorsTable" On Error GoTo ErrHandler: errnum = Err.Number errtxt = Err.Description Do While myFile <> "" If myFile Like myExt Then DoCmd.TransferSpreadsheet acImport, , myTable, myPath & myFile, True, myRange End If myFile = Dir() Loop If DCount("*", "WrongSheetErrorsQuery") > 0 Then DoCmd.OpenQuery "WrongSheetErrorsQuery" Else: MsgBox "All Good" End If Exit Function ErrHandler: Select Case errnum Case 0 'no error. Resume Next Case 91 DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');" Resume Next Case 2391 DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');" Resume Next Case Else 'All other errors. Resume Next End Select End Function |
![]() Post#2 | |
![]() UA Admin Posts: 36,171 Joined: 20-June 02 From: Newcastle, WA ![]() | This part is the problem: CODE On Error GoTo ErrHandler: errnum = Err.Number errtxt = Err.Description It does nothing, as a matter of fact. What you need to do is something like this: CODE Function Import_Excel(myPath As String, myFile As String, myExt As String, myTable As String, Optional myRange As String, Optional mySQLTable As String, Optional mySQLField As String) On Error GoTo ErrHandler .....code goes here ErrHandler: errnum = Err.Number errtxt = Err.Description ...the rest of your error handler code goes her End Function -------------------- My Real Name Is George. Grover Park Consulting is where I did business for 20 years. How to Ask a Good Question Beginning SQL Server |
![]() Post#3 | |
![]() UA Admin Posts: 36,171 Joined: 20-June 02 From: Newcastle, WA ![]() | The reason for this is that Err gets its value AFTER an error is encountered, not before. -------------------- My Real Name Is George. Grover Park Consulting is where I did business for 20 years. How to Ask a Good Question Beginning SQL Server |
![]() Post#4 | |
![]() UtterAccess Moderator Posts: 11,869 Joined: 6-December 03 From: Telegraph Hill ![]() | First thing: Always use Option Explicit This will mean you have to declare your variables. Then, with your code: Your On Error GoTo ErrHandler: line has a colon at the end. This actually makes a label (though how it works with spaces in the label name, I don't know!). So: CODE Option Compare Database Option Exlicit Function Import_Excel(myPath As String, myFile As String, myExt As String, myTable As String, Optional myRange As String, Optional mySQLTable As String, Optional mySQLField As String) On Error GoTo ErrHandler Dim errnum As Long Dim errtxt As String DoCmd.RunSQL "DELETE * FROM WrongSheetErrorsTable" Do While myFile <> "" If myFile Like myExt Then DoCmd.TransferSpreadsheet acImport, , myTable, myPath & myFile, True, myRange End If myFile = Dir() Loop If DCount("*", "WrongSheetErrorsQuery") > 0 Then DoCmd.OpenQuery "WrongSheetErrorsQuery" Else MsgBox "All Good" End If Exit_Here: If DCount("*", "WrongSheetErrorsQuery") > 0 Then DoCmd.OpenQuery "WrongSheetErrorsQuery" Else MsgBox "All Good" End If Exit Function ErrHandler: errnum = Err.Number errtxt = Err.Description Select Case errnum Case 91 DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');" Case 2391 DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');" Case Else 'All other errors. MsgBox errtxt End Select Resume Exit_Here End Function hth, d -------------------- Regards, David Marten |
![]() Post#5 | |
Posts: 6,177 Joined: 11-November 10 From: SoCal, USA ![]() | ...and, btw, you're missing the Option Explicit statement at the top of your code, as Option Compare Database Option Explicit declaring Option Explicit at the top of every module - including form and report modules - is generally considered to be a best practice. you can set the option at the software level, so that every new module you create in every database you work in will automatically add the statement. in the VBE window, from the menu click Tools | Options, then in the Options dialog, make sure the box is checked next to Require Variable Declaration. ![]() for existing modules, you'll need to add the statement manually. hth tina EDIT: ha, David, you're too quick for me! :) This post has been edited by tina t: Nov 19 2019, 03:02 PM -------------------- "the wheel never stops turning" |
![]() Post#6 | |
![]() UA Admin Posts: 36,171 Joined: 20-June 02 From: Newcastle, WA ![]() | And me, too. Missed the Option Explicit problem entirely. ![]() -------------------- My Real Name Is George. Grover Park Consulting is where I did business for 20 years. How to Ask a Good Question Beginning SQL Server |
![]() Post#7 | |
Posts: 75 Joined: 7-January 11 ![]() | @GroverParkGeorge, tina-t, and cheekybuddha - thank you all for your suggestions! When I make yalls recommended changes though, it gives a "runtime erro '3075'" because its trying to import one of the columns in the imported spreadsheet into the error table. I dont need that information, just the filename, error number, and error description. Any idea why? Specifically, this is happening at the QUOTE DoCmd.RunSQL "INSERT INTO [ImportErrorLog] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');" code under Case 2391This post has been edited by aggiemarine07: Nov 19 2019, 03:24 PM |
![]() Post#8 | |
![]() Posts: 332 Joined: 22-December 14 From: Grand Junction, CO, USA ![]() | Hello, is errNum a number or a string? If it is a number, then you might try replacing CODE VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "') with CODE VALUES ('" & myFile & "'," & errnum & ",'" & errtxt & "') i.e. remove the two single quote characters surrounding errnum. |
![]() Post#9 | |
![]() UtterAccess Moderator Posts: 11,869 Joined: 6-December 03 From: Telegraph Hill ![]() | OK, So, you want to keep looping even when there s an error: CODE Function Import_Excel(myPath As String, myFile As String, myExt As String, myTable As String, Optional myRange As String, Optional mySQLTable As String, Optional mySQLField As String) On Error GoTo ErrHandler Dim errnum As Long Dim errtxt As String DoCmd.RunSQL "DELETE * FROM WrongSheetErrorsTable" Do While myFile <> "" If myFile Like myExt Then DoCmd.TransferSpreadsheet acImport, , myTable, myPath & myFile, True, myRange End If Resume_Here: myFile = Dir() Loop Exit_Here: If DCount("*", "WrongSheetErrorsQuery") > 0 Then DoCmd.OpenQuery "WrongSheetErrorsQuery" Else MsgBox "All Good" End If Exit Function ErrHandler: errnum = Err.Number errtxt = Err.Description Select Case errnum Case 91, 2391 DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');" Resume Resume_Here Case Else 'All other errors. MsgBox errtxt Resume Exit_Here End Select End Function Also, note Daniel's comment about the ErrorNumber field datatype - I left your INSERT statement as is, but change it if Daniel's comment applies. hth, d -------------------- Regards, David Marten |
![]() Post#10 | |
Posts: 75 Joined: 7-January 11 ![]() | @Daniel_Stokley - within the table the field errnum is "short text"; the same goes for the filename and errtext fields. I tried your suggestion and it still gives me the same error as before. @cheekybudha - I do want it to keep looping through all of the import files. then when I am done importing, I want it to open the table to show me which files it was unable to import This is the exact syntax I get when I try yalls code: QUOTE "Run-time error '3075': Syntax error (missing operator in query express "Field: 'Mininum Warehouse Order' doesnt exist in destination table 'PLOGImportTable.");'. That field (mininum warehouse order) is the error I am trying to log in my table. So Access is catching the error like it is supposed to and is prompting me that there is an error but its not writing anything to the table. Let me know if this doesnt make sense. |
![]() Post#11 | |
![]() UtterAccess Moderator Posts: 11,869 Joined: 6-December 03 From: Telegraph Hill ![]() | Hmmm... Try using this in the error handler: CODE ' ... DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & Replace(errtxt, "'", "''") & "');" ' ... hth, d -------------------- Regards, David Marten |
![]() Post#12 | |
Posts: 75 Joined: 7-January 11 ![]() | @cheekybuddha - that did it! what did you do? |
![]() Post#13 | |
![]() UtterAccess Moderator Posts: 11,869 Joined: 6-December 03 From: Telegraph Hill ![]() | I noticed that you got the error when the errtxt contained single quotes. Since you are using single quotes to delimit the string passed on your SQL INSERT statement then you must also escape any single quotes contained within the string. This is done by doubling them up, which is what the Replace() function is doing. hth, d -------------------- Regards, David Marten |
![]() Post#14 | |
Posts: 75 Joined: 7-January 11 ![]() | awesome, thanks so much for your help! |
![]() Post#15 | |
![]() UtterAccess Moderator Posts: 11,869 Joined: 6-December 03 From: Telegraph Hill ![]() | ![]() -------------------- Regards, David Marten |
![]() Post#16 | |
![]() UtterAccess Moderator Posts: 11,869 Joined: 6-December 03 From: Telegraph Hill ![]() | Hi again, If you are doing any amount of coding you may wish to consider an add-in for the VBA editor like MZ-Tools. This is a great tool that includes the ability to add an error handler to your procedure at the click of a button (plus a myriad of other really helpful features). It used to be free but unfortunately costs a little now. Other members here may be able to suggest other similar tools which are still free. hth, d -------------------- Regards, David Marten |
![]() Post#17 | |
![]() UtterAccess VIP Posts: 4,746 Joined: 5-June 07 From: UK ![]() | the real problem with errors is how you react to them so given your handler - you cannot just assume you can continue to run code after encountering an error, so resume next after an error may not be sensible. note that you won't get to the error handler with no error (an error of zero) CODE ErrHandler: Select Case errnum Case 0 'no error. Resume Next Case 91 DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');" Resume Next Case 2391 DoCmd.RunSQL "INSERT INTO [WrongSheetErrorsTable] ([FileName],[ErrorNumber],[ErrorText]) VALUES ('" & myFile & "','" & errnum & "','" & errtxt & "');" Resume Next Case Else 'All other errors. Resume Next End Select with regard to reporting errors then you can use the error object err is the default case of err.number msgbox "Error encountered " & vbcrlf & "Error: " & err & " Description: " & err.description -------------------- Dave (Male) (Gemma was my dog) |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 6th December 2019 - 04:14 PM |