Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determining if a table exist (2000)

    I have a procedure that runs through a list of excel files within a loop. A range is imported from each excel file and a table is created within the current database. After other operations to the table are processed within the loop, the imported table is deleted. Most of the time there are import errors that occur during the importation process. Since I know what causes the import errors, I simply include as part of the loop a command to also delete the "ImportError" Table. The problem is that some of the excel files do not generate an error when imported and I get a error that stops the procedure.
    What I would like to know is how to determine if the error table has been created so I can create an if statement that will skip the delete table command and move on the the next process.

    As always any suggestions are appreaciated.

    Thank You,

    Richard

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Determining if a table exist (2000)

    If you are doing this in VBA just put the following statement before the delete statement:
    On Error Goto 0
    Don't forget to put the other error statement after the delete statement.

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    St Albans, Hertfordshire, England
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining if a table exist (2000)

    There are two ways of doing this - the first is deemed the 'correct' way, yet the second way will work too

    1: Using the tabledefs collection, you can loop through each tabledef, and determine if it has a name that meets a certain criteria - sample code shown to list all tablenames in current database

    <pre>Dim db As Database
    Dim t_defs As TableDefs
    Dim t_def As TableDef

    Set db = CurrentDb
    Set t_defs = db.TableDefs
    For Each t_def In t_defs
    Debug.Print t_def.Name
    Next</pre>



    2: Access contains a system table called MSysObjects - you can query this table to determine the presence of an Error table, and then delete it using the methodology from 1 above - example SQL to list all tables

    SELECT MSysObjects.Name, MSysObjects.Type
    FROM MSysObjects
    WHERE (((MSysObjects.Type)=1));

    (obviously you would need to add further clauses to the WHERE statement)

    I hope that points you in the right direction <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Kind regards

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

    Re: Determining if a table exist (2000)

    I wouldn't say the first method is the "correct" way. However, in at least some versions of Access, if the system tables were hidden, the only way to query the MSysObjects table was with a saved query. SQL on the fly returned a "table not found" error. I think that may be why the first method became a standard. It works regardless.
    Charlotte

Posting Permissions

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