Deleting tables based on MSysObjects (1 Viewer)

I have about 59 tables that all start with "TOA_". I want to delete them in one fell swoop. But it's not working... When I run this code (below) nothing happens.

The strSQL (when pulled from the watch window and run in a query object) shows 59 tables with names starting with "TOA_"... yet when the cofde is being run, the recordset is showing no records...

So what's wrong with this code? Is this due to some issue with using MSysObjects? Or is my code wrong?

Public Sub DELETE_TOA_TABLES()
Dim strSQL As String
Dim strTable As String

Set cn = New ADODB.Connection
Set Db = CurrentDb
Set rs = New ADODB.Recordset

strSQL = "SELECT Name FROM MSysObjects WHERE MSysObjects.Name " & _
"Like " & Chr(34) & "TOA_*" & Chr(34) & " ;"



With rs
.activeconnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open (strSQL)
End With

If rs.RecordCount > 0 Then
rs.MoveFirst

Do Until rs.EOF
strTable = rs!Name
DoCmd.RunSQL "DROP TABLE " & strTable
rs.MoveNext
Loop

End If

End Sub

Click to expand...

This routine uses the CurrentDb.TableDefs collection instead of having to delve into msysObjects.


This could be a potentially disasterous bit of code so test it on a copy of your database first to make sure it does what you want.



Don't forget to MAKE A COPY to try it on first.

Thanks,

That works great, (fyi: it's missing the end if, if you wanna edit...)

Thanks again!

-Gary

PS. Still am curious as to why my code didn't work... if anyone knows...

I'm glad it worked.



It's actually a single line IF construct.


Instead of a multiple line one


If...Then...Else Statement (Visual Basic)

out of interest, I have never used the msys tables directly

I would do this as nanscombe suggested, by using the tabledefs collection.

The thing is, it depends whether using the msys tables is documented and supported or not. (by MS). If it is, then fine - if not, and it is just that various users have "worked out" what data the msys tables inlcude - then MS could change the way the msys tables are structured in a future version of access, and any code using msys tables directly would no longer work. Whereas, I am pretty sure that code using the tabledefs collection would continue to work.

Years ago (that sounds bad, 2000 - 2006) I used to use the mSysObjects table because I was creating a system where I could export all of the objects based on a certain prefix, ie REP01: REP01 (Report - based on REP01_Output), REP01_Params (Form), REP01_Output (query), REP01_Data01 (query) etc.

I was creating an adHoc reporting system (several of them actually) in Access 97 where I could:

Create a new report
Export all of the related objects to a separate Access Db
E-mail the Db to the client
The client could then Import the objects into their copy of the reporter and use the report.

Using mSysObjects wasn't considered good practice but it worked fine for what I needed, especially since not all of the current Object Collections were available.

Just an fyi to everyone. When deleting multiple tables you need to iterate through the collection BACKWARDS because if you do it forwards, each delete reorders the collection and you will miss some of the tables.

So,