Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Does a table exist (Access 2K)

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does a table exist (Access 2K)

    Here you have a function to check if a table exist.<pre>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</pre>

    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
    Francois

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Does a table exist (Access 2K)

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

    <pre>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</pre>

    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does a table exist (Access 2K)

    Here are two more from

    Code sample from Accessory http://www22.brinkster.com/accessory


    <pre>'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
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •