Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Unanswered: deleting tables by code

    Hello,

    My problem is:
    I am doing an import. Ok, no problem, the thing is after this import sometimes some tables are generated, the names of the tables are Output_ImportErrorsX, where X is a number. I want to delete this tables in VBA. I know I can do it with DoCmd.DeleteObject table, name
    But the problem is that I want to know in runtime if these tables were generated and delete all of them, more or less, like this:

    i = 0
    For i=0 until number of tables which start with Output_ImportErrors
    DoCmd.DeleteObject table, "Output_ImportErrors" & i

    I don't know how to find out in runtime if these tables exist and how many they are.

    Any idea or orientation?

    Thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Make a function called IsTableExist which proof whether the given table exists or not

    PHP Code:
      If exists then
        DoCmd
    .RunSql ("Drop Table ....."
    OR use
        
    DoCmd.DeleteObject table"....."   
      
    Endif 

  3. #3
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100
    Quote Originally Posted by hammbakka
    Make a function called IsTableExist which proof whether the given table exists or not

    PHP Code:
      If exists then
        DoCmd
    .RunSql ("Drop Table ....."
    OR use
        
    DoCmd.DeleteObject table"....."   
      
    Endif 
    What is the result from the SQL Drop?

    Thanks by the way.

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    The SQL "Drop Table yourTableName" will Delete that table form the DB

  5. #5
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Red face

    ok, I am too silly. I understood what you explained me. But the problem is how can I prove that the table exists, is any command?

    thanks

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    No, you have to write yourself. Start now, I have to do some work and I'll help you later.

  7. #7
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100
    It is ok. I will see how can I guess in runtime if one table exists or not.

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Post

    Here is the function to check whether a table exists or not
    PHP Code:
    Function TExists(TableName As String) As Boolean
      Dim db 
    As Database
      Dim tbl 
    As Recordset

        On Error 
    GoTo ERR_TExists

          Set db 
    CurrentDb
          Set tbl 
    db.OpenRecordset(TableName)
          
    TExists True
          Set tbl 
    Nothing
        
      EXIT_TExists
    :
        Exit Function

      
    ERR_TExists:
         
    TExists False
         Set tbl 
    Nothing
         Resume EXIT_TExists
    End 
    Function 

  9. #9
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Talking :-d

    Hey Hammbakka, thanks a lot.
    It is not the first time, you are helping me.

    Now I am finishing my project ...

  10. #10
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Glad to be able to help you....
    Prima

Posting Permissions

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