How to use vbscript to read Access database
This article mainly introduces how to use vbscript to read Access database, the article is very detailed, has a certain reference value, interested friends must read it!
The effect is shown in the figure:
Core code:
The 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 With
'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 = "Binary"
ArrTypes (129) = "Char"
ArrTypes = "WChar"
ArrTypes = "Numeric"
ArrTypes = "UserDefined"
ArrTypes = "DBDate"
ArrTypes = "DBTime"
ArrTypes = "DBTimeStamp"
ArrTypes = "Chapter"
ArrTypes = "PropVariant"
ArrTypes (139) = "VarNumeric"
ArrTypes = "VarChar"
ArrTypes = "LongVarChar"
ArrTypes (202) = "VarWChar"
ArrTypes = "LongVarWChar"
ArrTypes (204) = "VarBinary"
ArrTypes (205) = "LongVarBinary"
ArrTypes (8192) = "Array"
GetDataTypeDesc = arrTypes (myTypeNum)
End Function
Sub Syntax
Dim strMsg
StrMsg = strMsg & vbCrLf _
& "AccessRd.vbs, Version 1.01" & vbCrLf _
& "Display MS Access database (user) tables and, optionally, their contents" _
& vbCrLf & vbCrLf _
& "Usage: CSCRIPT / / NOLOGO ACCESSRD.VBS access_db_file [/ T | / TF]" _
& vbCrLf & 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 & vbCrLf _
& "Written by Rob van der Woude" & vbCrLf _
& "http://www.robvanderwoude.com"
WScript.Echo strMsg
WScript.Quit (1)
End Sub
How to use it:
AccessRd.vbs, Version 1.01Display 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 Woude http://www.robvanderwoude.com
The above is all the contents of the article "how to use vbscript to read Access database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
