Does Table Exist,small Ms Access Gurus
Home Code

VBA > DAO > Does Table Exist and Does Query Exist

DoesTableExist returns True or False if table exists, or does not, in the current database, or other database.

DoesQueryExist returns True or False if query exists, or does not, in the current database, or other database.

Screen shot

Does Table Exist

Examples

Logic

Dimension variables, skip errors, and initialize sName to be a zero-length string.

If a database object was not passed, look in the AllTables collection of current database for the table name.

If a database object was passed, use the DAO TableDefs collection to see if the tablename exists.

If pBooCheckQueriesToo is true, and the name couldn't be found in tables, then check queries too.

When done, if sName is not still a zero-length string, then the name was found.

DoesQueryExist is similar to DoesTableExist except that it checks the AllQueries collection, or the QueryDefs collection.

DoesTableExist_OtherDatabase sets a DAO database object and calls DoesTableExist.

Just because a name is found, it may not necessarily open. Perhaps the table is linked to a back-end that isn't valid. I will be posting code for CanTableOpen to handles these cases.

Parameters

Optional

Code

' Module Name: mod_DoesTableExist
'*************** Code Start *****************************************************
' Purpose  : see if a table (or query) exists in current or other database
' Author   : crystal (strive4peace)
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'--------------------------------------------------------------------------------

'                              DoesTableExist
'--------------------------------------------------------------------------------'
Public Function DoesTableExist( _
   psTablename As String _
   , Optional pDB As DAO.Database _
   , Optional pBooCheckQueriesToo As Boolean = False _
   ) As Boolean
'...190105 strive4peace, 190127
   
   ' dimension variables
   Dim sName As String _
      , boo As Boolean 
   
   'don't stop code if an error happens
   On Error Resume Next
   
   'initialize variable
   sName = ""
   
   'see if variable can be assigned to tablename
   If pDB Is Nothing Then
      'look in AllTables collection of current database
      sName = CurrentData.AllTables(psTablename).Name
   Else
      'Use DAO if database was passed
      sName = pDB.TableDefs(psTablename).Name
   End If
   
   'if name was assigned, then table exists
   boo = (sName <> "")
   
   'if table was not found, check queries if pBooCheckQueriesToo is True
   If boo <> True And pBooCheckQueriesToo = True Then
      boo = DoesQueryExist(psTablename, pDB)
   End If
   
   DoesTableExist = boo

End Function
'--------------------------------------------------------------------------------

'                              DoesQueryExist
'--------------------------------------------------------------------------------'
Public Function DoesQueryExist( _
   psQueryname As String _
   , Optional pDB As DAO.Database _
   ) As Boolean
'190127 strive4peace, 190127
   
   ' dimension variables
   Dim sName As String _
      , boo As Boolean 
   
   'don't stop code if an error happens
   On Error Resume Next
   
   'initialize variable
   sName = ""
   
   'see if variable can be assigned to query name
   If pDB Is Nothing Then
      'look in AllQueries collection of current database
      sName = CurrentData.AllQueries(psQueryname).Name
   Else
      'Use DAO if database was passed
      sName = pDB.QueryDefs(psQueryname).Name
   End If
   
   'if name was assigned, then query exists
   boo = (sName <> "")
   
   DoesQueryExist = boo

End Function
'--------------------------------------------------------------------------------

'                              DoesTableExist_OtherDatabase
'--------------------------------------------------------------------------------'
Sub DoesTableExist_OtherDatabase()
   Dim db As DAO.Database
   Set db = OpenDatabase("C:\path\MyDatabase.accdb")
   'see if this exists as a table
   MsgBox DoesTableExist("MyTable", db), , "MyTable exist?"
   'see if this exists as a table OR a query
   MsgBox DoesTableExist("qMyQuery", db, True), , "qMyQuery exist?"
   db.Close
   Set db = Nothing
End Sub
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

Goto Top  

Back Story

This code is setting the stage for more code to create tables. Instead of expanding code to play music, I am writing an application to help manage music files.

References

Docs / Office VBA Reference / Access / Object model / Application object / Properties / CurrentData

Help: Application.CurrentData property (Access)

Docs / Office VBA Reference / Access / Object model / CurrentData object

Help: CurrentData object (Access)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / Database / Properties / TableDefs property

Help: Database.TableDefs property (DAO)

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / Database / Properties / QueryDefs property

Help: Database.QueryDefs property (DAO)

Goto Top  

Share with others

here's the link to copy:

https://MsAccessGurus.com/VBA/Code/DAO_DoesTableExist.htm

Do you have something to say or share?

It is interesting to hear from you. Was something not clear? Did you find a bug? Is an explanation wrong or not sufficient? Do you want the code do more (there is always more)?

Some of you write to say thanks and tell me what you're doing with Access ... its nice to get an echo back. I want you and others to be good with Access, and other Office applications like Excel, Word, and PowerPoint ... and Windows. Take advantage of the strengths in each to manage your information wisely.

Are you a developer? Do you want to share? Email to ask about getting your pages added to the code index.

When we communicate, collaborate, and appreciate, we all get better. Thank you. Email me at info@msAccessGurus.com

Goto Top