Database Search

TechHome

• CLR Scripts to Create Linked Servers
• Create an XML Schema File from a SQL Server Database
• Get Database Results into a WIL Array and Display
• Insert Access Data to Excel
• Insert Excel Data Into Access
• OleDb GetSchema
• OleDb

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

OleDb GetSchema

 Keywords: GetSchema List Tables Columns Schema MetaData Obtain Access .accdb Database Table Schema Microsoft.ACE.OLEDB.12.0 System.Data System.Data.OleDb.OleDbConnection System.Data.DataTable ReportView .Net dotNet

;///////////////////////////////////////////////////////////////////////////////////////////////////////////////
;
;Winbatch 2013A - Using the CLR To Obtain Access Database Table Schema
;
;
;Stan Littlefield, May 02, 2013
;///////////////////////////////////////////////////////////////////////////////////////////////////////////////

isdata=1
cACCDB= dirscript():"ndata.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue",cACCDB:" is missing")
cConn='Provider=Microsoft.ACE.OLEDB.12.0;data source=':cACCDB
BoxOpen("Creating Schema Output",cACCDB)
ObjectClrOption("use","System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oConn = ObjectClrNew( 'System.Data.OleDb.OleDbConnection',cConn)
oConn.Open()

;no need for OleDbDataAdapter 
oTable = ObjectClrNew( 'System.Data.DataTable')

;Get both System and User tables
oTable = oConn.GetSchema("Tables") 
;Or
;Get only User tables
;oTableAll = oConn.GetSchema("Tables") ;return both system and user tables 
;oTable= oTableAll.Clone() ; create a copy/clone of the table object
;oSelect = oTableAll.Select("TABLE_TYPE = 'TABLE'") ;get only user tables
;ForEach _row in oSelect ;import the results into the cloned table
; oTable.ImportRow(_row)
;Next
 
rowcount = oTable.Rows.Count
colcount = oTable.Columns.Count

If rowcount==0 
   isData=0
   goto close
Endif

arrData = ArrDimension(rowcount+1,colcount)

oCols = oTable.Columns
x=0
ForEach col in oCols
   arrData[0,x]=col.ColumnName
   x=x+1
Next

;Read Data into an Array
For _row = 1 To oTable.Rows.Count-1
   objRow = oTable.Rows.Item(_row)
   For _col = 0 To oTable.Columns.Count-1
     objColumn = oTable.Columns.Item(_col)
     arrData[_row,_col] = objRow.Item(objColumn)
   Next
Next

:close
oConn.Close()
            
If ! isData
   BoxShut()
   Display(2,"Cannot Display Data","Query Returned No Rows")
   Exit
Endif

BoxShut()

; Display in Reportview
MyDialogFormat=`WWWDLGED,6.2`

MyDialogCaption=`%cACCDB%`
MyDialogX=090
MyDialogY=090
MyDialogWidth=374
MyDialogHeight=147
MyDialogNumControls=003
MyDialogProcedure=`DEFAULT`
MyDialogFont=`DEFAULT`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,DEFAULT`
MyDialogConfig=0

MyDialog001=`101,127,036,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,10,32,DEFAULT,DEFAULT,DEFAULT`
MyDialog002=`237,127,036,012,PUSHBUTTON,"PushButton_Cancel",DEFAULT,"Cancel",0,20,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
MyDialog003=`001,005,366,110,REPORTVIEW,"ReportView_1",arrData,DEFAULT,DEFAULT,30,7340032,DEFAULT,DEFAULT,"192|192|192"`

ButtonPushed=Dialog("MyDialog")


Exit

;/////////////////////////////////////////////////////////////////////////////////////

Article ID:   W17814

Filename:   OleDb GetSchema.txt

File Created: 2013:05:02:08:49:48

Last Updated: 2013:05:02:08:49:48