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

File Created: 2001:03:06:12:44:00

Last Updated: 2001:03:06:12:44:00

	ADO DAO
	Collections
	Constants
	Crystal Reports
	DSOFILE
	Errors
	ImageMagick
	IMAPI
	LogParser
	Lotus Notes
	MDAC
	Netscape
	OLE and Outlook
	OLE TUTORIAL
	OLE with Access
	OLE with Acrobat Viewer
	OLE with Excel
	OLE with Google Earth
	OLE with MSIE
	OLE with PowerPoint
	OLE with Word
	OLE with XML
	OpenOffice
	PDF Conversion
	RegExp
	Samples from Users
	SAPI
	Shell
	SQLite
	Task Scheduler
	Textract
	UDFs
	WIA
	WIL Type Viewer
	WindowsInstaller
	WinHttpRequest
	Winsock
	WSH
	Zip

Database Search

TechHome

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