Results 1 to 8 of 8
  1. #1
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209

    Delete Query Or Table If Exists

    I found this code over on utteraccess to delete a query if it exists. Is it possible to modify to also pass in a table name as a string and delete the table if it exists?



    So I would want to have the function check both queries and tables for the name being passed in. If it can't be done all in one function, can someone write a function that can delete a table if it exists?

    I'm tired of using on error resume next as that supresses other messages that I may need to capture

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    there shouldnt be a need to delete tables nor queries.
    you should use all tables there. And trying to determine if you need to delete a query is not worth the time since ignoring it wont cause a problem.

    you should empty tables then refill them with data. Not delete tables.

  3. #3
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209
    that is the scenario i am in. :/ Is there a way to do it?

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Just a programming note-
    If your sub or function has an error handler, say named Error_Handler
    Issuing another
    On Error GoTo Error_Handler
    Or simply
    On Error GoTo 0
    reactivates error checking if you've issued On Error Resume Next previously in the code.

  5. #5
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209
    is there a function that can delete a table if exists in vba?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Juan4412 View Post
    I found this code over on utteraccess to delete a query if it exists.
    I agree with others about not deleting tables/queries.... but maybe you would post the code you have?

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Juan, it can get complicated if you want to delete tables. If the target table is a "Parent" table with "Child" relationships established, Access will complain when you try to delete the table. If you get around that and delete the table AND don't delete the related records in the "Child" table, those records will be ORPHANS. They will have no parent. You don't want this to happen.There are better options.
    To give focused advice, we need more information on how the target table is created (by an import?), how it is used, how it's related to other tables and why you feel that you need to delete it. If you are going to just delete the table to get rid of its data and then re-create it with new data, there are better options.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is a function to check if an object exists:
    Code:
    Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
    ' Pass the Object type: Table, Query, Form, Report, Macro, or Module
    ' Pass the Object Name
        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
    To use it for your purpose:
    Code:
    If ObjectExists("Table","tblYourTableName") Then Docmd.DeletObject acTable, "tblYourTableName"
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Iterate Array And Delete If Exists
    By chalupabatman in forum Programming
    Replies: 7
    Last Post: 11-20-2017, 01:31 PM
  2. Delete from Query B where record exists in Query A
    By patchesohouli in forum Queries
    Replies: 3
    Last Post: 04-29-2016, 01:04 PM
  3. Replies: 2
    Last Post: 01-31-2016, 08:47 PM
  4. Replies: 2
    Last Post: 01-24-2012, 02:16 PM
  5. How to tell if a file exists and then delete it
    By orcinus in forum Programming
    Replies: 3
    Last Post: 05-17-2010, 05:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums