免费视频淫片aa毛片_日韩高清在线亚洲专区vr_日韩大片免费观看视频播放_亚洲欧美国产精品完整版

1.首先我們來直接獲取某個數(shù)據(jù)庫的所有表信息

Sub 獲取數(shù)據(jù)庫中所有表的名稱和類型()
    Dim i As String
    Dim mydata As String
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    mydata = ThisWorkbook.Path & "\學生管理.accdb"
    With con
        .Provider = "microsoft.ace.oledb.12.0"
        .Open mydata
    End With
    Cells.Clear
    Range("A1:B1") = Array("表名稱", "表類型")
    i = 2
    Set rs = con.OpenSchema(adSchemaTables)
    Do Until rs.EOF
    If rs("table_type") = "TABLE" Then
        Cells(i, 1) = rs("table_name")
        Cells(i, 2) = rs("table_type")
        i = i + 1
    End If
        rs.MoveNext
    Loop
    Columns.AutoFit
    rs.Close
    Set rs = Nothing
    Set con = Nothing
End Sub

2.檢查某個字段是否存在

Sub 檢查數(shù)據(jù)是否存在()
    Dim mydata As String
    Dim mytable As String
    Dim mycol As String
    Dim con As New ADODB.Connection
    Dim rs As ADODB.Recordset
    mydata = ThisWorkbook.Path & "\學生管理.accdb"
    mytable = "學生"
    mycol = "姓名"
    With con
        .Provider = "microsoft.ace.oledb.12.0"
        .Open mydata
    End With
    Set rs = con.OpenSchema(adSchemaColumns)
    rs.Find "column_name='" & mycol & "'"
    If rs.EOF Then
        MsgBox "數(shù)據(jù)表<" & mytable & ">中不存在字<" & mycol & ">"
    Else
        MsgBox "數(shù)據(jù)表<" & mytable & ">中存在字<" & mycol & ">"
    End If
hhh:
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
End Sub

3.獲取某個數(shù)據(jù)表的所有字段名稱和類型，要注意的是用field.type 返回的不是字符串結果，而是其對應的數(shù)字，所以，用自定義函數(shù)inttostring來實現(xiàn)轉換

Sub 獲取數(shù)據(jù)表的所有表的名稱和類型()
    Dim i As Integer
    Dim mydata As String
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim mytable As String
    mydata = ThisWorkbook.Path & "\學生管理.accdb"
    mytable = "學生"
    With con
        .Provider = "microsoft.ace.oledb.12.0"
        .Open mydata
    End With
    Cells.Clear
    Range("A1:C1") = Array("字段名", "字段類型", "字段大小")
    i = 2
    Dim myfield As ADODB.Field
    rs.Open mytable, con, adOpenKeyset, adLockOptimistic '直接打開對應的表
    For Each myfield In rs.Fields
        Range("A" & i) = myfield.Name
        'field.type 用于獲取字段的類型，但不會直接返回類型的字符串
        '而是返回表示該類型的一個integer
        'Range("B" & i) = myfield.Type
         Range("B" & i) = inttostring(myfield.Type)
        Range("C" & i) = myfield.DefinedSize
        i = i + 1
    Next
    Columns.AutoFit
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
End Sub

4.轉換函數(shù)

Function inttostring(myint As Integer) As String
    Dim mystr As String
    Select Case myint
        Case 20: mystr = "adBigInt"
        Case 128: mystr = "adBinary"
        Case 11: mystr = "adBoolean"
        Case 8: mystr = "adBSTR"
        Case 136: mystr = "adChapter"
        Case 126: mystr = "adChar"
        Case 6: mystr = "adCurrency"
        Case 7: mystr = "adDate"
        Case 133: mystr = "adDBDate"
        Case 134: mystr = "adDBTime"
        Case 135: mystr = "adDBTimeStamp"
        Case 14: mystr = "adDecimal"
        Case 5: mystr = "adDouble"
        Case 0: mystr = "adEmpty"
        Case 10: mystr = "adError"
        Case 64: mystr = "adFileTime"
        Case 72: mystr = "adGUID"
        Case 9: mystr = "adIDispatch"
        Case 3: mystr = "adInteger"
        Case 205: mystr = "adLongVarBinary"
        Case 201: mystr = "adLongVarChar"
        Case 203: mystr = "adLongVarWchar"
        Case 131: mystr = "adNumeric"
        Case 130: mystr = "adPropVariant"
        Case 4: mystr = "adSingle"
        Case 2: mystr = "adSmallInt"
        Case 16: mystr = "adTinyInt"
        Case 21: mystr = "adUnsignedBigInt"
        Case 19: mystr = "adUnsignedInt"
        Case 18: mystr = "adUnsignedSmallInt"
        Case 17: mystr = "adUnsignedTinyInt"
        Case 132: mystr = "adUserDefined"
        Case 204: mystr = "adVarBinary"
        Case 200: mystr = "adVarchar"
        Case 12: mystr = "adVariant"
        Case 139: mystr = "adVarNumeric"
        Case 202: mystr = "adVarWChar"
        Case 130: mystr = "adWChar"
        Case Else: mystr = "error"
    End Select
    inttostring = mystr
End Function