Release Underlying Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form which, on opening creates a temp table. This table is then
attached to the form. If the user by mistake open the from it will create the
table. :) When trying to delete the table on closing of the form I get a
message that the table can't be delete because it is still in use.
I need to know how to release the table, so that it is no longer in use, to
delete the table on closing of the form.

The code I am using is very simple:
An SQL statement to create the table if it doesn't already exist.
Then using "me.recordsource = tablename" to attached the table to the form.
And then "DoCmd.DeleteObject acTable, "tablename" to a command button if
form was incorrectly opened.
Any help will be appreciated.
 
Dunmarie,

Just precede the table deletion with a:

Me.Recordsource = ""

which will release the table.

HTH,
Nikos
 
Mmmmm....I have tried that but it doesn't seem to work or my next command is
following to quickly, before the release is actually taking place.
 
Why are you creating a temp table. If this happens much you are going to blow
the database out & require much compacting. Additionally how does it handle
multiple users as you cannot muck with TableDefs while multiple users are
accessing the data?
Without knowing your reasoning I think a better choice may be to leave the
form unbound and to handle the data manually
Terry
 
Yes, you are right. I am creating the Temp table to do a stock take which can
take place over a couple of days. Once the stock take is finish, the main
database gets update and the temp table gets deleted. The info is kept for
the stock take period only. The temp table is created on the user interface
and not on the main database. It is not effecting any of the other users
using the database. Once the user quit the user interface it gets compacted
which takes a couple of seconds.
 
OK. Nick's response was partially correct. You will find with combo boxes,
lists and, I presume, forms that when you re-assign the Recordsource you need
to requery the object
Me.Requery
HTH
Terry
 
I tested this and it worked without requerying. I'm using A2K, by the way.

Nikos
 
Terry said:
OK. Nick's response was partially correct. You will find with combo
boxes, lists and, I presume, forms that when you re-assign the
Recordsource you need to requery the object
Me.Requery
Click to expand...

I haven't followed the whole discussion, but I can say that, in all
cases I'm aware of, changing an object's recordsource or rowsource
automatically forces a requery of the object, so you don't need to
explicitly requery.
 
Thanks guys, I got it to work. "blussing". A small spelling mistake earlier
in mu code caused a problem.
Thanks for all the help.
 
Back
Top