Does a table exist (Access 2K)

Topic

New Reply

Isn’t there a function that checks to see if a table exists? I’ve been searching to no avail so if anyone can help I’m in your debt.

Thx,
Kathi

Reply | Quote

Replies

Here you have a function to check if a table exist.

Function TableExist(TableName As String) As Boolean
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Name = TableName Then
   TableExist = True
   GoTo TableExistExit
End If
Next tdf
TableExist = False
TableExistExit:
Set tdf = Nothing
Set db = Nothing
End Function

Copy the code in a module. Save the module with any name but not TableExist (This is the function name and can’t be also the module name)
You can call the the function with :
TableExist(“YourTableName”)
It will return true if exist in the database, false if not.
Don’t forget to set a refence to Microsoft DAO 3.6 Object Libray as this is DAO code.
HTH

Reply | Quote

Here you have a function to check if a table exist.

Function TableExist(TableName As String) As Boolean
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Name = TableName Then
   TableExist = True
   GoTo TableExistExit
End If
Next tdf
TableExist = False
TableExistExit:
Set tdf = Nothing
Set db = Nothing
End Function

Copy the code in a module. Save the module with any name but not TableExist (This is the function name and can’t be also the module name)
You can call the the function with :
TableExist(“YourTableName”)
It will return true if exist in the database, false if not.
Don’t forget to set a refence to Microsoft DAO 3.6 Object Libray as this is DAO code.
HTH

Reply | Quote

Here’s another simple approach that works in Access 2000 and above:

Public Function TableExists(strTable As String) As Boolean
    Dim strName As String
     
    On Error Resume Next
    strName = CurrentData.AllTables(strTable).name
    TableExists = (strName  "")
     
End Function

Reply | Quote

Here’s another simple approach that works in Access 2000 and above:

Public Function TableExists(strTable As String) As Boolean
    Dim strName As String
     
    On Error Resume Next
    strName = CurrentData.AllTables(strTable).name
    TableExists = (strName  "")
     
End Function

Reply | Quote

Here are two more from

Code sample from Accessory http://www22.brinkster.com/accessory%5B/url%5D

'2 ways of checking if a table exists
'97 / 2000 / 2002
'There are two ways to check whether a table exists in your database.
'The first, and easiest, is to make use of the MSysObjects system table.
'We'll make use of three fields, ID (the primary key - for speed, we'll count this
'rather than anything else), Name (the name of the object, in this case our table)
'and Type (tables have a type of 1). This function will return True if the specified
'table exists, False otherwise.
    
' Print TableExistsDC("Customers")
' returns True

Function TableExistsDC(strTable As String) As Boolean
    
    ' Code sample from Accessory http://www22.brinkster.com/accessory
    TableExistsDC = (DCount("ID", "MSysObjects", "Name='" & strTable & "' AND Type=1") > 0)
    
End Function
    
'This is fine, but the DCount() function can be a bit slow. A slightly more long-winded
'solution is to enumerate through all tables in the database, stopping if one is found
'that matches the specified table. This is also slow the first time you run it, but is
'faster thereafter. Again, this function will return True if the specified table exists,
'False otherwise.
    
' Print TableExists("Customers")
' returns True
    
Function TableExists(strTable As String) As Boolean
    
    ' Code sample from Accessory http://www22.brinkster.com/accessory
    Dim db As Database
    Dim i As Integer
    Set db = DBEngine.Workspaces(0).Databases(0)
    TableExists = False
    db.TableDefs.Refresh
    For i = 0 To db.TableDefs.Count - 1
        If strTable = db.TableDefs(i).Name Then 'Table exists
            TableExists = True
            Exit For
        End If
    Next i
    Set db = Nothing
End Function

Reply | Quote

Here are two more from

Code sample from Accessory http://www22.brinkster.com/accessory%5B/url%5D

'2 ways of checking if a table exists
'97 / 2000 / 2002
'There are two ways to check whether a table exists in your database.
'The first, and easiest, is to make use of the MSysObjects system table.
'We'll make use of three fields, ID (the primary key - for speed, we'll count this
'rather than anything else), Name (the name of the object, in this case our table)
'and Type (tables have a type of 1). This function will return True if the specified
'table exists, False otherwise.
    
' Print TableExistsDC("Customers")
' returns True

Function TableExistsDC(strTable As String) As Boolean
    
    ' Code sample from Accessory http://www22.brinkster.com/accessory
    TableExistsDC = (DCount("ID", "MSysObjects", "Name='" & strTable & "' AND Type=1") > 0)
    
End Function
    
'This is fine, but the DCount() function can be a bit slow. A slightly more long-winded
'solution is to enumerate through all tables in the database, stopping if one is found
'that matches the specified table. This is also slow the first time you run it, but is
'faster thereafter. Again, this function will return True if the specified table exists,
'False otherwise.
    
' Print TableExists("Customers")
' returns True
    
Function TableExists(strTable As String) As Boolean
    
    ' Code sample from Accessory http://www22.brinkster.com/accessory
    Dim db As Database
    Dim i As Integer
    Set db = DBEngine.Workspaces(0).Databases(0)
    TableExists = False
    db.TableDefs.Refresh
    For i = 0 To db.TableDefs.Count - 1
        If strTable = db.TableDefs(i).Name Then 'Table exists
            TableExists = True
            Exit For
        End If
    Next i
    Set db = Nothing
End Function

Reply | Quote