Sub error handling

[merry_fay]

Hi,

In my code, just in case something goes wrong, I use the error handling
method:

On Error GoTo ErrMsg
code...
ErrMsg:
MsgBox "There has been an error importing this file"
DoCmd.DeleteObject acTable, "Temp"

On this particular form, I'm creating a temporary link to an excel file
which means creating a Temp table in my database.
IF for some reason, there happened to be an error after this link had been
created, then this table has to be deleted or the next time the code was run,
it would create Temp1 which wouldn't work with my queries.
Of course, if an error happened before the link was created, then an error
would occur when deleting the table. I tried to solve this using:


ErrMsg:
MsgBox "There has been an error importing this file"
On Error GoTo ErrMsg2
DoCmd.DeleteObject acTable, "Temp"
ErrMsg2:
End Sub

This doesn't work though. Can anyone help please?

Thanks

 

[Steve Schapel]

Merry Fay,

One way to do this in VBA is:

Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
If tdf.Name = "Temp" Then
CurrentDb.TableDefs.Delete tdf.Name
Exit For
End If
Next tdf

 

[merry_fay]

Hi Steve,

Sorry for the delaying in replying -it took me a while to find the post
again! Thanks for the code, it's just what I need.

Just a couple more questions, to help make my database more efficient:
1. When it loops through the tables, does it do them in alphabetical order
or in the order I have them in the database (eg by description)

2. Which bit of code runs faster?
CurrentDb.TableDefs.Delete "Temp"
or
DoCmd.DeleteObject acTable, "Temp"
If you have 2 different bits of code that do the same thing, how can you
tell which is likely to run faster?

Thanks
merry_fay

 

[merry_fay]

Another question:

When I type the subject of this post, or any combination of the words in the
subject of this post into the 'Search For:' box in this forum & select
'Access Database Macros' which is where it is in the 'In:' section, it
doesn't find it.
I tried using bits of the message too, but it still couldn't find it.
The only way I can get to it is by scrolling through all the posts in date
order.

Is there anywhere to let the administrators know this or is this problem
being worked on?

Thanks

 

[Steve Schapel]

Merry_fay,

I'm afraid I'm not going to be much use to you, as I don't know the answer
to these questions.

You could test your first question yourself, by running this code:
Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
Debug.Print tdf.Name
Next tdf
.... and then look in the Immediate Window to see the order they were
processed in.

You could test the second question yourself too, in theory, by running a
number of trials of each of the two approaches in code, looping through all
the tables in a database file, and by recording the start time and end time
of the process. I say "in theory" because I imagine you would need to be
deleting hundreds or even thousands of tables in order to get a reliable
measure. My "hunch" would be that the CurrentDb.TableDefs.Delete method
would be faster, measured in nanoseconds, but don't really know.

 

[Steve Schapel]

Merry_fay,

I have to apologise again. I do not approach this group via the online
forum. I use a newsreader (in my case Windows Live Mail) to access the
group, which I find much easier. However, if you want to pursue it (and I'm
sure it would be helpful if you did), I believe there is a link somewhere on
that forum website where you can give feedback?