Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error handler to delete temporary tables/queries

Posted on 2009-04-29
8
Medium Priority
?
359 Views
Last Modified: 2013-11-29
Hi Experts,

I'm looking for your help on writing a error handleing procedure for an Access application i've come up with.

I need this error handler being able to delete temporary tables/queries that might exist during the executions when the program is interrupted due to errors. It could be something like this:

sub deleteTempTables (TableName As String)
and
sub deleteTempQueries (QueryName As String)

Can anyone provide the code?

Thanks a lot!
0
Comment
Question by:LizzJ
  • 3
  • 3
  • 2
8 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 1000 total points
ID: 24258081
For Tables

Kelvin
Dim dbs As Database
Dim cntTemp As Container
Static intCount As Integer
Dim intCounter As Integer
Set dbs = CurrentDb()
Set cntTemp = dbs.Containers("Tables")
intCount = cntTemp.Documents.Count
For intCounter = 0 To intCount - 1
    If TableName = cntTemp.Documents(intCounter).Name Then
        Docmd.DeleteObject acTable, TableName
        Exit Function
    End If
Next intCounter

Open in new window

0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24258086
And For Queries

Kelvin
Dim dbs As Database
Dim cntTemp As Container
Static intCount As Integer
Dim intCounter As Integer
Set dbs = CurrentDb()
Set cntTemp = dbs.Containers("Queries")
intCount = cntTemp.Documents.Count
For intCounter = 0 To intCount - 1
    If QueryName = cntTemp.Documents(intCounter).Name Then
        Docmd.DeleteObject acQuery, QueryName
        Exit Function
    End If
Next intCounter

Open in new window

0
 

Author Comment

by:LizzJ
ID: 24258155
Thanks Kelvin.

Btw, what my error handler should look like? Can you suggest on that? i'm quite new to error handling.
Let's say if error encounters, the two subs you provided will be called.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24258210
At the point where you need this

On error GoTo DelTable (or maybe DelQuery)



Somehwere further down have

DelTable:
     Call deleteTempTables(TableName)
     Now some code to do whatever after table is deleted.

DelQuery:
     Call deleteTempQuery
     More code......
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 24261371
On Error ResumeNext
DoCmd.DeleteObject acTable "TableName"
If Err.Number>0 Then Err.Number=0
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 24261401
Correction...    The same with your queries:

On Error ResumeNext
DoCmd.DeleteObject acQuery, "TableName"
If Err.Number>0 Then Err.Number=0


On Error ResumeNext
DoCmd.DeleteObject acTable, "TableName"
If Err.Number>0 Then Err.Number=0

If the table or query exist, it will delete it.
 
Mike
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 24261411
On Error Resume Next
                                ^--- space was missing

Sorry
0
 

Author Closing Comment

by:LizzJ
ID: 31575816
This is not exactly what i'm looking for but it gives a clue.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question