?
Solved

Automatically delete a temporary table after usage

Posted on 2011-09-15
6
Medium Priority
?
269 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 2000 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 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