VBA-access表信息的获取

1.首先我们来直接获取某个数据库的所有表信息

Sub 获取数据库中所有表的名称和类型()
    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 检查数据是否存在()
    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 "数据表<" & mytable & ">中不存在字<" & mycol & ">"
    Else
        MsgBox "数据表<" & mytable & ">中存在字<" & mycol & ">"
    End If
hhh:
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
    

End Sub

3.获取某个数据表的所有字段名称和类型，要注意的是用field.type 返回的不是字符串结果，而是其对应的数字，所以，用自定义函数inttostring来实现转换

Sub 获取数据表的所有表的名称和类型()
    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.转换函数

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