Table Deleting Through VBA

[MoonMullen]

I have written a subprocedure that formats a text file externally and then imports the text file into Access. I run a few queries against this "working table" and then copy the newly format data to another table for permenant storage. The code abends when I try to delete the "working table". Is it a good idea to keep the database clear of "working tables"? Does Access hold onto the "working table" internally, so that is why I can not delete it? If so, how do I solve this problem?

 

[MichaelRed]

Is it a good idea to keep the database clear of "working tables"?

Yes - but. If you are doing the same 'exercise' repetively, then actualy deleting the table and then re-creating it doesn't save anything, so it would be just as easy to delete all of the records in the table before appending from the external source. Would require minimal change to your current procedure(s).


Does Access hold onto the "working table" internally, so that is why I can not delete it?

I haven't actualy tried it with a table, but typically Ms. Access will not let you delete an instantiated (e.g. any object which is "Dimmed" and "Set" in code) object. If you are using a procedure to 'generate' the table, then it is instantiated and must be released before the deletion.


If so, how do I solve this problem?

If you are using a procedure to 'generate' the table, then it is instantiated and must be released before the deletion. The 'release' may be accomplished explicitly (Set rst = NOTHING), or implicitly by having the instantiation 'go out of scope'.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[THoey]

Just as a side note to MichaelReds great answer, remember to compact this database frequently. Ms Access doesn't really delete anything, it just hides it until you run a compact. So, if you don't compact it, your DB is gonna get huge... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.