Force the Drop of a Table

[gray]

Hi All

WinXPPro
Access 2002 SP3

Here's a circle I've been going around for several hours now.

In the Open event of a Form i re-create some temporary tables. These temporary tables are intended to be unique to the individual user. I re-create these from a set of "permanent" tables in order to pick up any design/field changes etc in the permanent set. I also do this so that if the user's system has crashed I can "re-set" form bindings and so on.

In the form's Open event, I first Drop the old temporary tables before re-creating them. Unfortunately, if, indeed, the system has crashed out... my Drop tables fail with the jolly old Error 3211 "The database engine could not lock table xxxx because it is in use by another person or process" ... of course there are no other users and even re-starting Access does not clear the problem.

I believe this is because a sub-form may still have a binding reference to the temporary table... hahaa I hear you say, remove the binding reference(s) before re-creating the tables... but not so fast, my Access chums..... at this point it's not possible to do that because the sub-forms have not yet been initialised! Grrrr....

I looked at SQL Drop Table in the hope there might be a way to force the table delete despite the locks.... but if there is it's a well kept secret ... probably only known to the SQL Inner Sanctum!

Is there any way to delete the locks programatically or get around this problem in some other way?

Thanks so much for helping me keep my stress levels down! Look at it this way, you are helping the (UK) National Health Service save a fortune!

 

[datAdrenaline]

First and foremost ... is your application Split ... meaning do are your data tables in one db (called a Back End) and your application (Forms, Queries, code, LINKED tables, etc) in another db (The Front End)? ... If not your application SHOULD be split. Also, EACH user should have his/her own copy of the Front End.

Ultimately, there is generally NEVER a need to DROP "temp tables" that you recreate. Because if you re-create them, then they are not temporary. The information in them may be considered temporary and if that is the case, then I would suggest that you DELETE the information (records) in the tables, then use an APPEND query to add a fresh set of records into them. Your db will be MUCH happier and stable by using this technique over the DROP TABLE / MAKE TABLE scenario ... but again ... your app needs to be split with EACH user having their OWN copy of the Front End.

 

[datAdrenaline]

>> do that because the sub-forms have not yet been initialised! Grrrr.... <<

One more point to note ... a SUB-FORM will completely load PRIOR TO a Main form completing its Load ... so ... if you are manipulating the sub-forms recordsources (ie: dropping the table they are bound to, you will NOT be able to do so because the Sub-Form is loaded and bound BEFORE your Main form has a chance to drop the table. This can be seen simply by putting simple msgbox code in the sub-forms Load event and the Main forms Load event, you will see the message box of the OnLoad PRIOR TO the msgbox of the OnLoad for the main form....

So ... BEFORE your open your Main form is when you should be Dropping the table ... but alas, you have probably read my first reply and will re-iterate that it makes for a much more stable application if you refresh the data and do not drop the tables.

 

[gray]

Hi datAdrenaline

Thanks very much for your time on this. I intend to split the database as you suggest and have already experimented with it on an old copy thanks.

Whilst trying to solve my problem I did a few run-throughs with msgboxes in the Load and Open events of my various forms. However, I noticed that the sub-forms don't load before the main form where the sub-forms' tables are empty of records. I think this is why I can't manipulate the recordsources in the Main form loads.

I'd also dabbled a little with emptying and appending records to my temp tables (as you say these are not temp tables in the strict sense I just hijacked the term for mine) but I don't think new fields in the permanent tables are forwarded to the temp tables unless using a SELECT INTO. This method appears to delete and re-create the tables but because they are locked, I was back to square one . My purpose here, incidentally, was trying to save development time and ommisons as I developed the permanent table schemas.

You have given me a thought though, and that is to manipulate the recordsources etc in the Load/Open events of the subforms themselves rather than in the Main form events.

Thanks again.

 

[datAdrenaline]

Hello Gary ...

Your statement:
>> However, I noticed that the sub-forms don't load before the main form where the sub-forms' tables are empty of records.<<

Intrigued me simply because the event sequence does indeed begin with a sub-form prior to the Main form. To verify this, created a "Standard Config" form/sub-form arangement ... meaning NO CODE, the main form has a Table name as the recordsource, the sub-form has a Table name as a recordsource and the Link Child/Master properties and set accordingly. Here is the sequence of events:

xfrmSubForm : Open
xfrmSubForm : Load
xfrmSubForm : Current
xfrmMain : Open
xfrmMain : Load
xfrmMain : Current

... Now ... when I REMOVE the sources ... so BOTH forms are UNBOUND, I get the same sequence of events.

xfrmSubForm : Open
xfrmSubForm : Load
xfrmSubForm : Current
xfrmMain : Open
xfrmMain : Load
xfrmMain : Current

Next ... I when I set the sub-form to have an empty set of records (SELECT * FROM mytable WHERE 1=0) ... Same Order ... just about every thing I did to try to get a different order of events yeilds ... yep .... the same order ... HOWEVER ... I decided to change the Recordset Type property of the sub form to Snapshot, thus causing the sub-form to have an Empty recordset AND additions were NOT allowed ... VIOLA here is the order of events I witnessed:

xfrmSubForm : Open
xfrmMain : Open
xfrmMain : Load
xfrmMain : Current
xfrmSubForm : Load

Notice that the Open event of the sub-form STILL occurs prior to the Open event of the Main, however, the sub-forms LOAD event does not fire until all the Main form events fire... PLUS ... NO OnCurrent Event fired for the sub-form, which is interesting since an UNBOUND sub-form fired off an OnCurrent event! ...

So .... I am guessing that your sub-form is either set to (AllowAdditions = No) or the Recordset Type is "Snapshot" ... or some other situation exists that has your sub-form bound to an empty recordset that can not have records added to it.

....

Ok ... with all this ... where does that leave us. In this scenario, I would still highly recommend you DO NOT DROP the tables, but merely refresh the data in them ... better yet, why not just use queries, and keep the data in the source tables? ...

....

Also ... you indicated:
>> You have given me a thought though, and that is to manipulate the recordsources etc in the Load/Open events of the subforms themselves rather than in the Main form events. <<

I personally manipulate the recordsources of sub-forms via the Main simply because the recordsources of the sub are often derived from info in the main. My sub-forms are often codeless so I have never been effected by the phenomenon we have just seen with respect to event firing order.

I use passthrough queries to a SQL Server back end and thus I am unable to use the Link Master/Child properties in many of my form/subform scenarios. In those cases, I set up my subform recordsource to return an empty set ...

SELECT * FROM <my_pt_query_name> WHERE 1=0

Then in my MAIN forms OnCurrent event (or OnLoad, depending on the number of records in the Main form) I have something like this:

Private Sub Form_Load()
    Dim strSQL As String
    strSQL = "SELECT * FROM dbo.MySQLServerTable WHERE SomeID = " & Me.SomeID
    CurrentDb.QueryDefs("<my_pt_query_name>").SQL = strSQL
    Me.<sub_form_control_name>.Form.Recordsource = "<my_pt_query_name>"
End Sub

...

Hope this bit if info helps you on your project!! ...

 

[gray]

Hi datAdrenaline

Thanks for the very thorough update, you obviously spent quite some time looking at my problem; believe me, we newbies really do appreciate it. I had no idea just how big Access was until I started my project!

I think you may have hit the nail square on the head with this because I do, indeed, bring up my forms with AllowAdditions and Edits set to NO. Curiously, the results of my tests where slightly different to yours, however, whereby the Open event for subforms without records did not fire until I added a new record in my Main form. During the course of the creation of a new Main form record, a subform record is automatically created and from that point onwards the subforms' Open event is fired up first. My Recordset Type and Locks are Dynaset and No Locks respectively. Yesterday, I moved my table creations to the subform open events which has cured my particular problem. For the reference of other new starters I found a very handy tip from Bob Larson at http://www.access-programmers.co.uk/forums/showthread.php?t=99837 . This method will create a copy of a table including field properties which the "SELECT * INTO" does not.

Incidentally, I arrived here (i.e. manipulating Recordsources and Rowsources all over the place) as I wanted Users to be able to perform Cancels and Undos as they would be able to on other typical Windows applications. I spent weeks trying code with Access built-in cancel/undo features, cancelling updates in BeforeUpdate events and then moved on to Disconnected Recordsets. I was never able to develop a reliable setup. I know of course that this was probably my inexperience! I'd also read horrors about record-locking in multi-user environments. In the end, I elected to use a read-only and edit mode concept whereby forms are bound to permanent tables whilst in read-mode. When a record requires editing, I write its details to a temporary table and adjust the form Recordsource to the temporary table... when the Save button is pressed the new details get written back to the real tables..if the cancel button is pressed the new details are not written back. I've checks in there to deal with conflicts should two users edit a record at the "same" time. It's taken an awful lot of time to do but it does seem reliable... My temporary tables, therefore, are temporary more in the English sense than the SQL sense.

I mention this here but don't want to mislead other newbies if you, more experienced, developers disapprove so I'd be interested to know your thoughts on my construct?

Thanks again