HOWTO: Determine If a Table or Query Exists

Last reviewed: August 11, 1997
Article ID: Q129927
The information in this article applies to:
  • Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 5.0
  • Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit and 32-bit, for Windows, version 4.0

SUMMARY

This article shows, by example, how to determine if a given table or query exists in a database opened by Visual Basic.

MORE INFORMATION

Step-by-Step Example

  1. Start a new project in Visual Basic. Form1 is created by default.

  2. Add a module (Module1) to your Project.

  3. If using VB 5.0, establish a reference to Microsoft DAO 3.x Object Library using the References command from the Project menu.

  4. Add the following line to the General Declarations section of Module1:

          Public Const NameNotInCollection = 3265
    

  5. Add the following code to the General Declarations section of Form1:

          Dim DB As Database
    

          Private Function ExistsTableQuery(TName As String) As Boolean
    
             Dim Test As String
             On Error Resume Next
       
             ' See if the name is in the Tables collection:
             Test = db.TableDefs(TName).Name
             If Err <> NameNotInCollection Then
                ExistsTableQuery = True
                ' Reset the error variable:
                Err = 0
                ' See if the name is in the Queries collection:
                Test = db.QueryDefs(TName$).Name
                If Err <> NameNotInCollection Then
                   ExistsTableQuery = True
                End If
             End If
          End Function
    
    

  6. Add the following code to the Form1_Load procedure:

          Private Sub Form_Load ()
          Set DB = DBEngine.Workspaces(0).Opendatabase("Biblio.mdb")
          Debug.Print "BadTable " ;IIF (ExistsTableQuery("BadTableName"), _
    
             "does", "doesn't"); " exist."
          Debug.Print "Authors " ;IIF (ExistsTableQuery("Authors"), _
             "does", "doesn't"); " exist."
          End Sub
    
    

  7. Start the program by choosing Start from the Run menu or by pressing the F5 key. The Immediate window correctly prints:

          BadTable doesn't exist.
          Authors does exist.
    
Keywords          : APrgData VB4ALL VB4WIN vb5all vb5howto VBKBDB VBKBJet kbcode kbhowto
Version           : 4.0 5.0
Platform          : NT WINDOWS


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 11, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.