check table exists

[The_Vicar75]

Hey,

I know there is a function to, in VBA, check a table exists yes or no. But I don't remember... Can anyone help please?

Thx!

V.

 

[chergh]

Use this:

Public Function TableExists(TableName As String) As Boolean


Dim tdf As TableDef
Dim db As Database

On Error GoTo errorhandler
Set db = CurrentDb
On Error Resume Next

Set tdf = db.TableDefs(TableName)
TableExists = Err.Number = 0
db.Close

Exit Function

errorhandler:

Err.Raise Err.Number
Exit Function

End Function

 

[The_Vicar75]

Thanks a lot!

V.

 

[dallr]

There are many different ways to do this here is another one

Public Function TblExists(strTableName As String) As Boolean

'ADO Method

Dim obj As AccessObject
Dim dbs As Object

Set dbs = Application.CurrentData
TblExists=False
For Each obj In dbs.AllTables
    If obj.Name = strTableName Then
        TblExists = True
        Exit For
    End If
Next obj

End Function

Dallr

 

[ecawilkinson]

Try:

function TableExists(sTable as string) as boolean
    dim tdf as tabledef
    
    On Error Resume Next
  
    set tdf = Currentdb.Tabledefs(sTable)

    if Err.Number = 0 Then
        TableExists = True
    else
        TableExists = False
    end if
End function

or:

Function TableExists(sTable as string) as booleans
    dim iResult as integer

    If Dcount("*","msysobjects","Type = 1 AND name='" & sTable & "'") = 0 Then
        TableExists = False
    Else
        TableExists = True
    End if
End Function

HTH,
Chris

 

[gemma-the-husky]

but all the methods are based on the same premise

the tabledefs collections holds details of all the tables

so you try to find a tabledef with the correct name

if you get an error, the table does NOT exist
if you dont get an error, the table exists

 

[BobMcClellan]

Chris, I know you posted this 6+ years ago. I had to jump into some old applications ( still running in 97 believe it or not ). I don't too much with vba anymore and could not remember how to check for a table. It was great to google for it and hit this piece posted way back then. Just felt compelled to say thanks..
so ... Thanks !
..bob

Try:

Function TableExists(sTable as string) as booleans
    dim iResult as integer

    If Dcount("*","msysobjects","Type = 1 AND name='" & sTable & "'") = 0 Then
        TableExists = False
    Else
        TableExists = True
    End if
End Function

HTH,
Chris

 

[Uncle Gizmo]

Updating one of the code examples because it had a typo:- "Booleans" and a redundant variable declaration. This is the new code, note the slight modification to get rid of some annoying quotation marks!

Function fTableExists(ByVal sTable As String) As Boolean

'I prefer to add the delimiters (quotation marks) in to the variable to avoid difficult to read code.
'Code with lots of confusing quotation marks in it!
'Add an Extra line - Like This:-
sTable = Chr(34) & sTable & Chr(34)     'Chr(34) = "
    
    'Original
    'If DCount("*", "msysobjects", "Type = 1 AND name='" & sTable & "'") = 0 Then
    
    'Simplified
    If DCount("*", "msysobjects", "Type = 1 AND name=" & sTable) = 0 Then
        fTableExists = False
    Else
        fTableExists = True
    End If
End Function      'fTableExists