Question re error handling code (1 Viewer)

[KirRoyale]

I have a very simple piece of code which deletes the import error tables for 6 file imports.
Private Sub DeleteImportErrorTables_Click()
'Delete the import error tables for those created due to header row.
DoCmd.DeleteObject acTable, "Call0CurrentFile_ImportErrors"
DoCmd.DeleteObject acTable, "Call1CurrentFile_ImportErrors"
DoCmd.DeleteObject acTable, "Call2CurrentFile_ImportErrors"
DoCmd.DeleteObject acTable, "Call3CurrentFile_ImportErrors"
DoCmd.DeleteObject acTable, "Call6CurrentFile_ImportErrors"
DoCmd.DeleteObject acTable, "Call7CurrentFile_ImportErrors"
End Sub
The problem is that if an import error table is not produced for any of the imports, an error message is displayed saying that the file could not be found.
Could somebody please help me with a simple error handling code to add to this? I did try adding the code in red below but that didn’t work.
Private Sub DeleteImportErrorTables_Click()
On Error GoTo Err_DeleteImportErrorTables _Click
'Delete the import error tables for those created due to header row.
DoCmd.DeleteObject acTable, "Call0CurrentFile_ImportErrors"
DoCmd.DeleteObject acTable, "Call1CurrentFile_ImportErrors"
DoCmd.DeleteObject acTable, "Call2CurrentFile_ImportErrors"
DoCmd.DeleteObject acTable, "Call3CurrentFile_ImportErrors"
DoCmd.DeleteObject acTable, "Call6CurrentFile_ImportErrors"
DoCmd.DeleteObject acTable, "Call7CurrentFile_ImportErrors"
Exit_ DeleteImportErrorTables _Click:
Exit Sub
Err_ DeleteImportErrorTables _Click:
MsgBox Err.Description
Resume Exit_ DeleteImportErrorTables _Click
End Sub
Thank you.

 

[Galaxiom]

Dim db as DAO.Database
Dim tbldef as DAO.TableDef

Set db = Currentdb

For Each tbldef In db.TableDefs
  If Right(tbldef.Name,12) = "ImportErrors" Then
      DoCmd.DeleteObject acTable, tbldef.Name
  End If
Next

 

[KirRoyale]

Thank you, Galaxiom!
That worked a treat and is really short and concise!

 

[Mihail]

Private Sub DeleteImportErrorTables_Click()
On Error Resume Next
..................................
End Sub

should also do the trick.
And it is a little bit more flexible because not depend from tables names.

 

[nanscombe]

I do tend to use the "Resume Next" trick myself but you have to be careful as it might hide an error that it would be useful to know about.


Galaxiom's code neatly gets around the need for the error handler by looking for the existence of the table first rather than blindly trying to delete tables whether they exist or not.

 

[Mihail]

Agree, Nigel.
My option for Resume Next is based by the design for THIS procedure (only statements for remove tables).
On the other hand, your code will remove all tables which have ImportErrors as last 12 characters. In my opinion this is a dangerous approach.
Do not misunderstand, please. It is NOT a bad one. It is only dangerous.

 

[nanscombe]

Personally I might have been a bit more precise for the line

If Right(tbldef.Name,13) = "_ImportErrors" Then

Apart from Microsoft's import, who is going to intentionally create a table with that on the end of it's name?

 

[Mihail]

I understand your view point.
And I understand, also, that you are a lot more skilled than me.
And you MUST be because this is your job, and for me is only a hobby.
And, forgive me, please, but I'll don't stop to disturb you, in order to "force" you to highlight my mistakes. Thank you !

 

[nanscombe]

Not mistakes, just a difference in approaches.

The only pleasure I get is from passing on ideas based on my experience and seeing someone achieving what they set out to.

 

[Galaxiom]

On the other hand, your code will remove all tables which have ImportErrors as last 12 characters. In my opinion this is a dangerous approach.

