Rob van der Woude's Scripting Pages

VBScript Scripting Techniques > Databases > MS-Access

Read MS-Access databases

 

ADODB
VBScript Code:
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(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 _
           & "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
 
Requirements:
Windows version: 2000 with MDAC 2.8, XP (SP1 or later), Server 2003, or Vista
Network: any
Client software: MDAC 2.8 for Windows 2000
Script Engine: any
Summarized: Works in Windows 2000 or later, Windows 2000 requires MDAC 2.8.
 
[Back to the top of this page]

 

Note: This source code does not contain the full list of constants, only the one used in this sample.
If you intend to experiment with this code, download and use the full source code.

page last uploaded: 2016-09-19, 14:58