Solved

Automatically delete a temporary table after usage

Posted on 2011-09-15
6
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

735 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