How to check table exist or not exist

Question

How to check table is there or not?

Using VB 6.0

cmd.CommandText = "drop table t1"
cmd.Execute

Above code is working fine, but if table is not exist then it showing “table does not exit”

How to check table exist or table not exist?

Need VB CODE help?

Answer 1

If you just want to drop the table without throwing an error message, you can use the following SQL if you're using MySQL.

DROP TABLE t1 IF EXISTS

Other databases have a similar feature, but the syntax is different. To do the same in MSSQL:

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1') DROP TABLE t1;

Although that looks very ugly.. there must be a better syntax to get the same result.

Answer 2

For a Jet MDB (and perhaps generically for many OLEDB Providers) you can use an approach like:

Private Sub Main()
    Dim cnDB As ADODB.Connection

    Set cnDB = New ADODB.Connection
    cnDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Jet OLEDB:Engine Type=5;Data Source='sample.mdb'"

    'Check presence of table --------------
    Dim rsSchema As ADODB.Recordset

    Set rsSchema = _
        cnDB.OpenSchema(adSchemaColumns, _
                        Array(Empty, Empty, "t1", Empty))
    If rsSchema.BOF And rsSchema.EOF Then
        MsgBox "Table does not exist"
    Else
        MsgBox "Table exists"
    End If
    rsSchema.Close
    Set rsSchema = Nothing
    '--------------------------------------

    cnDB.Close
End Sub