Results 1 to 7 of 7
  1. #1
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114

    Function To Check If Table Exists

    We use the below function to check if a table exists. I was curious if we could modify it to check for a table or query, or if a secondary function would have to be created to check for a query
    Code:
    Public Function TableExists(strName As String) As Boolean
    
    
       On Error GoTo HandleErr
    
    
       Dim Db As DAO.Database, tDef As DAO.TableDef
       
       Set Db = CurrentDb
       
       TableExists = False
       
       For Each tDef In Db.TableDefs
          If tDef.Name = strName Then
             TableExists = True
             Exit For
          End If
       Next tDef
    
    
    ExitFunction:
       Db.Close
       Set Db = Nothing
       Exit Function
       
    HandleErr:
       TableExists = False
       Resume ExitFunction
       
    End Function
    And we call it like such


    Code:
       tableArray = Array("ESR", "NAC", "_LMNO")
       
       'Iterating the array to pass each one individually to the Delete function
       For amx = 0 To UBound(tableArray)
            If TableExists(amx) Then
                With Db.TableDefs
                    .Delete amx
                    .Refresh
                End With
            End If
       Next
    Could the same function be adapted to check for a query as well?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see why not. Have you tried using QueryDef(s) instead of TableDef(s)?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Played around with it, and the function will work when updated for queries.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why wouldnt a table exist?

  5. #5
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by pbaldy View Post
    I don't see why not. Have you tried using QueryDef(s) instead of TableDef(s)?
    AH - I was trying to use querydef gotta have the S

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by ranman256 View Post

    why wouldnt a table exist?
    Good question...if Tables are being dynamically created, in an ongoing process, as the OP's question kind of suggests (why else would you need to check whether a Table existed?) you'd have to wonder about the basic design of the database.

    A possible exception to this, of course, would be if there were temporary Tables you were talking about.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Replies: 1
    Last Post: 08-09-2015, 10:03 AM
  2. Replies: 9
    Last Post: 12-05-2014, 03:39 PM
  3. How to check if Table already exists
    By riaarora in forum Access
    Replies: 1
    Last Post: 08-12-2012, 09:48 AM
  4. Replies: 1
    Last Post: 03-06-2012, 06:45 PM
  5. Check to see if record id exists in joined table
    By csoseman in forum Programming
    Replies: 1
    Last Post: 08-18-2011, 01:06 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