16. February 2008
Bob
Office
Summary
This article shows you a Windows Script Host (WSH) application that will create a report based on the schema of an Access Database.
More Information
- Open Windows Notepad and copy/paste the WSH code listed below into it.
- Modify the
strDatabaseFile and strOutputFile constants for your database and desired report name.
- Save the file as "DatabaseScema.vbs" to your desktop.
- Double-click the WSH file to run it.
Note: A message box that says "Finished!" will appear when the script has finished executing.
Windows Script Host (WSH) Code
Option Explicit
' --------------------------------------------------
' Define variables and constants
' --------------------------------------------------
Const strDatabaseFile = "MusicStuff.mdb"
Const strOutputFile = "MusicStuff.htm"
Const adSchemaTables = 20
Dim strSQL
Dim strCN
Dim objCN
Dim objRS1
Dim objRS2
Dim objField
Dim intCount
Dim objFSO
Dim objFile
' --------------------------------------------------
' Open the output file
' --------------------------------------------------
Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(strOutputFile)
objFile.WriteLine "<html><head>" & _
"<style>BODY { font-family:arial,helvetica; }</style>" & _
"</head><body>"
objFile.WriteLine "<h2>Schema Report for "" & _
strDatabaseFile & ""</h2>"
' --------------------------------------------------
' Setup the string array of field type descriptions
' --------------------------------------------------
Dim strColumnTypes(205)
' initialize array
For intCount = 0 To UBound(strColumnTypes)
strColumnTypes(intCount) = "n/a"
Next
' add definitions
strColumnTypes(2) = "Integer"
strColumnTypes(3) = "Long Integer"
strColumnTypes(4) = "Single"
strColumnTypes(5) = "Double"
strColumnTypes(6) = "Currency"
strColumnTypes(11) = "Yes/No"
strColumnTypes(17) = "Byte"
strColumnTypes(72) = "Replication ID"
strColumnTypes(131) = "Decimal"
strColumnTypes(135) = "Date/Time"
strColumnTypes(202) = "Text"
strColumnTypes(203) = "Memo/Hyperlink"
strColumnTypes(205) = "OLE Object"
' --------------------------------------------------
' Open database and schema
' --------------------------------------------------
strCN = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & strDatabaseFile
Set objCN = WScript.CreateObject("ADODB.Connection")
objCN.Open strCN
Set objRS1 = objCN.OpenSchema(adSchemaTables)
' --------------------------------------------------
' Loop through database schema
' --------------------------------------------------
Do While Not objRS1.EOF
If Left(objRS1("TABLE_NAME"),4) <> "MSys" Then
objFile.WriteLine "<p><big>" & objRS1("TABLE_NAME") & "</big></p>"
objFile.WriteLine "<blockquote><table border=1>" & _
"<tr><th>Field Name</th><th>Data Type</th></tr>"
strSQL = "SELECT * FROM [" & objRS1("TABLE_NAME") & "]"
Set objRS2 = objCN.Execute(strSQL)
For Each objField in objRS2.Fields
objFile.WriteLine "<tr><td>" & objField.Name _
& "</td><td>" & strColumnTypes(objField.Type) & "</td></tr>"
Next
objFile.WriteLine "</table></blockquote>"
End If
objRS1.MoveNext
Loop
' --------------------------------------------------
' Close the output file
' --------------------------------------------------
objFile.WriteLine "</body></html>"
MsgBox "Finished!"