how do i query index information using SQL (Microsoft Access)

[mike wells sydney]

My system manages indexes and is designed to talk (via ODBC) to various
databases (ms access, SQL server, Oracle).
In order to manage indexes, we query index information and then determine
what is missing.
This can be done with SQL Server (eg. using SQL like SELECT * from
SysIndexes, SysIndexKeys WHERE name like.....) but I cannot find a way to do
the same thing with MS access.
Does anyone know how to query MS Access for index info using SQL ?

 

[Douglas J. Steele]

My system manages indexes and is designed to talk (via ODBC) to various
databases (ms access, SQL server, Oracle).
In order to manage indexes, we query index information and then determine
what is missing.
This can be done with SQL Server (eg. using SQL like SELECT * from
SysIndexes, SysIndexKeys WHERE name like.....) but I cannot find a way to
do
the same thing with MS access.
Does anyone know how to query MS Access for index info using SQL ?

Unfortunately, you can't.

You have to use methods of either DAO or ADOX.

 

[Douglas J. Steele]

Closer is the ADODB OpenSchema approach because you get a recordset
(rather than a hierarchy of collections) e.g.

? CurrentProject.Connection.OpenSchema(adSchemaIndexes).GetString

True. I keep forgetting about OpenSchema!

 

[mike wells sydney]

Thanks very much for replying. Its greatly appreciated.

However, you have used terms that I know absolutely nothing about. DAO,
ADOX, ADODB.... I'm not sure what context you guys work in - maybe .NET or
Java ?. My world is Smalltalk talking to databases via native ODBC calls. We
pretty much just pass direct SQL to the database via ODBC.

So it sounds like I can't get the index information.

Thanks for the help. Its nice to have an answer - even if its a 'no'.

 

[Brendan Reynolds]

I've never used Smalltalk, but I checked Cincom's web site (apparently
they're one of the main Smalltalk implementers) and they say their product
supports COM. DAO, ADOX and ADODB are all COM technologies, so it is
possible you may be able to call them from Smalltalk.