Private Function GetTabelsName(adoConS As ADODB.Connection) As String Dim rstSchema As ADODB.Recordset '读取数据库中的所有表名称 On Error GoTo ErrDo Set rstSchema = New ADODB.Recordset Set rstSchema = adoConS.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE")) rstSchema.MoveFirst Do Until rstSchema.EOF GetTabelsName = GetTabelsName & rstSchema!TABLE_NAME & "," rstSchema.MoveNext Loop rstSchema.Close Set rstSchema = Nothing GetTabelsName = Mid(GetTabelsName, 1, Len(GetTabelsName) - 1) Exit Function ErrDo: GetTabelsName = "" End Function
对 sql server 的:'返回值是一个字符串数组 '参数分别是:服务器名,数据库名,用户ID,密码 '如果你已有条活动连接,大可不需要以上参数,只需要传一个 oconn 然后稍微修改一下代码 Function GetTableNames(ByVal svrName As String, ByVal dbName As String, ByVal uID As String, ByVal Pwd As String) As String() '获取数据库中所有表名 Dim Tables() As String Dim i As Integer Dim oConn As New ADODB.Connection, oRs As New ADODB.Recordset
oRs.Open "SELECT name From sysobjects WHERE type = 'U' AND status > 0 order by name", oConn, adOpenForwardOnly, adLockReadOnly If Not oRs.EOF Then ReDim Tables(oRs.RecordCount - 1) i = 0 Do While Not oRs.EOF Tables(i) = oRs(0) i = i + 1 oRs.MoveNext Loop Else ReDim Tables(0) End If
oRs.Close Set oRs = Nothing
oConn.Close Set oConn = Nothing
GetTableNames = Tables End Function
strTemp = "select NAME from sysobjects where xtype='u' and name<>'dtproperties'" rs.Open strTemp, Cn, adOpenStatic, adLockReadOnly, adCmdText -------- www.vicmiao.com 努力就有美好时光!
Dim rstSchema As ADODB.Recordset
'读取数据库中的所有表名称
On Error GoTo ErrDo Set rstSchema = New ADODB.Recordset
Set rstSchema = adoConS.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
rstSchema.MoveFirst
Do Until rstSchema.EOF
GetTabelsName = GetTabelsName & rstSchema!TABLE_NAME & ","
rstSchema.MoveNext
Loop
rstSchema.Close
Set rstSchema = Nothing
GetTabelsName = Mid(GetTabelsName, 1, Len(GetTabelsName) - 1)
Exit Function
ErrDo:
GetTabelsName = ""
End Function
'参数分别是:服务器名,数据库名,用户ID,密码
'如果你已有条活动连接,大可不需要以上参数,只需要传一个 oconn 然后稍微修改一下代码
Function GetTableNames(ByVal svrName As String, ByVal dbName As String, ByVal uID As String, ByVal Pwd As String) As String()
'获取数据库中所有表名 Dim Tables() As String
Dim i As Integer
Dim oConn As New ADODB.Connection, oRs As New ADODB.Recordset
oConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=" & dbName & ";Data Source=" & svrName, uID, Pwd
oConn.CursorLocation = adUseClient
oRs.Open "SELECT name From sysobjects WHERE type = 'U' AND status > 0 order by name", oConn, adOpenForwardOnly, adLockReadOnly
If Not oRs.EOF Then
ReDim Tables(oRs.RecordCount - 1)
i = 0
Do While Not oRs.EOF
Tables(i) = oRs(0)
i = i + 1
oRs.MoveNext
Loop
Else
ReDim Tables(0)
End If
oRs.Close
Set oRs = Nothing
oConn.Close
Set oConn = Nothing
GetTableNames = Tables
End Function
rs.Open strTemp, Cn, adOpenStatic, adLockReadOnly, adCmdText
--------
www.vicmiao.com
努力就有美好时光!