Sample Code to Test the ad... Constants in the ADO include File

Keywords: 	 ADO table schemas

I wrote this to test the ad.. constants in the ADO include file, but it provides a good method to document table schemas.

I also attached a JPG to show the results since the code uses a specific table.

stan littlefield

tab2html.wbt

;////////////////////////////////////////////////////////////////////////////////
;//     WINBATCH - OutPut Table Schema as HTML Document
;////////////////////////////////////////////////////////////////////////////////
;//     Currently 'hard-coded' for an ACCESS Table I tested, but should
;//     be generic for most tables you encounter.  Program will currently
;//     write to temp.htm, then shellexecute("temp.htm") which will bring
;//     the table in IE or your default browser.
;//
;//
;//     Stan Littlefield 07/24/2000
;////////////////////////////////////////////////////////////////////////////////


BoxOpen("Creating HTML Table OutPut","Opening ACCESS Table")

If FileExist(".\TEMP.HTM") == @TRUE
  FileDelete(".\TEMP.HTM")
EndIf


;put in your own data source
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=E:\WBDEMO\BLANK.MDB"

DB  = ObjectOpen("ADODB.Connection")

DB.Open(cConn)

;same with your own table
RS  = DB.Execute("SELECT * FROM TESTTBL")

handle= FileOpen("temp.htm","WRITE")

;modify to fit
STRING ="<html><head><title>Access Table Analysis</title></head><center><h2><i><b></b></i></h2><h2><i><b>Schema For...TESTTBL</b></i></h2></center><hr><p>"

FileWrite(handle,STRING)


;the rest is pretty standard
FileWrite(handle, "<TABLE BORDER>")
FileWrite(handle, "<TR> <TD>Name</TD> <TD>Type</TD> <TD>Size</TD>")
FileWrite(handle, "<TD>Attributes</TD> </TR>")


RS.MoveFirst()
flds= RS.Fields
nCnt=flds.Count

For i=0 To nCnt-1
   FileWrite(handle, "<TR>")
   fld  = rs.Fields(i)
   name = fld.name
   FileWrite(handle, StrCat("<TD>",name,"</TD> ") )
   GoSub fType
   size = fld.DefinedSize
   FileWrite(handle, StrCat("<TD>",size,"</TD> ")  )
   GoSub Display
   FileWrite(handle, "</TR>")
Next

FileWrite(handle, "</TABLE></HTML>")

FileClose(handle)

ObjectClose(RS)
DB.close()
ObjectClose(DB)

Drop(RS,DB)
BoxShut()

ShellExecute("temp.htm","","",@NORMAL,"")
Exit


:fType
FileWrite(handle, "<TD>")
Switch fld.Type

   Case 20
      FileWrite(handle, "adBigInt")
      Break
   Case 128
      FileWrite(handle, "adBinary")
      Break
   Case 11
      FileWrite(handle, "adBoolean")
      Break
   Case 8
      FileWrite(handle, "adBSTR")
      Break
   Case 129
      FileWrite(handle, "adChar")
      Break
   Case 6
      FileWrite(handle, "adCurrency")
      Break
   Case 7
      FileWrite(handle, "adDate")
      Break
   Case 133
      FileWrite(handle, "adDBDate")
      Break
   Case 134
      FileWrite(handle, "adDBTime")
      Break
   Case 135
      FileWrite(handle, "adDBTimeStamp")
      Break
   Case 14
      FileWrite(handle, "adDecimal")
      Break
   Case 5
      FileWrite(handle, "adDouble")
   Case 0
      FileWrite(handle, "adEmpty")
      Break
   Case 10
      FileWrite(handle, "adError")
      Break
   Case 72
      FileWrite(handle, "adGUID")
      Break
   Case 9
      FileWrite(handle, "adIDispatch")
      Break
   Case 3
      FileWrite(handle, "adInteger")
      Break
   Case 13
      FileWrite(handle, "adIUnknown")
      Break
   Case 205
      FileWrite(handle, "adLongVarBinary")
      Break
   Case 201
      FileWrite(handle, "adLongVarChar")
      Break
   Case 203
      FileWrite(handle, "adLongVarWChar")
      Break
   Case 131
      FileWrite(handle, "adNumeric")
      Break
   Case 4
      FileWrite(handle, "adSingle")
      Break
   Case 2
      FileWrite(handle, "adSmallInt")
      Break
   Case 16
      FileWrite(handle, "adTinyInt")
      Break
   Case 21
      FileWrite(handle, "adUnsignedBigInt")
      Break
   Case 19
      FileWrite(handle, "adUnsignedInt")
      Break
   Case 18
      FileWrite(handle, "adUnsignedSmallInt")
      Break
   Case 17
      FileWrite(handle, "adUnsignedTinyInt")
      Break
   Case 132
      FileWrite(handle, "adUserDefined")
      Break
   Case 204
      FileWrite(handle, "adVarBinary")
      Break
   Case 200
      FileWrite(handle, "adVarChar")
      Break
   Case 12
      FileWrite(handle, "adVariant")
      Break
   Case 202
      FileWrite(handle, "adVarWChar")
      Break
   Case 130
      FileWrite(handle, "adWChar")
      Break
EndSwitch

FileWrite(handle, " </TD>")
Return

:Display
Attrib = fld.Attributes
FileWrite(handle, "<TD>")

If(Attrib & 2) Then
   FileWrite(handle, "adFldMayDefer ")
EndIf

If(Attrib & 4) Then
   FileWrite(handle, "adFldUpdatable ")
EndIf

If(Attrib & 8) Then
   FileWrite(handle, "adFldUnknownUpdatable ")
EndIf

If(Attrib & 16) Then
   FileWrite(handle, "adFldFixed ")
EndIf

If(Attrib & 32) Then
   FileWrite(handle, "adFldIsNullable ")
EndIf

If(Attrib & 64) Then
   FileWrite(handle, "adFldMayBeNull ")
EndIf

If(Attrib & 128) Then
   FileWrite(handle, "adFldLong ")
EndIf

If(Attrib & 256) Then
   FileWrite(handle, "adFldRowID ")
EndIf

If(Attrib & 512) Then
   FileWrite(handle, "adFldRowVersion ")
EndIf

If(Attrib & 4096) Then
   FileWrite(handle, "adFldCacheDeferred ")
EndIf

FileWrite(handle, "</TD>")
Return

To view a JPG showing Schema for ... TESTTBL: testtbl_table.jpg

Article ID:   W14705

Filename:   ADO Table Schemas.txt

File Created: 2017:08:29:11:58:50

Last Updated: 2017:08:29:11:58:50

Database Search

TechHome

Sample Code to Test the ad... Constants in the ADO include File

tab2html.wbt