Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: Test for table exists

    Hello, How do I test to see if a table exists? I want to check to see if the table exists before I delete it and run the make table query.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I dunno if there's a better way to do this, but this is what I came up with:
    Code:
    Public Function TableExists(tbl As String) As Boolean
    Dim obj As AccessObject, db As Object
    
    Set db = Application.CurrentData
    TableExists = False
    
    For Each obj In db.AllTables
        If obj.name = tbl Then
            TableExists = True
        End If
    Next obj
    
    End Function
    Usage:

    TableExists (TableNameAsString)

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    PHP Code:
    Function ExistTable(TableName As String) As Boolean
    Dim db 
    As Database
    Dim tbl 
    As Recordset

    On Error 
    GoTo ERR_ExistTable

        Set db 
    CurrentDb
        Set tbl 
    db.OpenRecordset(TableName)
        
    ExistTable True
        Set tbl 
    Nothing
        
    EXIT_ExistTable
    :
      Exit Function

    ERR_ExistTable:
        
    ExistTable False
        Set tbl 
    Nothing
        Resume EXIT_ExistTable
    End 
    Function 
    You can call this function to check, whether the passed Table name exists or not

  4. #4
    Join Date
    May 2004
    Location
    Moscow, Russia
    Posts
    29

    + 2 way to do this

    sql:

    select name from MSysObjects where type = 1 - list of tables


    error handling:

    dim RS as new Adodb.recordset
    on error resume next
    rs.open "Select top 0 * from requeriedtablename", ....
    if err.number = 0 then
    msgbox "Table exists"
    else
    msgbox "Table NOT exists"
    end if

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I think hammbakkas (and alexeyK's) is more efficient as it doesn't require scrolling through all the tables.

    Must be Monday, I'm a little slow!!

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Please note that the use of MSysObjects is NOT supported by MS and may change or whatever, as they see fit without changelogs or whatever.

    So i would not change AlexeyK's solution

    Regards

Posting Permissions

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