Error deleting tables 2

[purpledawn]

Hi everyone.

I want to delete all tables from the application I'm creating every time the user runs a particular form. I'm using the code:

For Each tbl In dbs.TableDefs
DoCmd.DeleteObject acTable, tbl.Name
Next

But this give me an error (Run time error 3211) that basically says that the program can lock a System table (MSysAccessObjects) because it's being used by another person or process.

This error occurs regardless of whether there are any tables.

Does anyone know what the problem is?

 

[Aivars]

Hi!
The system tables are Access own tables for saving information what is needad for Access work. These table names begin &quot;MSys&quot;. You can see these tables if you'll put <Menu Bar>;<Tools>;<View>;&quot;System objects&quot; True.

For Each tbl In dbs.TableDefs
if left(tbl.name,4)<>&quot;MSys&quot; then
DoCmd.DeleteObject acTable, tbl.Name
end if
Next

Aivars

 

[rgbanse]

Purpledawn,
My personal preference is to not delete tables but to empty them. I find it easier to follow code and queries if the worktables are intact. Also you may need to trim you list of tables to delete due to some of them being linked tables, or they may be &quot;Reference Tables&quot; ie:
XRef Tables that store a master list of items to process,
of &quot;if this = this , substitute this thing&quot; type stuff.
With that said here is what I did:

Both functions are run with a Call
Hope they work
RGB

'The first function will check for any &quot;Valid&quot; tables to empty. It is intended
'to catch all temp files, but it's a good idea to check the master table before
'running the Empty Function. After the &quot;Valid&quot; tables are selected you will be
'prompted to edit the table before emptying begins


Public Function GetTablesToDelete()
Dim ThisDatabase As Database
Set ThisDatabase = CurrentDb

DoCmd.SetWarnings False
'The following SQL Statement reads MSysObjects and identifies tables in the
'database that may be candidates for emptying. Candidates are generally local
'tables that are not being used as Access reference table and tables that are not linked.
DoCmd.RunSQL (&quot;SELECT MSysObjects.Name AS table_name INTO XXX_wrkfl_0000_tables_to_empty FROM MSysObjects WHERE (((MSysObjects.Name) Not Like 'XXX*') AND ((MSysObjects.Flags)=0) AND ((MSysObjects.Type)=1)); &quot

'The following Message Box just states that there may be files that shouldn't be emptied
'then opens the &quot;Master&quot; table for verification.
MsgBox &quot;After the table opens, check for Tables (Reference Tables)&quot; & Chr(13) & &quot;that you do not want emptied. To delete the &quot;&quot;Table&quot;&quot;(record), &quot; & Chr(13) & &quot;just select it in the table and delete it!&quot;
'Opens the &quot;Master&quot; table for viewing
DoCmd.OpenTable &quot;XXX_wrkfl_0000_tables_to_empty&quot;
DoCmd.SetWarnings True
End Function



Public Function EmptyTables()
Dim ThisDatabase As Database
Set ThisDatabase = CurrentDb
Dim TablesToEmpty As Recordset
Set TablesToEmpty = ThisDatabase.OpenRecordset(&quot;XXX_wrkfl_0000_tables_to_empty&quot
Dim TableName As String
Dim NumberOfTablesToDelete As Double
DoCmd.SetWarnings False

'This section cycles through the &quot;Master&quot; table and empties all tables listed
'Please note: some tables may cause an error because the name doesn't conform
'with an SQL Statement

'The following two lines are used if you want to have a &quot;Last Chance&quot; at
'changing your mind. They just count the number of tables you are going
'to delete and asks if you want to continue. &quot;Un Rem&quot; to make active.

'NumberOfTablesToDelete = DCount(&quot;*&quot;, &quot;XXX_wrkfl_0000_tables_to_empty&quot
'If MsgBox(&quot;You are about to empty &quot; & NumberOfTablesToDelete & &quot; Table(s).&quot; & Chr(13) & &quot;Do you wish to continue?&quot;, vbYesNo, &quot;The Table Killer !!&quot = vbNo Then Exit Function

TablesToEmpty.MoveFirst
Do
TableName = TablesToEmpty!table_name.Value
DoCmd.RunSQL (&quot;DELETE &quot; & TableName & &quot;.* FROM &quot; & TableName & &quot;; &quot
TablesToEmpty.MoveNext
Loop Until TablesToEmpty.EOF

DoCmd.SetWarnings True
End Function

 

[purpledawn]

Thank-you both!

I get a type mismatch error in the line

Set TablesToEmpty = ThisDatabase.OpenRecordset
&quot;XXX_wrkfl_0000_tables_to_empty&quot

I'm using the ADO 3.6 Reference. Is there another particular reference that should be open to use OpenRecordset method

PD

 

[Aivars]

Look for priority of reference to ADO. Change it into position next default (disabled) position. Maybe this will help you.
Aivars

 

[purpledawn]

Thanks Aivars! That did the trick.

PD