Dim TDF as TableDef - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Dim TDF as TableDef

Juan C Rivera

3 years ago

Hi Im trying to return a true or false if a table exists here is my code

Public Function TableExists(tblName As String) As Boolean
'Returns true if table exists in current database else returns false
    Dim tdf As tabledef
    For Each tdf In CurrentDb.TableDefs
        If StrComp(tblName, tdf.Name) = 0 Then
            TableExists = True
            Exit For
        End If
      Next tdf
End Function

only when i try to type dim as tabl i get no intelisense (I think thats how its spelled if not very good spanglish)

Is ther a tool ref I need to add? I also looked at developer 33-35 thinking this was covered but did not see it. Did Richard cover this in any lesson?

	Juan C Rivera OP @Reply 3 years ago

	Alex Hedley @Reply 3 years ago
`DAO` or `ADO`?

	Kevin Robertson @Reply 3 years ago
Looks like you could be missing the following reference: Microsoft Office 16.0 Access Database Engine Object Library

	Juan C Rivera OP @Reply 3 years ago
Thanks guys, addred the lib and looks like it will work V/r Juan

	Juan C Rivera OP @Reply 3 years ago
Alex sorry I did not answer had to think about ADO and DAO. I guess my knowledge of access is still very limited I like to know if there is a book out there that tell which Lib should be used? But after reading I an not sue of ADO or DAO... Maybe when I get better. V/r Juan

	Kevin Robertson @Reply 3 years ago
ADO vs DAO

Kevin Yip @Reply

3 years ago

One way to do it without adding the library reference is to create a dummy query with the table name and check for a runtime error:

Function IsTable(ByVal tablename As String) As Boolean
    Dim r As Recordset
    On Error GoTo 10
    Set r = CurrentDb.OpenRecordset("SELECT DISTINCT 1 AS dummyfield FROM [" & tablename & "];")
    r.MoveFirst
    IsTable = True
    Exit Function
10:
    IsTable = False
End Function

If the table doesn't exist, OpenRecordset will cause an error.  This can be used to check for existing query names too.

"DISTINCT" is used above to ensure the query will return only one row.

With this method, you also don't need to loop through all the table names, which could cause (slight) delays in code execution if there are many tables.

	Juan C Rivera OP @Reply 3 years ago
Thanks Kevin

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View