UDF to Create a Catalog Object and Return an Array of all Database Tables and Table Types

Keywords: 	 UDF arrays catalog object access

---

Below is a script which implements both UDF's and arrays to perform a standard OLE task (in this case enumerating tables in a database).

; /////////////////////////////////////////////////////////////
; Using UDF's in WB 2001 to Perform standard OLE Tasks       //
;                                                            //
; This example prompts for a given ACCESS File, then uses    //
; OLE DB to create a Catalog Object.  This object is then    //
; passed to a UDF which returns an array of all tables and   //
; table types in the Database. A second array holds the      //
; information for the first array.                           //
;                                                            //
; Just remember, arrays are 0-based                          //
;                                                            //
; Stan Littlefield 03/03/2001                                //
; /////////////////////////////////////////////////////////////

#DefineFunction ArInfo(PassedArray)
; in this particular script we only need element 1
; but might as well get them all
; so we have a generic and re-usable UDF
aInfo    = ArrDimension(7,0,0,0,0)
aInfo[0] = ArrInfo(PassedArray,0)  ; array dimensions
aInfo[1] = ArrInfo(PassedArray,1)  ; elements in dimension 1
aInfo[2] = ArrInfo(PassedArray,2)  ; elements in dimension 2
aInfo[3] = ArrInfo(PassedArray,3)  ; elements in dimension 3
aInfo[4] = ArrInfo(PassedArray,4)  ; elements in dimension 4
aInfo[5] = ArrInfo(PassedArray,5)  ; elements in dimension 5
aInfo[6] = ArrInfo(PassedArray,6)  ; total array elements
Return(aInfo)
#EndFunction

#DefineFunction GetTables(cat)     ; takes ADOX Catalog as parameter
Tbls   = cat.Tables
nCount = Tbls.count()
lArray  = ArrDimension(nCount,2,0,0,0)
For i=0 To nCount-1
   tbl = cat.Tables(i)
   lArray[i,0]  = tbl.Name
   lArray[i,1]  = tbl.Type
Next
Return(lArray)
#EndFunction

cMDB   = AskFileName("Select ACCESS Database",".\","MDB Files|*.mdb|","*.mdb",1)
cat    = ObjectOpen("ADOX.Catalog")
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%cMDB%;"

BoxOpen("Collecting Table Information.....","Opening %cMDB% ")

aOle   = GetTables(cat)  ; create array of tables
aInfo  = ArInfo(aOle)    ; gather info on elements in array

ObjectClose(cat)  ; might want to leave open in a real app
BoxShut()
message("Total Tables Found",aInfo[1] )

;now enumerate tables using the arrays
For i=0 To aInfo[1]-1
    j = i+1
    message("Table %j%",strcat("NAME: ",aOle[i,0],@CRLF," TYPE: ",aOle[i,1],@CRLF) )
Next

exit

:cancel
message("No File Selected","Bye....")
exit

---

Article ID:   W14679

Filename:   UDF to Return an Array of DB Table Types.txt