Shulou(Shulou.com)06/03 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.