Solved

Automatically delete a temporary table after usage

Posted on 2011-09-15
6
265 Views
Last Modified: 2012-06-27
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"
0
Comment
Question by:Shanan212
  • 3
  • 2
6 Comments
 
LVL 21
ID: 36545692
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
 
LVL 13

Author Comment

by:Shanan212
ID: 36545931
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
 
LVL 5

Accepted Solution

by:
DoveTails earned 500 total points
ID: 36546426
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 21
ID: 36547213
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
 
LVL 21
ID: 36547218
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
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36549277
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

816 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now