vba: Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim rs1 As New ADODB.Recordset Dim rs2 As New ADODB.Recordset Dim rs3 As New ADODB.Recordset ff = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=test;USER=root;Pwd=123;" cn.Open ff Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE")) i=0 While Not rs.EOF If rs("table_type") = "TABLE" Then i=i+1 End If rs.MoveNext Wend msgbox i
or Dim rs As New ADODB.Recordset Dim rs1 As New ADODB.Recordset Dim rs2 As New ADODB.Recordset Dim rs3 As New ADODB.Recordset ff = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=test;USER=root;Pwd=123;" cn.Open ff Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE")) rs.MoveLast rs.MoveFirst MsgBox rs.RecordCount
Dim cn As New ADODB.Connection Dim ff1 As New ADOX.Catalog i = 0 ff = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=TEST;USER=root;Pwd=123;" cn.Open ff Set ff1.ActiveConnection = cn MsgBox ff1.Tables.Count For Each ff In ff1.Tables If ff.Type = "TABLE" Then i = i + 1 End If这样TABLE数字是准确的
很简单,你先执行 show tables; 然后直接 select FOUND_ROWS();通过FOUND_ROWS()得到表的个数。
Dim cn As New ADODB.Connection Dim ff1 As New ADOX.Catalog i = 0 ff = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=TEST;USER=root;Pwd=123;" cn.Open ff Set ff1.ActiveConnection = cn MsgBox ff1.Tables.Count上述代码有Dim cn As New ADODB.Connection Dim ff1 As New ADOX.Catalog i = 0 ff = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=TEST;USER=root;Pwd=123;" cn.Open ff Set ff1.ActiveConnection = cn MsgBox ff1.Tables.Count For Each ff In ff1.Tables If ff.Type = "TABLE" Then i = i + 1 End IfI中的值就是准确的TABLE数字
楼上啊,MySQL 5.0 才开始支持 view
最简单的方法,就是直接 show tables;一下,然后检查一下返回的记录行数。这样在脚本中,PHP,或者ADO等接口中均可
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
ff = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=test;USER=root;Pwd=123;"
cn.Open ff
Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
i=0
While Not rs.EOF
If rs("table_type") = "TABLE" Then
i=i+1
End If
rs.MoveNext
Wend
msgbox i
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
ff = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=test;USER=root;Pwd=123;"
cn.Open ff
Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
rs.MoveLast
rs.MoveFirst
MsgBox rs.RecordCount
Dim ff1 As New ADOX.Catalog
i = 0
ff = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=TEST;USER=root;Pwd=123;"
cn.Open ff
Set ff1.ActiveConnection = cn
MsgBox ff1.Tables.Count
For Each ff In ff1.Tables
If ff.Type = "TABLE" Then
i = i + 1
End If这样TABLE数字是准确的
然后直接 select FOUND_ROWS();通过FOUND_ROWS()得到表的个数。
select found_rows()
这种方法,VIEW也计算了的,不是准确的TABLE数字,一般用ADO、ADOX取得
Dim ff1 As New ADOX.Catalog
i = 0
ff = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=TEST;USER=root;Pwd=123;"
cn.Open ff
Set ff1.ActiveConnection = cn
MsgBox ff1.Tables.Count上述代码有Dim cn As New ADODB.Connection
Dim ff1 As New ADOX.Catalog
i = 0
ff = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=TEST;USER=root;Pwd=123;"
cn.Open ff
Set ff1.ActiveConnection = cn
MsgBox ff1.Tables.Count
For Each ff In ff1.Tables
If ff.Type = "TABLE" Then
i = i + 1
End IfI中的值就是准确的TABLE数字