'取某数据库下的数据表

'
'取某数据库下的数据表
'函数名:GetDbTabS
'参数:  DBconn ADO连接
'返回值:包含数据表的字符串数组
'例:    TabArr=GetDbTabS(P_CNN)

Public Function GetDbTabs(ByRef DBconn As ADODB.Connection) As String()
   Dim RstSchema As ADODB.Recordset
   Dim strCnn As String
   Dim ReturnVal() As String
   Dim ReID As Long
  
   On Error Resume Next
  
   Set RstSchema = DBconn.OpenSchema(adSchemaTables)
  
   Do Until RstSchema.EOF
      If UCase$(Left$(RstSchema.Fields("TABLE_TYPE"), 3)) <> "SYS" Then
            ReID = ReID + 1
            ReDim Preserve ReturnVal(ReID)
            ReturnVal(ReID - 1) = RstSchema.Fields("TABLE_NAME") ' & ":" & RstSchema!TABLE_TYPE
      End If
      RstSchema.MoveNext
   Loop
   RstSchema.Close
   Set RstSchema = Nothing
   GetDbTabs = ReturnVal
End Function

 

 

'列出所有的表名
Private Sub Command2_Click()
    Dim TableName As String
    Set rs = conn.OpenSchema(adSchemaTables)
    Do While Not rs.EOF
        TableName = rs.Fields("TABLE_NAME")
        List1.AddItem TableName
        rs.MoveNext
   Loop
End Sub

Private Sub Form_Load()
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "/db.mdb;Persist Security Info=False"
    conn.Open
    rs.Open "select * from  info", conn, adOpenKeyset, adLockOptimistic
 End Sub

取ACCESS表名.
'-----------------------------------------------
引用.
Microsoft Activex Data Objects 2.5 Library
Microsoft Ado Ext 2.6 For DDL And Security
'=====================================================
Public mCon As ADODB.Connection
Public mCat As ADOX.Catalog

Public DB_Name As String
Public DB_Title As String

Private Sub Command1_Click()
    Dim I As Long
    Dim TBL As ADOX.Table
   
    If Not mCon Is Nothing Then Set mCon = Nothing
    Set mCon = New ADODB.Connection
   
    mCon.Provider = "Microsoft.Jet.OLEDB.4.0"
    mCon.Mode = adModeRead
    mCon.CursorLocation = adUseClient
    mCon.Properties("Data Source") = "E:/WORKSHAR/CODE.MDB"
    mCon.Properties("Jet OLEDB:Database Password") = ""
    mCon.Open
   
    Set mCat = New ADOX.Catalog
    mCat.ActiveConnection = mCon
   
    For Each TABL In mCat.Tables
        Debug.Print TABL.Name
    Next
End Sub