Excel database (enumerating database tables, fields, etc)

[Gintaras]

Didn't find how to get that info using MS Jet SQL. But can get using ADO.

Get only table names (or sheet names)

Code:

Copy      Help

out
Database d
d.Open(d.CsExcel("$personal$\book1.xls"))
ADO.Recordset rs=d.conn.OpenSchema(ADO.adSchemaTables)
;out rs.RecordCount
ARRAY(str) a
d.RsGetAll(rs a)

;;all info
;int i j
;for i 0 a.len
,;for j 0 a.len(1)
,,;out a[j i]
,;out "--"

;table names (or Excel sheet names)
int i
for i 0 a.len
,out a[2 i]

Get table names and column names

Code:

Copy      Help

out
Database d
d.Open(d.CsExcel("$personal$\book1.xls"))
ADO.Recordset rs=d.conn.OpenSchema(ADO.adSchemaColumns)
ARRAY(str) a
d.RsGetAll(rs a)

;;all info
;int i j
;for i 0 a.len
,;for j 0 a.len(1)
,,;out a[j i]
,;out "--"

;table names (or sheet names), column positions and column names
int i
for i 0 a.len
,out "%s %i %s" a[2 i] val(a[6 i]) a[3 i]

ADO reference is available in MSDN Library (search for OpenSchema). The same information also is available in MS Access Help.