Effect:
Core code:
Copy codeThe Code is as follows: Option Explicit
Dim arrTables (), I, idxTables, intValidArgs
Dim blnContent, blnFieldNames
Dim objConn, objFSO, objRS, objSchema
Dim strConnect, strHeader, strOutput
Dim strFile, strResult, strSQL, strTable
Const adSchemaTables = 20
'Check command line arguments
With WScript. Arguments
If. Unnamed. Count = 1 Then
StrFile =. Unnamed (0)
Else
Syntax
End If
BlnFieldNames = True
BlnContent = True
If. Named. Count> 0 Then
IntValidArgs = 0
If. Named. Exists ("T") Then
BlnFieldNames = False
BlnContent = False
IntValidArgs = intValidArgs + 1
End If
If. Named. Exists ("TF") Then
BlnContent = False
IntValidArgs = intValidArgs + 1
End If
If intValidArgs <>. Named. Count Then Syntax
End If
End
'Check if the specified database file exists
Set objFSO = CreateObject ("Scripting. FileSystemObject ")
If Not objFSO. FileExists (strFile) Then Syntax
Set objFSO = Nothing
'Connect to the MS-Access database
Set objConn = CreateObject ("ADODB. Connection ")
StrConnect = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" & strFile
ObjConn. Open strConnect
'Search for user tables and list them in an array
Set objSchema = objConn. OpenSchema (adSchemaTables)
IdxTables =-1
Do While Not objSchema. EOF
If objSchema. Fields. Item (3). Value = "TABLE" Then
IdxTables = idxTables + 1
ReDim Preserve arrTables (idxTables)
ArrTables (idxTables) = objSchema. Fields. Item (2). Value
End If
ObjSchema. MoveNext
Loop
'List all tables, their column names and their contents
For Each strTable In arrTables
StrSQL = "Select * From" & strTable
Set objRS = objConn. Execute (strSQL)
If IsObject (objRS) Then
'Display the current table's name
If blnContent Then
WScript. Echo "Table:" & strTable &""""
Else
WScript. Echo "& strTable &""""
End If
If blnFieldNames Then
StrOutput = ""
Do While Not objRS. EOF
'Create a header line with the column names and data types
StrHeader = ""
For I = 0 To objRS. Fields. Count-1
StrHeader = strHeader &",""["_
& GetDataTypeDesc (objRS. Fields. Item (I). Type) & "]" _
& ObjRS. Fields. Item (I). Name &""""
Next
StrHeader = Mid (strHeader, 2)
If blnContent Then
'List the fields of the current record in comma delimited format
StrResult = ""
For I = 0 To objRS. Fields. Count-1
StrResult = strResult & "," "& objRS. Fields. Item (I). Value &""""
Next
'Add the current record to the output string
StrOutput = strOutput & Mid (strResult, 2) & vbCrLf
End If
'Next record
ObjRS. MoveNext
Loop
'List the results for the current table
WScript. Echo strHeader & vbCrLf & strOutput & vbCrLf
End If
End If
Next
ObjRS. Close
ObjSchema. Close
ObjConn. Close
Set objRS = Nothing
Set objSchema = Nothing
Set objConn = Nothing
Function GetDataTypeDesc (myTypeNum)
Dim arrTypes (8192), I
For I = 0 To UBound (arrTypes)
ArrTypes (I) = "???? "
Next
ArrTypes (0) = "Empty"
ArrTypes (2) = "SmallInt"
ArrTypes (3) = "Integer"
ArrTypes (4) = "Single"
ArrTypes (5) = "Double"
ArrTypes (6) = "Currency"
ArrTypes (7) = "Date"
ArrTypes (8) = "BSTR"
ArrTypes (9) = "IDispatch"
ArrTypes (10) = "Error"
ArrTypes (11) = "Boolean"
ArrTypes (12) = "Variant"
ArrTypes (13) = "IUnknown"
ArrTypes (14) = "Decimal"
ArrTypes (16) = "TinyInt"
ArrTypes (17) = "UnsignedTinyInt"
ArrTypes (18) = "UnsignedSmallInt"
ArrTypes (19) = "UnsignedInt"
ArrTypes (20) = "BigInt"
ArrTypes (21) = "UnsignedBigInt"
ArrTypes (64) = "FileTime"
ArrTypes (72) = "GUID"
ArrTypes (128) = "Binary"
ArrTypes (129) = "Char"
ArrTypes (130) = "WChar"
ArrTypes (131) = "Numeric"
ArrTypes (132) = "UserDefined"
ArrTypes (133) = "DBDate"
ArrTypes (134) = "DBTime"
ArrTypes (135) = "DBTimeStamp"
ArrTypes (136) = "Chapter"
ArrTypes (138) = "PropVariant"
ArrTypes (139) = "VarNumeric"
ArrTypes (200) = "VarChar"
ArrTypes (201) = "LongVarChar"
ArrTypes (202) = "VarWChar"
ArrTypes (203) = "LongVarWChar"
ArrTypes (204) = "VarBinary"
ArrTypes (205) = "LongVarBinary"
ArrTypes (8192) = "Array"
GetDataTypeDesc = arrTypes (myTypeNum)
End Function
Sub Syntax
Dim strMsg
StrMsg = strMsg & vbCrLf _
& Amp; "AccessRd. vbs, Version 1.01" & vbCrLf _
& "Display MS Access database (user) tables and, optionally, their contents "_
& VbCrLf _
& "Usage: CSCRIPT // nologo accessrd. VBS access_db_file [/T |/TF]" _
& VbCrLf _
& "Where:" "access_db_file" "is an MS-Access database file" & vbCrLf _
& "/T list table names only" & vbCrLf _
& "/TF list table and field names only" & vbCrLf _
& "(Default is list tables, field names AND contents )"_
& VbCrLf _
& "Written by Rob van der Woude" & vbCrLf _
& "Http://www.robvanderwoude.com"
WScript. Echo strMsg
WScript. Quit (1)
End Sub
Usage:
AccessRd. vbs, Version 1.01 Display MS Access database (user) tables and, optionally, their contents
Usage: CSCRIPT // nologo accessrd. VBS access_db_file [/T |/TF] Where: "access_db_file" is an MS-Access database file
/T list table names only
/TF list table and field names only
(Default is list tables, field names AND contents) Written by Rob van der Woudehttp: // www.robvandrewoude.com
Package and download test code
