ado连接mysql状态的判断_使用ADO访问数据库时怎样判断某一表名是否存在

方法1:

Dim adoConnectionX As New ADODB.Connection

Dim adoSchemaRecordsetX As New ADODB.Recordset

'Ms SQL 7:

'adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS"

'Access 2000:

adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"

Dim TestTableName As String

TestTableName = "产品"

Set adoSchemaRecordsetX = adoConnectionX.OpenSchema(adSchemaTables, Array(Empty, Empty, TestTableName, "Table"))

If Not adoSchemaRecordsetX.EOF Then

MsgBox "[" & TestTableName & "]表已存在!"

End If

方法2(引用Microsoft ADO Extensions 2.1 for DDL and Security (ADOX)):

(主持人注：需要升级至VB 6.0SP3)

Dim adoConnectionX As New ADODB.Connection

'Ms SQL 7:

adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS"

'Access 2000:

'adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"

Dim adoxCatalogX As New ADOX.Catalog

Set adoxCatalogX.ActiveConnection = adoConnectionX

Dim TestTableName As String

TestTableName = "产品"

Dim adoxTableX As ADOX.Table

For Each adoxTableX In adoxCatalogX.Tables

If adoxTableX.Name = TestTableName Then

MsgBox "[" & TestTableName & "]表已存在!"

Exit For

End If

Next

主持人注：方法2也可以这样：

Private Sub Command1_Click()

Dim cat As ADOX.Catalog

Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog

Set cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51;" _

& "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb"

On Error Resume Next

Set tbl = cat.Tables("MyTable")

If tbl Is Nothing Then

MsgBox "MyTable doesn't exist"

Else

MsgBox "MyTable exists"

Set tbl = Nothing

End If

Set cat = Nothing

Set con = Nothing

End Sub

方法三：

select * from sysobjects where name='TblName'

if rst.recordcount=0 then

不存在此表

else

存在

end if