How Could I delete all tables by writing a Module?

[Andrew]

As above, How could I delete all tables by writing a module??
Also, How could I import all tabels from an ODBC Database??

 

[Parra]

Maybe I did not understand your question, but if you want to delete all the tables, why not just create a new database?

 

[Parra]

Do you want to just delete the data? But keep the table format?

 

[Andrew]

No, I need to delete all the tables in the database, then import all the tables (with same name).

 

[Ziggy]

Andrew can you clarify why you want to do this and what you are trying to achieve. It doesn't make any sense to delete the tables and then import them again.

If you are trying to update your data you can set up delete queries to delete the data in the tables and then use the transfer spreadsheet method to re import the data to the appropriate tables.


Ziggy

 

[mdmilner]

Andrew,

Instead of importing a new file or into an existing one, how about linking to the existing odbc database instead? This should give you dynamic data.

If you'd like to delete existing tables, for which you know the name, you can assign that name to a variable like strTblName and insert the below If statment into your code to first look for, then delete the table (or link)

The function below was something I believe I pulled off:
http://www.mvps.org/access/

Lot of good information and examples there.

If ObjectExists("Table", strTblName) Then
  DoCmd.DeleteObject acTable, strTblName
End If


Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
     
     Set db = CurrentDb()
     ObjectExists = False
     
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If
     
End Function