Yes my code will remove all import error tables so modify the code to suit if you need to keep others. However wouldn't it be somewhat naive for a developer to name their own tables in a naming pattern used by Access? Anyone like to start their table names with "MSys"?

The technique is obviously adaptable to the circumstance.

If we want to be pedantic then we could do this:

For Each tbldef In db.TableDefs
  If Right(tbldef.Name,24) = "CurrentFile_ImportErrors" _
      And Left(tbldef.Name,3) = "Call"  Then
         DoCmd.DeleteObject acTable, tbldef.Name
  End If
Next

Take it to any extreme you wish. Test the remaining character for IsNumeric or for being within a range of numbers.

Another alternative is to put all the table names into an array and loop through the array.

Whichever way, any group of code lines that are almost identical begs to be put into a loop.

Ignoring errors with On Error Resume Next should be a last resort. At least test the error code and show the error to the user if it isn't the one expected.

 

[KirRoyale]

Thank you all for your help and input! I’m learning a lot!

 

[KirRoyale]

I also noticed that, if I run an import twice or 2 imports related to the same table – such as with detailed data and totals (e.g. if the total records are in a different format to the detail), the 2nd import errors table shows up as “….ImportErrors1”. This results in an error as the right most 12 characters are not “ImportErrors”.
So, although I could probably have avoided this by cleverer import or table design, I have slightly amended Galaxiom’s code to have a ‘mid’ function, which solves the above problem.
Private Sub DeleteAllImportErrorTables_Click()
'Delete all import error tables if created.
Dim db As DAO.Database
Dim tbldef As DAO.TableDef
Set db = CurrentDb
For Each tbldef In db.TableDefs
If Mid(tbldef.Name, 18, 12) = "ImportErrors" Then
DoCmd.DeleteObject acTable, tbldef.Name
End If
Next
End Sub

 

[KirRoyale]

Or to also incorporate Nanscombe’s suggestion:
If Mid(tbldef.Name, 17, 13) = "_ImportErrors" Then

 

[Mihail]

@Galaxiom.
Sorry if my intervention seems to be a criticism.
See please post #9. It is addressed to Nigel but it is, I think, equal for you.

More about that:
There is no long from 3 years from my starting, from scratch, to use Access.
All this time the guy's from BYTES, teach me, push me and so on. A BIG THANKS for they.
But, with all their help I think that I know, maybe, 10% from Access.
Now, and only NOW, thanks to you and Nigel, I understand that Access create automatically, in certain conditions, some tables that have _ImportErrors as last characters.
I see this as a big step for me. From now I can google in order to understand this tables, what are, how can be used and so on.

As I say, forgive me, please, but I'll don't stop to "disturb" you, Nigel, Pat and others, maybe with stupid interventions, especially when yours approaches are totally different from my own.

Thank you again and
ALL THE BEST !

 

[nanscombe]

Or to also incorporate Nanscombe’s suggestion:
If Mid(tbldef.Name, 17, 13) = "_ImportErrors" Then

Personally, I would avoid using the Mid() function. It will work but what if you add another import, say Call10CurrentFile_ImportErrors?

It may never happen but all of a sudden _ImportErrors isn't at position 17 anymore.

If you use Right(tbldef.Name, 13) = "_ImportErrors" instead, the rightmost 13 characters will still be the same.

Left(string, numberOfCharacters) - will get the numberOfCharacters on the left of the string
Mid(string, startPosition, numberOfCharacters) - will get the numberOfCharacters at startPosition of the string
Right(string, numberOfCharacters) - will get the numberOfCharacters on the right of the string

 

[nanscombe]

@Galaxiom.
Sorry if my intervention seems to be a criticism.

Please excuse me for bringing this up but I think there may be a little misunderstanding going on.

Be careful when using an "!" after the phrase "thank you" it may not be read the way you intend it to. Some of us use an exclamation mark in that way to show that we are not happy.

