Rob van der Woude's Scripting Pages
Powered by GeSHi

Source code for accessrd.vbs

(view source code of accessrd.vbs as plain text)

  1. Option Explicit
  2.  
  3. Dim arrTables( ), i, idxTables, intValidArgs
  4. Dim blnContent, blnFieldNames
  5. Dim objConn, objFSO, objRS, objSchema
  6. Dim strConnect, strHeader, strOutput
  7. Dim strFile, strResult, strSQL, strTable
  8.  
  9. ' ADODB Data Types
  10. Const adEmpty                        =    0
  11. Const adSmallInt                     =    2
  12. Const adInteger                      =    3
  13. Const adSingle                       =    4
  14. Const adDouble                       =    5
  15. Const adCurrency                     =    6
  16. Const adDate                         =    7
  17. Const adBSTR                         =    8
  18. Const adIDispatch                    =    9
  19. Const adError                        =   10
  20. Const adBoolean                      =   11
  21. Const adVariant                      =   12
  22. Const adIUnknown                     =   13
  23. Const adDecimal                      =   14
  24. Const adTinyInt                      =   16
  25. Const adUnsignedTinyInt              =   17
  26. Const adUnsignedSmallInt             =   18
  27. Const adUnsignedInt                  =   19
  28. Const adBigInt                       =   20
  29. Const adUnsignedBigInt               =   21
  30. Const adFileTime                     =   64
  31. Const adGUID                         =   72
  32. Const adBinary                       =  128
  33. Const adChar                         =  129
  34. Const adWChar                        =  130
  35. Const adNumeric                      =  131
  36. Const adUserDefined                  =  132
  37. Const adDBDate                       =  133
  38. Const adDBTime                       =  134
  39. Const adDBTimeStamp                  =  135
  40. Const adChapter                      =  136
  41. Const adPropVariant                  =  138
  42. Const adVarNumeric                   =  139
  43. Const adVarChar                      =  200
  44. Const adLongVarChar                  =  201
  45. Const adVarWChar                     =  202
  46. Const adLongVarWChar                 =  203
  47. Const adVarBinary                    =  204
  48. Const adLongVarBinary                =  205
  49. Const adArray                        = 8192
  50.  
  51. ' ADODB Schema Types
  52. Const adSchemaProviderSpecific       =   -1
  53. Const adSchemaAsserts                =    0
  54. Const adSchemaCatalogs               =    1
  55. Const adSchemaCharacterSets          =    2
  56. Const adSchemaCollations             =    3
  57. Const adSchemaColumns                =    4
  58. Const adSchemaCheckConstraints       =    5
  59. Const adSchemaConstraintColumnUsage  =    6
  60. Const adSchemaConstraintTableUsage   =    7
  61. Const adSchemaKeyColumnUsage         =    8
  62. Const adSchemaReferentialConstraints =    9
  63. Const adSchemaTableConstraints       =   10
  64. Const adSchemaColumnsDomainUsage     =   11
  65. Const adSchemaIndexes                =   12
  66. Const adSchemaColumnPrivileges       =   13
  67. Const adSchemaTablePrivileges        =   14
  68. Const adSchemaUsagePrivileges        =   15
  69. Const adSchemaProcedures             =   16
  70. Const adSchemaSchemata               =   17
  71. Const adSchemaSQLLanguages           =   18
  72. Const adSchemaStatistics             =   19
  73. Const adSchemaTables                 =   20
  74. Const adSchemaTranslations           =   21
  75. Const adSchemaProviderTypes          =   22
  76. Const adSchemaViews                  =   23
  77. Const adSchemaViewColumnUsage        =   24
  78. Const adSchemaViewTableUsage         =   25
  79. Const adSchemaProcedureParameters    =   26
  80. Const adSchemaForeignKeys            =   27
  81. Const adSchemaPrimaryKeys            =   28
  82. Const adSchemaProcedureColumns       =   29
  83. Const adSchemaDBInfoKeywords         =   30
  84. Const adSchemaDBInfoLiterals         =   31
  85. Const adSchemaCubes                  =   32
  86. Const adSchemaDimensions             =   33
  87. Const adSchemaHierarchies            =   34
  88. Const adSchemaLevels                 =   35
  89. Const adSchemaMeasures               =   36
  90. Const adSchemaProperties             =   37
  91. Const adSchemaMembers                =   38
  92. Const adSchemaTrustees               =   39
  93. Const adSchemaFunctions              =   40
  94. Const adSchemaActions                =   41
  95. Const adSchemaCommands               =   42
  96. Const adSchemaSets                   =   43
  97.  
  98. ' Check command line arguments
  99. With WScript.Arguments
  100. 	If .Unnamed.Count = 1 Then
  101. 		strFile = .Unnamed(0)
  102. 	Else
  103. 		Syntax
  104. 	End If
  105. 	blnFieldNames = True
  106. 	blnContent    = True
  107. 	If .Named.Count > 0 Then
  108. 		intValidArgs = 0
  109. 		If .Named.Exists( "T" ) Then
  110. 			blnFieldNames = False
  111. 			blnContent    = False
  112. 			intValidArgs  = intValidArgs + 1
  113. 		End If
  114. 		If .Named.Exists( "TF" ) Then
  115. 			blnContent    = False
  116. 			intValidArgs  = intValidArgs + 1
  117. 		End If
  118. 		If intValidArgs <> .Named.Count Then Syntax
  119. 	End If
  120. End With
  121.  
  122. ' Check if the specified database file exists
  123. Set objFSO = CreateObject( "Scripting.FileSystemObject" )
  124. If Not objFSO.FileExists( strFile ) Then Syntax
  125. Set objFSO = Nothing
  126.  
  127. ' Connect to the MS-Access database
  128. Set objConn = CreateObject( "ADODB.Connection" )
  129. strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile
  130. objConn.Open strConnect
  131.  
  132. ' Search for user tables and list them in an array
  133. Set objSchema = objConn.OpenSchema( adSchemaTables )
  134. idxTables = -1
  135. Do While Not objSchema.EOF
  136. 	If objSchema.Fields.Item(3).Value = "TABLE" Then
  137. 		idxTables = idxTables + 1
  138. 		ReDim Preserve arrTables( idxTables )
  139. 		arrTables( idxTables ) = objSchema.Fields.Item(2).Value
  140. 	End If
  141. 	objSchema.MoveNext
  142. Loop
  143.  
  144. ' List all tables, their column names and their contents
  145. For Each strTable In arrTables
  146. 	strSQL = "Select * From " & strTable
  147. 	Set objRS = objConn.Execute( strSQL )
  148. 	If IsObject( objRS ) Then
  149. 		' Display the current table's name
  150. 		If blnContent Then
  151. 			WScript.Echo """Table: " & strTable & """"
  152. 		Else
  153. 			WScript.Echo """" & strTable & """"
  154. 		End If
  155. 		If blnFieldNames Then
  156. 			strOutput = ""
  157. 			Do While Not objRS.EOF
  158. 	    		' Create a header line with the column names and data types
  159. 		    	strHeader = ""
  160. 	    		For i = 0 To objRS.Fields.Count - 1
  161.     				strHeader = strHeader & ",""[" _
  162. 				              & GetDataTypeDesc( objRS.Fields.Item(i).Type ) & "] " _
  163. 					          & objRS.Fields.Item(i).Name & """"
  164. 			    Next
  165.     			strHeader = Mid( strHeader, 2 )
  166. 	    		If blnContent Then
  167. 		    		' List the fields of the current record in comma delimited format
  168. 			    	strResult = ""
  169. 			    	For i = 0 To objRS.Fields.Count - 1
  170. 	    				strResult = strResult & ",""" & objRS.Fields.Item(i).Value & """"
  171.     				Next
  172. 			    	' Add the current record to the output string
  173. 			    	strOutput = strOutput & Mid( strResult, 2 ) & vbCrLf
  174. 		    	End If
  175. 	    		' Next record
  176. 	    		objRS.MoveNext
  177. 			Loop
  178. 			' List the results for the current table
  179. 			WScript.Echo strHeader & vbCrLf & strOutput & vbCrLf
  180. 		End If
  181. 	End If
  182. Next
  183.  
  184. objRS.Close
  185. objSchema.Close
  186. objConn.Close
  187. Set objRS     = Nothing
  188. Set objSchema = Nothing
  189. Set objConn   = Nothing
  190.  
  191.  
  192. Function GetDataTypeDesc( myTypeNum )
  193. 	Dim arrTypes( 8192 ), i
  194. 	For i = 0 To UBound( arrTypes )
  195. 		arrTypes( i ) = "????"
  196. 	Next
  197. 	arrTypes(0)     = "Empty"
  198. 	arrTypes(2)     = "SmallInt"
  199. 	arrTypes(3)     = "Integer"
  200. 	arrTypes(4)     = "Single"
  201. 	arrTypes(5)     = "Double"
  202. 	arrTypes(6)     = "Currency"
  203. 	arrTypes(7)     = "Date"
  204. 	arrTypes(8)     = "BSTR"
  205. 	arrTypes(9)     = "IDispatch"
  206. 	arrTypes(10)    = "Error"
  207. 	arrTypes(11)    = "Boolean"
  208. 	arrTypes(12)    = "Variant"
  209. 	arrTypes(13)    = "IUnknown"
  210. 	arrTypes(14)    = "Decimal"
  211. 	arrTypes(16)    = "TinyInt"
  212. 	arrTypes(17)    = "UnsignedTinyInt"
  213. 	arrTypes(18)    = "UnsignedSmallInt"
  214. 	arrTypes(19)    = "UnsignedInt"
  215. 	arrTypes(20)    = "BigInt"
  216. 	arrTypes(21)    = "UnsignedBigInt"
  217. 	arrTypes(64)    = "FileTime"
  218. 	arrTypes(72)    = "GUID"
  219. 	arrTypes(128)   = "Binary"
  220. 	arrTypes(129)   = "Char"
  221. 	arrTypes(130)   = "WChar"
  222. 	arrTypes(131)   = "Numeric"
  223. 	arrTypes(132)   = "UserDefined"
  224. 	arrTypes(133)   = "DBDate"
  225. 	arrTypes(134)   = "DBTime"
  226. 	arrTypes(135)   = "DBTimeStamp"
  227. 	arrTypes(136)   = "Chapter"
  228. 	arrTypes(138)   = "PropVariant"
  229. 	arrTypes(139)   = "VarNumeric"
  230. 	arrTypes(200)   = "VarChar"
  231. 	arrTypes(201)   = "LongVarChar"
  232. 	arrTypes(202)   = "VarWChar"
  233. 	arrTypes(203)   = "LongVarWChar"
  234. 	arrTypes(204)   = "VarBinary"
  235. 	arrTypes(205)   = "LongVarBinary"
  236. 	arrTypes(8192)  = "Array"
  237. 	GetDataTypeDesc = arrTypes( myTypeNum )
  238. End Function
  239.  
  240.  
  241. Sub Syntax
  242. 	Dim strMsg
  243. 	strMsg = strMsg & vbCrLf _
  244. 	       & "AccessRd.vbs,  Version 1.01" & vbCrLf _
  245. 	       & "Display MS Access database (user) tables and, optionally, their contents"  _
  246. 	       & vbCrLf & vbCrLf _
  247. 	       & "Usage:  CSCRIPT  //NOLOGO  ACCESSRD.VBS  access_db_file  [ /T | /TF ]" _
  248. 	       & vbCrLf & vbCrLf _
  249. 	       & "Where:  ""access_db_file""   is an MS-Access database file"   & vbCrLf _
  250. 	       & "        /T                 list table names only"             & vbCrLf _
  251. 	       & "        /TF                list table and field names only"   & vbCrLf _
  252. 	       & "                           (default is list tables, field names AND contents)" _
  253. 	       & vbCrLf & vbCrLf _
  254. 	       & "Written by Rob van der Woude" & vbCrLf _
  255. 	       & "http://www.robvanderwoude.com"
  256. 	WScript.Echo strMsg
  257. 	WScript.Quit(1)
  258. End Sub
  259.  

page last modified: 2024-04-16; loaded in 0.0108 seconds