使用DAO和ADODB 2中方法获取,结果显示在Debug中,以”,“分开,方便复制到Excel中处理。
附上常用对应关系。
'DAO ADO Type '1 11 Yes/No '2 17 Number byte '3 2 Number Integer '4 3 Number LongInteger '4 3 AutoNumber '5 6 Currency '6 4 Number Single '7 5 Number Double '15 72 Number Replication ID '20 131 Number Decimal '8 7 Date/Time '10 202 Short Text '11 205 OLE Object '12 203 Hyperlink '12 203 Long Text '16 20 Large Number '26 135 Date/Time Extended '101 203 Attachement
程序如下:
Sub GetDatabaseType() Dim DBE As Object, DB As Object, Tbl As Object, TbName As String Dim Rst As Object, SQL As String Dim i As Long TbName = "Hand" '表名 SQL = "SELECT * FROM " & TbName Set Rst = CreateObject("ADODB.RecordSet") Set DBE = CreateObject("DAO.DBEngine.120") Set DB = DBE.OpenDatabase(PathBas & "\Database\xxxxx.accdb")'路径 Set Tbl = DB.TableDefs(TbName) Rst.Open SQL, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathBas & "\Database\xxxxx.accdb", 1, 1 With Tbl For i = 0 To .Fields.Count - 1 Debug.Print .Fields(i).Name & "," & .Fields(i).Type & "," & Rst.Fields(i).Type Next End With Rst.Close DB.Close Set Rst = Nothing Set Tbl = Nothing Set DB = Nothing Set DBE = Nothing End Sub