Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: Check if a table exsist and delete the table if..

    I would like to Check if a table exsist and delete the table if it does, i would be using a ado command object. would anyone know what sql i could use?

    Or if you know .net how i can use executescalar and then execute?? to delte the tbl thnks Happy holidayz


    ~M

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I attached a function I use in DAO to find out whether a table exists.

    Purhaps this will give you some ideas.
    Attached Files Attached Files
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

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

    Re: Check if a table exsist and delete the table if..

    Originally posted by mikezcg
    I would like to Check if a table exsist and delete the table if it does, i would be using a ado command object. would anyone know what sql i could use?

    Or if you know .net how i can use executescalar and then execute?? to delte the tbl thnks Happy holidayz


    ~M
    Use the next function to detect whether a table exist or not

    Function Table_Exist(TableName As String) As Boolean
    Dim db As Database
    Dim tbl As Recordset

    On Error GoTo ERR_Table_Exist

       Set db = CurrentDb
       Set tbl = db.OpenRecordset(TableName)
       Table_Exist = True
       Set tbl = Nothing

    EXIT_Table_Exist:
       Exit Function

    ERR_Table_Exist:
       Table_Exist = False
       Set tbl = Nothing
       Resume EXIT_Table_Exist
    End Function

    To delete a table use
    DoCmd.RunSQL("Drop Table yourTableName;")

  4. #4
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Help.

    How do you find out whether a table exists
    using an ADO command?


    I can't use the DAO "Dim db As Database"

    anyone?
    Last edited by jpshay; 01-07-04 at 18:04.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by jpshay
    Help.

    How do you find out whether a table exists
    using an ADO command?


    I can't use the DAO "Dim db As Database"

    anyone?
    You have to open any Module, then click the Options/Preferences and check the Microsoft DAO 3.51 or 3.52

  6. #6
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks.

    Yes, I understand how to use the references. So it isn't an issue of not being able to see the correct DLL. I am deliverately moving away from DAO.

    So, the question remains, is there a command I can use to see if a table exists? Typically I have seen methods that seek to do something with a table and then trap the error that occurs when the table is not there. In fact, that is what both of our ideas listed in response to mikezcg's original question do.

    How do you do that same check with ADO type methods or commands?
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Sorry, with ADO, I have not much experience. I meant you ask about DAO.

  8. #8
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks anyway.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by jpshay
    Help.

    How do you find out whether a table exists
    using an ADO command?


    I can't use the DAO "Dim db As Database"

    anyone?
    Question: Why do you want to know if it exists before executing a DELETE command? Why not just perform the deletion and if the table does not exist there is no foul ...

  10. #10
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Sounds good.
    I was going to see if a table was present so I knew I wouldn't have to relink it or knew whether I should delete it. Various reasons. I just thought it would be nice.

    I guess one way to resolve this is to have a function that makes a catalog, loops through all documents in the catalog, and checks if any match the name I pass it.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

Posting Permissions

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