VB使用ADOX获得各种数据库的表

MSSQL的表

Public Function getSQL2kTable(DBServerName, sDBName, DBUser, DBPWD)

 On Error Resume Next
    Dim cat As New ADOX.Catalog
    Dim strTable As New ADOX.Table
    Dim i
  '  打开目录。
  'strTable = ""
  Dim strTb
 ' "Driver={SQL SERVER};SERVER=(LOCAL);UID=sa;PWD=123;DATABASE=QJ2002;AutoTranslate=false"
     'response.end
      cat.ActiveConnection = "Driver={SQL SERVER};SERVER=" & DBServerName & ";UID=" & DBUser & ";PWD=" & DBPWD & ";DATABASE=" & sDBName & ";AutoTranslate=false"

  ' cat.ActiveConnection = "Provider=SQLOLEDB;data source="&DBServerName&";initial catalog="& sDBName & _
  ' ";userid="& DBUser &";password="&DBPWD &""
     
   For i = 0 To cat.Tables.Count - 1
 '  response.write cat.Tables.Item(1).Type &"<br>"
    If cat.Tables.Item(i).Type = "TABLE" Then
   
        strTb = strTb & "," & cat.Tables.Item(i).Name
      ' response.write "<br>"& cat.Tables.Item(i).Name & "表共有" & cat.Tables.Item(i).Columns.Count & "列"
      '  For j = 0 To cat.Tables.Item(i).Columns.Count - 1
       
       ' cat.Tables.Item(I).Columns.Item(I).Attributes
      '  response.write "<br>　分别是："& cat.Tables.Item(i).Columns.Item(j).Name
      '  Next
       
    End If
   Next
    strTb = Mid(strTb, 2, Len(strTb))

   getSQL2kTable = strTb
 
End Function

 excel 的表

Public Function getExcelTable(Sour)
    Dim Driver, DBpath
    Driver = "Driver={Microsoft Excel Driver (*.xls)};"
    DBpath = "DBQ=" & Sour
    Dim cat As New ADOX.Catalog
    Dim strTable As New ADOX.Table
   'Dim myTable As ADOX.Table
  
  ' strTable = ""
   ' 打开目录。
   cat.ActiveConnection = Driver & DBpath

   For i = 0 To cat.Tables.Count - 1
   'MsgBox cat.Tables.Item(1).Type
   ' If cat.Tables.Item(i).Type = "TABLE" Then
   
        strTb = strTb & "," & cat.Tables.Item(i).Name

   ' End If
   Next
    strTb = Mid(strTb, 2, Len(strTb))
    getExcelTable = strTb
  
   
End Function

vfp的

Public Function getAccessTable(Sour)

     On Error Resume Next
    Dim cat As New ADOX.Catalog
    Dim strTable As New ADOX.Table
     strTb = ""
  '  打开目录。
 ' strTable = ""
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & Sour & ";"
    If Err.Number <> 0 Then
    Err.Clear
      cat.ActiveConnection = "DBQ=" + sFileWz + ";DefaultDir=;uid=" + sDBuser + ";pwd=" + sDBPWD + ";DRIVER={Microsoft Access Driver (*.mdb)};"
      If Err.Number <> 0 Then
      End If
     
    End If
   For i = 0 To cat.Tables.Count - 1
 '  response.write cat.Tables.Item(1).Type &"<br>"
    If cat.Tables.Item(i).Type = "TABLE" Then
        strTb = strTb & "," & cat.Tables.Item(i).Name
    End If
  Next
     strTb = Mid(strTb, 2, Len(strTb))
   getAccessTable = strTb
 
End Function