Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Youngstown, Ohio, USA
    Thanked 0 Times in 0 Posts

    Check 'if exist' on creating table by vba (2000 SR1)

    I have a module that when run will create two linked tables, using the run-date to determine which tables to link to, and what to name the tables. The module has an 'On Error GoTo' control that will display a message and halt the code if an error is encountered, but I would like to include a more graceful check first. I would like something like 'if not exist %tablename% then create %tablename%' so that it can simply bypass any unnecessary steps instead of completely erroring out.

    I have an Excel function that uses a loop to run through the named ranges in a spreadsheet, and I was thinking something of the sort could be used to solve my problem - but I couldn't find how to gain access to the database's list of tablenames.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Check 'if exist' on creating table by vba (2000 SR1)

    You could use this function:

    Function TableExists(TableName As String) As Boolean
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    ' Loop through AllTables collection.
    For Each obj In dbs.AllTables
    If obj.Name = TableName Then
    TableExists = True
    Exit For
    End If
    Next obj
    Set obj = Nothing
    Set dbs = Nothing
    End Function

Posting Permissions

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