Automatically delete a temporary table after usage

Hi,

I have the following queries being called. They out put to "OUT" and "IN" tables respectfully

DoCmd.OpenQuery "OUT"
DoCmd.OpenQuery "IN"

DoCmd.OpenQuery "RESULT"

Both of these tables are then being merged in another query called "RESULT" which outputs to table called "RESULT"

What I want is that I do not want the user to see the "IN" & "OUT" tables. I understand that I can hide them but I would rather like to delete them. Could you please help me to delete them.

Now I have done this before but the method seems too code heavy and it exits the sub so I cannot use the same code :/

    On Error GoTo EH

    CurrentDb.TableDefs.Delete ("Get65")
    
    CurrentDb.Execute "SELECT INTER.OTCOMBO, INTER.DTCOMBO, INTER.TCOMBO, INTER.SERVICE, INTER.CLASS, INTER.[MIN]*0.65 as MIN, INTER.[LTL]*0.65 as LTL, INTER.[500]*0.65 as 500, INTER.[1M]*0.65 as 1M, INTER.[2M]*0.65 as 2M, INTER.[5M]*0.65 as 5M, INTER.[10M]*0.65 as 10M, INTER.[20M]*0.65 as 20M INTO Get65 FROM [INTER] WHERE INTER.TCOMBO=" & Chr(34) & termcombo & Chr(34) & " AND INTER.SERVICE=" & Chr(34) & [Forms]![Point2Point]![servicecombo] & Chr(34) & " ORDER BY INTER.CLASS", dbFailOnError
   
    Exit Sub
    
EH:
  If Err.Number = 3265 Then Resume Next
  MsgBox "error " & Err.Number & ": " & Err.Description

Open in new window


The above code is being used to delete a table called "Get65"
LVL 13
Shanan212Asked:
Who is Participating?
 
DoveTailsConnect With a Mentor Commented:
Could you simply delete the table with:
DoCmd.DeleteObject acTable, "IN"
DoCmd.DeleteObject acTable, "OUT"

Or....using the table definition as in your example:
CurrentDb.TableDefs.Delete ("OUT")

Best of luck
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Try something like:

CurrentDb.Execute  "OUT", dbFailOnError
CurrentDb.Execute  "IN", dbFailOnError

CurrentDb.Execute "RESULT", dbFailOnError

CurrentDb.TableDefs.Delete ("In")
CurrentDb.TableDefs.Delete ("Out")

Open in new window

0
 
Shanan212Author Commented:
Appreciate your answer. But this opened up whole can of worms such as "RESULT"  already exist error.

Is there a way to overwrite on "Result" table?

Thanks!
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Try:

CurrentDb.TableDefs.Delete ("In")
CurrentDb.TableDefs.Delete ("Out")
CurrentDb.TableDefs.Delete ("Results")


CurrentDb.Execute  "OUT", dbFailOnError
CurrentDb.Execute  "IN", dbFailOnError

CurrentDb.Execute "RESULT", dbFailOnError

CurrentDb.TableDefs.Delete ("In")
CurrentDb.TableDefs.Delete ("Out")

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Or maybe this:

CurrentDb.TableDefs.Delete ("Results")


CurrentDb.Execute  "OUT", dbFailOnError
CurrentDb.Execute  "IN", dbFailOnError

CurrentDb.Execute "RESULT", dbFailOnError

CurrentDb.TableDefs.Delete ("In")
CurrentDb.TableDefs.Delete ("Out")

Open in new window



I would definitely add some error handling for the delete table code.
0
 
Shanan212Author Commented:
This worked. No error handling is required since the query before creates the table that is being deleted and another query is outputting values that is being output.

Then I am deleting the table that was output 1st.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.