Obtain the Access Table Structure

Some people often ask how to obtain the access table structure.

1. Public sub gettables ()
2. Dim RS as new ADODB. recordset
3. Dim conn as ADODB. Connection
4. Dim ssql as string
7. Set conn = currentproject. Connection
8. Ssql = "select ID, name from msysobjects where type = 1 and flags = 0"
9. Rs. Open ssql, Conn
10. Do while not Rs. EOF
11. Debug. Print "*****"; RS. Fields ("name"). value ;"****************"
12. Gettablecolumns Rs. Fields ("name"). Value
13. Rs. movenext
14. Loop
15. Rs. Close
16. Set rs = nothing
17. Set conn = nothing
19. End sub
21. Public sub gettablecolumns (stablename as string)
22. Dim RS as new ADODB. recordset
23. Dim conn as ADODB. Connection
24. Dim metadata as ADODB. Field
26. Set conn = currentproject. Connection
27. Rs. Open stablename, Conn, adcmdtable
30. For each errors in RS. Fields
31. Debug. Print consumer. Name, sfldtype (consumer. type), consumer. numericscale, consumer. Precision
33. Next
35. Rs. Close
36. Set rs = nothing
37. Set conn = nothing
39. End sub
43. Public Function sfldtype (itype as integer)
44. Select case itype
45. Case 20
46. Sfldtype = "bigint"
47. Case 1, 128
48. Sfldtype = "binary"
49. Case 11
50. Sfldtype = "Boolean"
51. Case 8
52. Sfldtype = "BSTR"
53. Case 1, 136
54. Sfldtype = "chapter"
55. Case 1, 129
56. Sfldtype = "char"
57. Case 6
58. Sfldtype = "currency"
59. Case 7
60. Sfldtype = "date"
61. Case 1, 133
62. Sfldtype = "dbdate"
63. Case 1, 134
64. Sfldtype = "dbtime"
65. Case 1, 135
66. Sfldtype = "dbtimestamp"
67. Case 14
68. Sfldtype = "decimal"
69. Case 5
70. Sfldtype = "double"
71. Case 0
72. Sfldtype = "empty"
73. Case 10
74. Sfldtype = "error"
75. Case 64
76. Sfldtype = "filetime"
77. Case 72
78. Sfldtype = "guid"
79. Case 9
80. Sfldtype = "idispatch"
81. Case 3
82. Sfldtype = "integer"
83. Case 13
84. Sfldtype = "iunknown"
85. Case 1, 205
86. Sfldtype = "longvarbinary"
87. Case 1, 201
88. Sfldtype = "longvarchar"
89. Case 1, 203
90. Sfldtype = "longvarwchar"
91. Case 1, 131
92. Sfldtype = "numeric"
93. Case 1, 138
94. Sfldtype = "propvariant"
95. Case 4
96. Sfldtype = "single"
97. Case 2
98. Sfldtype = "smallint"
99. Case 16
100. Sfldtype = "tinyint"
101. Case 21
102. Sfldtype = "unsignedbigint"
103. Case 19
104. Sfldtype = "unsignedint"
105. Case 18
106. Sfldtype = "unsignedsmallint"
107. Case 17
108. Sfldtype = "unsignedtinyint"
109. Case 1, 132
110. Sfldtype = "userdefined"
111. Case 1, 204
112. Sfldtype = "varbinary"
113. Case 1, 200
114. Sfldtype = "varchar"
115. Case 12
116. Sfldtype = "variant"
117. Case 1, 139
118. Sfldtype = "varnumeric"
119. Case 1, 202
120. Sfldtype = "varwchar"
121. Case 1, 130
122. Sfldtype = "wchar"
123. Case else
124. Sfldtype = "unknown"
125. End select
127. End Function