unlocking a recordset

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

Guest

I'm using Access 2003. How do I unlock a table ?

I have in VBA the follwing code :

Sub MySub_Click()

db.Tabledefs.Delete ("mytable")

then I re-create the table :

set tbl = db.createTableDef("mytable")
With tbl
.Fields.append .createField ("Field1)
..
End With
db.TableDefs.append tbl

Then I fill up the database with the records :
set rst = db.Openrecordset("mytable")

rst.Addnew
rst("Fiedl1") = "field1"
....
rst.Updtate

EVerything is fine

On a form I have a button that trigger the above sub. I also have a combo
list that use the above table as Row source display the fields Field1
(unique) coming from the above table. I have the folowing problem :

1) IF I click on the button of the above sub, everythig is fine. The tbale
seems to be deleted fine. But If I open the combo list and select the Field1
(unique) , db.Tabledefs.Delete ("mytable") seems to not work because It just
append the rows. The "mytable" seems to be locked as soon as I just choose
one of its field from the combo list.
The tabel "mytable" is locked. How can i unlock it.

What I need to acheive is to be able to click on the above button to have a
fresh table (it is filled up with the content of many files), so it is easier
for me to just recreate the table everytime I click on the button.

So I to refresh the combo list and not have the tbale locked so I can delete
the table and have the combo list refreshed with new content with I expand
the list ?

Make sens ?
 
rst.close
set rst = nothing

Perhaps it's locked as the memory variable is retaining it.

Try to use an Append rather than delete / make table, as the overhead is
much lower.
 
thanks, but it does not help. the table is still locked, in fact it is not
related to my code I have done the following test :

Table1 with one Field
AAAAA
BBBBB
CCCC

Form1 with a combo list where the record source is field1 of table1. Open
the form but do not select any item from the combo list. Try to delete the
table1 it is ok. Now, select an item from the combo list and then try to
delette the table. Access returns a message that the engine icannot lock the
table as it is used by another process. any way to wrod this around ?


Also, if I have the form open and then I add a row in my table, how do I
refresh the list in the combo without closing the form ?
 
For first ?: What is the combobox's rowsource... Table or Query?

comboboxname.requery is the answer to your second question.
 
Back
Top