Solved

Automatically delete a temporary table after usage

Posted on 2011-09-15
6
264 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

896 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

14 Experts available now in Live!

Get 1:1 Help Now