Geek Answers Handbook

How to check a table exists or does not exist

How to check the table or not?

Using VB 6.0

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

It works fine on the code, but if the table does not exist, then it shows that "the table does not exit"

How to check for a table or table?

Need help with VB CODE?

+5
source share
3 answers

For Jet MDB (and possibly in the general case for many OLEDB providers) you can use an approach such as:

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
+2
source

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

DROP TABLE t1 IF EXISTS

Other databases have a similar function, 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;

. .

+4

, .

The SQL syntax depends on the database / engine server used, but for Sql Server you can use something like:

Sql Server 2000:

SELECT 1 as Exists FROM sysobjects WHERE name = 't1'

Sql Server 2005/2008:

SELECT 1 as Exists FROM sys.objects WHERE name = 't1'

Then you can use VB, for example:

Dim rs as Recordset
Dim iExists as Integer

rs = cmd.Execute
On Error Goto DoesNotExist
rs.MoveFirst
iExists = CInt(rs!Exists)
DoesNotExist:
If iExists = 1 Then
 ' Put code here for if the table exists
Else
 ' Put code here for if the table does not exist
End If

Note. This code requires ordering and "productionising" =) (i.e. I have not actually tested that it works, since I do not have VB6 on this computer)

+1
source

More articles:


All Articles