I am glad of your help, thank you. - Happy
I know what I am doing and I don't need your help, thank you! - Unhappy

Since you thanked me afterward I'm reading it as the happy version.

Without seeing your face it can be difficult to read what you meant rather than what you wrote.

 

[Mihail]

Oh. I'm sorry for that.
In my language the "!" sign is to put accent on what you say, to highlight the idea:
"Sunt FOARTE fericit !!!!! " = "I am VERY happy. VERY VERY happy."
"Sunt FOARTE nefericit !" = "I'm VERY unhappy."

So I must say thank you, again, Nigel.
Or, in Romanian language, a big, MULTUMESC !!!

Please, correct me any time you see something wrong and, of course, you have time.
Thank you.

 

[nanscombe]

In the UK we do that as well but we also look at the persons face whilst they are saying it.

Thank you! (whilst smiling) -
Thank you! (whilst looking angry) -

Forums aren't good for that, which is why I use the smilies (little faces) to make up for it.

I'm happy
I'm angry
I don't know what you are talking about
You can not be serious
Oops, sorry.

 

[Galaxiom]

@Galaxiom.
Sorry if my intervention seems to be a criticism.
See please post #9. It is addressed to Nigel but it is, I think, equal for you.

Mihail:

You worry too much. On a technical forum like this we say it like it is. It is one of the things I really appreciate about AWF compared to some of the other forums where I have had posts deleted for "being too negative" and been criticised for not "offering salutation and closure to your post". (Google that term and my username if you think I am joking.)

Everyone shares what they do and ideas they come up with and see the responses. It is a fantastic way to have your techniques critiqued and refine them.

It isn't about personal prestige though I have seen some users here take that line. It doesn't bother me that they do because I really only care about the technical issues.

In this regard and if it bothers you please be most aware of the the Australians on the site though it certainly is not limited to us. On the whole, though not universally, in our culture we tend to be very direct and matter of fact about things. (And definitely stay away from the Water Cooler thread.)

You seem a little defensive about your limited experience. You shouldn't be. Everyone's input is appreciated.

We all went through answering the less complex questions as we developed our knowledge. We started by answering the regular questions and we learnt from the feedback. The more experienced users here keep an eye on the responses and will quickly step in if there is something to clarify.

Indeed, at one stage my biggest motivation for answering questions was the anticipation that one of our power posters would teach me something better. The situations where the advanced developers argued the points are the best learning I have experienced here.

The input from upcoming developers is incredibly important. It provides the opportunity for the most experienced to handle the advanced questions as well as the time to take the overview.

There is absolutely nothing to be embarrassed about when someone offers more information about one's techniques. We are all still learning. There is not a single user on this site that has not benefited from the criticism received here.

I am sure that in time you will be one of the one's where we look at a forum index, see a question with your response, know how you will have responded and not even look.

Of course there are others where we know how they will have responded and visit looking for the argument ... (ahem) discussion.

There are many ways to do things and they all have advantages and costs. The more of them we know about the better developers we will all be.

 

[Galaxiom]

Be careful when using an "!" after the phrase "thank you" it may not be read the way you intend it to. Some of us use an exclamation mark in that way to show that we are not happy.

Exclamation meas just that. It is meant to draw attention. I very rarely use it in writing because because I believe that emphasis can generally be conveyed in the words and phrasing. To me the exclamation is something most used in reporting speech. "Stop!" ; "No!"

If I use exclamation I will in effect almost be be quoting myself.

Anyone who uses it to indicate unhappy (or perhaps more accurately, sarcasm) is misunderstanding the conventional usage of the English language.

I have a particular hatred for exclamation in popup messages for applications. Few exclamations are so inane and inappropriate as a message from an application indicating that an expected task has been completed.

"File Import Complete!"

Why exclaim? It is like the computer saying "Oh wow I didn't know I could do that." Or "Look at me, aren't I clever.".

Or does it mean "I'm finished and I am angry."?